supercategory axis labels in Excel

I have a super quick tip to share today for those reading who work in Excel. I see graphs regularly that look similar to the following:

Supercategories in Excel 1.png
 

Note the x-axis. It isn't awful. But it also isn't ideal. The years are repeated. It looks a little sloppy. "Lack of attention to detail here? What does that mean for all the steps behind the scenes that I can't see?" your audience may be wondering. Also, studies have shown that diagonal text is about 50% slower to read than horizontal text. So if efficiency of information transfer is one of your goals when communicating with data—which I would argue it should be—aim for horizontal text whenever possible.

In Excel, there is a quick trick to fix this. I get asked how to do this regularly, so figured it warrants a quick "how to" post. I'll focus on the x-axis using the above example—you could follow a similar process to create supercategory axis labels on the y-axis when that makes sense, too. Also, this example uses quarterly data but you could follow the same process with monthly data.

In the Excel spreadsheet, the data graphed above looks like this:

Supercategories in Excel 2.png
 

The current x-axis labels (DATE) are in the column on the left and the y-axis values (SALES) are in the column on the right. Start by highlighting these and creating your graph. It will look similar to the graph at the beginning of this post.

Next, we need to add a couple more columns of data to split out the year and quarter (the process would look very similar if you have monthly data). I typically add two columns to the left of my original data to do this (new columns highlighted in blue):

Supercategories in Excel 3.png
 

Once you've done that, right-click on your graph and go to "Select Data."

Supercategories in Excel 4.png
 

In the menu that comes up, "Category (X) axis labels" (highlighted in blue at right, below) will be pointed at the original DATE column (mine, as you see below, was in Column D):

Supercategories in Excel 5.png
 

Click on this and use your mouse to highlight the range of both of the new columns you've added (my YEAR and QUARTER data was in columns B and C, respectively).

Supercategories in Excel 6.png
 

Hit OK, and your graph will look something like this:

Supercategories in Excel 7.png
 

Voila! A beautifully formatted x-axis. If it would be helpful, you can download the Excel file.

Would it be helpful to see more Excel "how to" posts like this? Let me know if there are specific topics or questions of interest that we can answer here by leaving a comment. Do you have any quick tool tips you'd like to share (Excel or otherwise)? Leave a comment to enlighten us all!

/
CLICK HERE TO JOIN OUR MAILING LIST

SEARCH STORYTELLING WITH DATA: © 2010-2018 Cole Nussbaumer Knaflic. All rights reserved. STORYTELLING WITH DATA and the STORYTELLING WITH DATA logo are trademarks of Cole Nussbaumer Knaflic.

#SWDchallenge: square area graph

Last month’s challenge was a frequently used graph: the basic bar chart (nearly 90 people shared their bars!). This month, as promised in the latest podcast episode, I’m going to challenge you to take on something less common: the square area graph. This is also known as a waffle chart. I tend to dislike charts that can be described as food—pie, donut, spaghetti, lasagna—so I don’t usually call it that (I realize some people may take issue with this, but that’s not going to stop me). I’ve also heard it called a unit chart, though for me unit charts use an icon to represent values (as described here by the Information Lab), whereas the square area graph uses...well...squares.

Whatever you choose to call it, these types of visuals can be useful in a couple of situations. I’ll give you some insight into scenarios where I’ve used square area graphs and illustrate each through example. If you plan to participate, be sure to read the instructions at the end of the post, as there are some important steps to take if you’d like yours to be included in the recap (most important: email your entry to SWDchallenge@storytellingwithdata.com). Here are some thoughts and examples on how I’ve used square area graphs:

To compare numbers of vastly different magnitudes

I see a bad thing done sometimes to bars when trying to compare numbers of very different magnitudes: the broken y-axis. This is when someone cuts a chunk out of the middle of the y-axis (often, this is denoted by a zigzag or diagonal line or set of lines on the axis or bars). This invalidates the visual comparison (the same way starting a bar chart at something other than zero does—don’t do this! If you aren't convinced, read more). The square area graph can sometimes be an alternative here. With bars, you only either get height (in the case of a vertical bar) or width (in the case of horizontal bar), whereas with a square you get both height and width. This allows us to compare numbers of different magnitudes in a more condensed space. Here’s an example illustrating this from the SWD blog archives (view full post):

Warren Buffett 3.jpg
 

An alternative to the pie chart

The square area graph can also sometimes work as an alternative to the pie chart. It’s worth noting that because we’re looking at areas, this chart type does fall victim to some of the same challenges as the pie chart (namely, our eyes aren’t great at measuring areas, making specific comparisons difficult). You do get some benefit, though, because you have the x- and y-axes on left/right and top/bottom, which means you have a few consistent baselines for comparison. We tend to overestimate areas, so having the “waffling” or gridlines forming the squares becomes important. I should also note that, depending on what you’re looking at (and how you determine it best to aggregate the data if you do so) while your units will be squares, you may not end up with a perfect square as your overall shape, which is ok.

The following is an anonymized example from one of my client workshops:

Square Area Advertiser Spend.png
 

When presenting live

While the presenting live scenario isn't fully sufficient reason to use a square area graph—the data must first lend itself well to this format—the live presentation does give you additional flexibility. Because square area graphs are less common, it can mean we face a hurdle getting our audience to understand what we’re showing. In a live setting, you can help this less-familiar graph feel more accessible by building it piece by piece while talking your audience through what you are showing. When I introduced the previous example to its intended audience, I built it piece by piece. Let’s check out another example where I show you the full progression. Again, this is a real example, where I've changed the details to preserve confidentiality (press the play button in the middle to see the progression; there's no audio in the following video, only images).

 

If you are looking at these examples thinking “that looks kind of cool, but can I do that in my tool?”—you are not alone. This question is posed to me nearly every time I discuss the square area graph. While it may look fancy, my method is quite ineloquent. I consider this “brute force Excel” at its best: in the examples above, I’m working directly in an Excel spreadsheet, where I’ve sized the cells to be squares and manually formatted them to get the resulting visuals. It isn’t fancy, but it works. That said, I’m pretty sure there are tools and add-ins that make this easier. I haven’t used any of them, so if you’re aware of these and would like to share so others can benefit, please leave a comment on this post with details.

All of that is prelude to the challenge I pose to you: find some data of interest and create a square area graph. Share it so we can all see and learn! DEADLINE: Tuesday, 4/10 by midnight PST. You must EMAIL YOUR ENTRY to SWDchallenge@storytellingwithdata.com for inclusion in the follow-up post. I know we missed a number of people with the last recap, but it’s because we can’t go searching for your graphs. Given the volume of entries we’re receiving (and how manual the process currently is), we are not able to scrape Twitter, LinkedIn, etc. for submissions. The way to ensure inclusion is to send them to us. Full submission instructions follow (please do follow them, it makes our lives much easier!).

SUBMISSION INSTRUCTIONS:

  • Make it. Identify your data and create your visual with the tool of your choice. If you need help finding data, check out this list of publicly available data sources. You're also welcome to use a real work example if you'd like, just please don't share anything confidential.
  • Share it. Email your entry to SWDchallenge@storytellingwithdata.com by the deadline. Attach your image as a .PNG. Put any commentary you’d like included in my follow up post in the body of the email (e.g. what tool you used, any notes on your methods or thought process you’d like to share); if there’s a social media profile or blog/site you’d like mentioned, please embed the links directly in your commentary (e.g. Blog | Twitter). If you’re going to write more than a paragraph or so, I encourage you to post it externally and provide a link or summary for inclusion. Feel free to also share on social media at any point using #SWDchallenge.
  • The fine print. I reserve the right to post and potentially reuse examples shared.

I look forward to seeing what you come up with! I love seeing the varied topics and learning new things through this challenge. Stay tuned for the recap post in the second half of April.

/
CLICK HERE TO JOIN OUR MAILING LIST

SEARCH STORYTELLING WITH DATA: © 2010-2018 Cole Nussbaumer Knaflic. All rights reserved. STORYTELLING WITH DATA and the STORYTELLING WITH DATA logo are trademarks of Cole Nussbaumer Knaflic.

“how do I incorporate visual design into our monthly deck?”

After reading storytelling with data or participating in a workshop, people often ask how they can incorporate the lessons into a recurring (i.e. monthly, quarterly) report. These reports often materialize as a PowerPoint deck, which started sparsely, but over time has taken on a life of its own and now resembles the “slideument”: part presentation, part document but not exactly either at its best.

Consider the slide below, which is based on an actual slide from a recent client workshop. (I’ve anonymized the client’s data to preserve confidentiality.) Today’s post demonstrates how to apply data storytelling lessons to a visual from a monthly deck, illustrating the thought process to improve it.

Picture1.png

This slide shows a monthly trend of customer service complaints: in total (top chart) and broken down by category (bottom chart). The commentary section tells us (the audience) what the important points of reference are: what happened this month compared to last month (complaints are up 14%), where it changed (Employees) and their proposed next steps. However, notice how much work takes to read through all this text and then find evidence of this in the graphs.

Imagine if you were given this slide to determine an action plan. If you were in a live meeting, would you be able to read all of this text and listen to the presenter at the same time? If you weren’t in the meeting and were reading through the deck, how much time would you realistically spend trying to digest the information presented? We can improve on this visual in both scenarios with a few design changes.

In both cases, I used the commentary as a guidepost for the important takeaways and re-designed the visuals accordingly.

First, let’s a closer look at the top chart. The commentary tells us that complaints were up 14% vs the prior month.

Picture2.png
 

Where did your eyes go first in this graph? Mine went to the red Average line, which I visually estimated to be about 410 per month.  In looking for evidence of the 14% increase in December, I had to do a lot of mental math (add the Solicited + Unsolicited for November and compare it to Solicited + Unsolicited for December) which took more time than someone would likely spend doing this.

If that 14% increase is what the audience should know, check out the difference between the original visual and this:

Picture3.png
 

When applying the “where are your eyes drawn?” test, my eye went straight to the data markers & labels at the end of the total line, where I could see both the absolute numbers and annotations telling me it’s a 14% increase. Since we’re visualizing time, I changed the graph type from a bar chart to a line chart, unstacked the data series, and added a series for the total. This was intentional based on the commentary, which only referenced the total trend. I chose to de-emphasize the subcomponent pieces (Unsolicited and Solicited) by using grey.

Side note: what about the Average line? If the monthly deviation from average was really important, one option would be to keep it in the graph for reference with the tradeoff that adding a fourth data series could create clutter. Another option is an entirely different choice of visual, depicting the monthly change (from average), with a visual cue to indicate that December’s data is acceptable. Both are choices the information designer would make knowing the audience and what context is relevant. In this case, I didn’t feel that this additional point added anything to the overarching story, so I chose to eliminate it altogether.

Let’s take another look at the second visual now. The commentary tells us that complaints were up in a specific category: Employees. Not only did they increase, but they increased from 87 to 117. Apply the “where are your eyes drawn?” test again with the original visual.

Picture4.png
 

If I took an informal poll of readers here, some might have gone to the black line, others might have noticed the blue list first and others (like me) went to the red line. Regardless of which line you focused on first, I’d likely bet that you didn’t focus first on the November to December increase in the Operations line (red).  In fact, it’s difficult to discern the absolute numbers (87 and 117) here because of the general clutter: overlapping data series, gridlines, color, heavy chart border and legend at the bottom requiring some visual work to figure out which line goes with which complaint category.

When setting out to improve a visual, there’s not necessarily a right or wrong answer in choosing a visual type: it often takes looking at the same data several different ways to find which view is going to create that magical “lightbulb” moment. Let's look at a few different variations of this visual.  

First let’s keep the existing line chart, remove some of the clutter and focus attention on the November to December change in Employees.

Picture5.png
 

This view gives the audience the full context of the 12 month trend, while focusing attention strategically on a specific point. However, if the emphasis is really about the November to December change, we could also visualize only those two data points. Let’s look at a few different ways of displaying this.

First, this horizontal bar chart compares this month (December) to last month (November). Horizontal bar charts are useful when your category names are long and therefore can be displayed horizontally from left to right on the y-axis without having to rotate or shorten them.

Picture6.png
 

Another option is a vertical bar chart, if you’re more inclined to preserve the left-to-right construct of displaying time.

 

As a third option, we could use a slopegraph. Slopegraphs can work well in making change visually apparent across categories. Check out how clear it is that some of these categories changed more drastically than others. In fact, looking at the data this way, we see that there was also a marked increase in service-related complaints, something that didn't stand out as much in the other views of the data. You can read more about slopegraphs, including design considerations, in this previous post.

Picture8.png
 

Any of these three visuals could work for depicting this data, I chose the slopegraph for the final version to keep the emphasis on the change in the two data points.

Here's what it could look like if all of this needed to be on a single slide:

Picture10.png

In the remade version, I’ve moved the text to be closer to the data it describes and used color strategically to create a visual link between the text and where to look in the graphs for evidence. I’ve also made the call to action more visible—remember when communicating with data for explanatory purposes, we should always want our audience to do something with the data we’re showing them!

Check out the difference between the original and the remade version:

Picture9.png
Picture10.png

This single view works well as a remake of the original, but not as well in a live presentation. There’s still too much text to read and process, while listening to a presenter at the same time. For a live setting we can still use the same visuals, but build piece by piece (using animation), which forces the audience to listen to the presenter describing the data. For example, consider the Complaints over time visual again:

Picture11.png
 

Now imagine if each of these images were its own slide. Sparse slides lead to better presentations because a person is there to narrate what’s happening.

Picture12.png
 

One final note on the choice of red as the emphasis color. Some readers may be surprised to see something different from our usual blue & orange as emphasis colors (and readers who are Michigan fans are probably having heart palpitations!). In this case, red was the client’s brand color so we chose to stay consistent with the rest of their visuals. If that weren’t the case, we might avoid red because it could a negative connotation, even though this is a somewhat positive story (complaints declining over time).  

In conclusion, we can indeed incorporate visual cues such as strategic use of color and words into a monthly recurring presentation so that our audience clearly knows 1) what’s important and 2) what action to take.  You can download the Excel file with accompanying visuals here


Elizabeth Ricks is a Data Visualization Designer on the Storytelling with Data team. She has a passion for helping her audience understand the "so-what?" Connect with Elizabeth on LinkedIn or Twitter .  

/
CLICK HERE TO JOIN OUR MAILING LIST

SEARCH STORYTELLING WITH DATA: © 2010-2018 Cole Nussbaumer Knaflic. All rights reserved. STORYTELLING WITH DATA and the STORYTELLING WITH DATA logo are trademarks of Cole Nussbaumer Knaflic.

#SWDchallenge: basic bars

If I could only use a single graph forever forward, it would be a bar chart. It's sort of like that exercise: if you were stranded on a deserted island and could only bring a single book to read or only have one type of food to eat from then on out—what would it be? (Easy: The Great Gatsby and peanut butter toast). Is it a realistic scenario? No (I'd have my Kindle with me and likely not an endless supply of PB and bread...or electricity...or a toaster). Would I get sick of my choices? Probably. Would it work all of the time? No. But I really do have an affinity for stories about the Jazz Age and peanut butter toast.

I also really like bar charts.

Bars are my go-to graph for a number of reasons. They are common. While this might be cause for some to avoid, this is one of my top reasons for embracing: your audience already knows how to read a bar chart, so you don't face a learning curve for getting your information across. They are not intimidating—you aren't likely to scare anyone with a bar chart. Bar charts are also easy for us to read. When we look at a bar chart, our eyes compare the ends of the bars relative to each other and relative to the axis. Because of the alignment to a consistent baseline, it's easy to see which category is the largest, which is the smallest, and also the incremental difference between categories. Note that for the visual comparison to be accurate, bar charts must have a zero baseline (read more). Bar charts can be vertical (also known as a column chart) or horizontal (great use case: if category names are long—allows you to orient text in a legible fashion, avoiding slow-to-read diagonal text that would be needed if you stick with vertical orientation). Below is an example of each from storytelling with data:

Basic Bars - Vertical.png
Basic Bars - Horizontal.png

The #SWDchallenge this month is to create a basic bar chart. Nothing fancy. No need to stack it or do anything else crazy. Have you made a bar chart before? Probably. But here is an opportunity to focus on making your best bar chart yet. Find some data and teach us all something new. While not a strict requirement, I do encourage you to look to the prior challenges when it comes to annotating and thoughtful use of color and words (including the takeaway title!)—these bar charts are meant to inform, so those important lessons will apply here as well. DEADLINE: Wednesday, 3/7 by midnight PST. Specific submission details follow.

SUBMISSION INSTRUCTIONS:

  • Make it. Identify your data and create your visual with the tool of your choice. If you need help finding data, check out this list of publicly available data sources. You're also welcome to use a real work example if you'd like, just please don't share anything confidential.
  • Share it. Email your entry to SWDchallenge@storytellingwithdata.com by the deadline. Attach your image as a .PNG. Put any commentary you’d like included in my follow up post in the body of the email (e.g. what tool you used, any notes on your methods or thought process you’d like to share); if there’s a social media profile or blog/site you’d like mentioned, please embed the links directly in your commentary (e.g. Blog | Twitter). If you’re going to write more than a paragraph or so, I encourage you to post it externally and provide a link or summary for inclusion here. Feel free to also share on social media at any point using #SWDchallenge.
  • The fine print. I reserve the right to post and potentially reuse examples shared.

I look forward to seeing your beautiful bars! Stay tuned for the recap post in the second half of March.

/
CLICK HERE TO JOIN OUR MAILING LIST

SEARCH STORYTELLING WITH DATA: © 2010-2018 Cole Nussbaumer Knaflic. All rights reserved. STORYTELLING WITH DATA and the STORYTELLING WITH DATA logo are trademarks of Cole Nussbaumer Knaflic.