putting graph elements in cells directly

Today's short post focuses on an Excel tip that I find myself using frequently and I get asked about a ton. While I focus on Excel here, I imagine the same could apply when working in any spreadsheet application. For additional formatting flexibility, put some graph elements in the cells directly.

This is probably easiest to illustrate through an example. Below is a visual from storytelling with data: a data visualization guide for business professionals. It's highlighted in Chapter 6 (pg. 161), which focuses on what I consider to be model examples of data visualization. I regularly get questions about how this graph was created.

Source: Knaflic, Cole.  Storytelling With Data: A Data Visualization Guide for Business Professionals , Wiley, © 2015. (Original figure had data labels on the stacked bars; I've omitted those here for simplicity.)

Source: Knaflic, Cole. Storytelling With Data: A Data Visualization Guide for Business Professionals, Wiley, © 2015. (Original figure had data labels on the stacked bars; I've omitted those here for simplicity.)

There are two common questions posed about this visual:

  1. How did you format some of the y-axis labels differently from the rest? 
  2. How did you include the TOTAL % in the graph?

The trick in both of these cases is the same: these elements aren't part of the graph directly, rather they are in individual cells in Excel. In this particular graph, the only things directly in the graph element are the stacked bars. Everything else is done in cells. I do this for additional formatting flexibility. In Excel, you can only apply a single format to axis labels on a given axis. You also have to get creative (and it can sometimes be limiting) if you want to add additional numbers or labels to the visual that aren't part of the data you're visualizing directly. Using the cells allows me to break free from both of these constraints.

In case it's easier to see what I'm talking about, below is what the visual looks like if I show the gridlines in Excel (in the preceding view, all of my cells were filled with white).

FIG0605 with gridlines.png

When approaching this way, you do have to take care to ensure that everything is lined up correctly. This means precisely aligning the graph with the cells and making both cell and graph heights line up (otherwise your bars won't correctly align with the words and numbers). Also, be aware that if you shuffle your data around, you'll need to adjust the elements you've put into the cells directly. Changing the order of your data would change the graph in this case, but not the PRIORITY or TOTAL %, which would have to be done manually (unless you set up so it's pointing to the data you reshuffle). Both of these things lead me to express a word of caution: when I break the pieces apart like this, it's easier to introduce issues and have things not line up: both from a visual standpoint as well for data and labels to get out of sync. Because of this, the tradeoffs may not be worth it if you're trying to automate or it's a repeated process that you don't want to have to adjust each time. But so long as you're careful and pay attention to detail, when you need the additional formatting flexibility, consider putting some elements into cells directly.

If it's of interest, you can download my Excel workbook.

Are there other graphs from storytelling with data that you have questions about or would like a behind-the-scenes peek? Or other tricks you employ when graphing data that you'd like to share? Leave a comment!

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!

axis vs data labels

A common decision point that arises when creating a graph is whether to show the axis or label the data points directly. Today's post focuses on the things I think about when making this decision and illustrates a few possibilities via example.

Probably the most common approach you'll see is where the axis is shown directly, as in the following graph.

In the above graph, we have monthly spend, in millions of dollars, on the y-axis. The x-axis is time, with Jan-Apr depicting actual data and May forward are forecast figures.

Showing both axes directly is a good default approach. This works especially well if you want your audience to focus on the shape of the data without cluttering it up with a bunch of numbers.

That said, there are circumstances that might cause you to vary from this. One consideration is the level of specificity the audience needs to have with the individual data points. If the particular numbers themselves are important, I'd suggest labeling the data points directly. In this case, you can get rid of the y-axis altogether: it's no longer necessary and becomes redundant when the points are individually labeled.

While I wouldn't typically preserve the y-axis and label every single data point (due to the redundancy previously mentioned), there may be cases where it makes sense to keep the axis and label just some of the data directly. For example, if we thought our audience would want to know how much we've actually spent each month but for the forecast we just want people to focus on the general shape of the data that illustrates our expectations, I might do something like the following:

Notice how the data labels themselves can help to draw attention to certain data points. In preattentive attribute language, these are "added marks." They act as visual cues, helping direct our audience's attention within the graph. It's like the graph is saying, "Look audience, these numbers are so important that they are labeled directly so you know what the specific values are."

We could use this strategy with data labels to direct our audience's attention to another point in the graph:

Notice that you can't help but look at the $7.5M label and the data point that goes with it. In the above, I chose a different color to fill in the data marker and also to label the data point, so as not to inadvertently visually tie together the ACTUAL data with the Jun data point (by filling it with the same dark blue as the actual data). In this case, we are drawn quickly to the $7.5M figure in Jun, while none of the other figures are labeled directly. Rather, we see the general shape of the rest of the data as context, and if we want to know rough values, can estimate those using the y-axis at the left.

This is actually an example from a recent workshop (details and numbers changed to preserve confidentiality). My final graph looked similar to the following, where in addition to the data label, I added some explanatory text:

I should note that whether to show the axis or label data directly is a decision point in most (all?) graphs. I've illustrated using a line graph here, but the same thought process can be applied to vertical or horizontal bars, scatterplots, etc.

In general, when you are deciding whether to show the axis, label the data directly, or some combination of these things: consider how you want your audience to read the graph. What level of specificity do they need to have with the individual data points? Where do you want them to pay attention? Let the answers to these questions guide your thoughtful design.

If interested, you can download the Excel file with the above graphs.

where are your eyes drawn?

I spend a lot of time in my workshops and in my new book discussing preattentive attributes. These are elements like size, color, and position on page that, when used sparingly, can direct your audience's attention to where you want them to pay it when communicating with data. There is a test I like to employ when it comes to determining whether preattentive attributes are being used effectively: the "where are your eyes drawn?" test. In this post, I discuss this test, how to do it, and we look at a number of images using it and discussing the implications for data visualization.

Read More

align against a common baseline

There is a recommendation I find myself often voicing to workshop participants: Think about what you want your audience to be able to easily compare. Put those things as physically close together as you can and align them along a common baseline. This post features a makeover applying this recommendation.

Read More

the power of categorization

I am writing this post on the heels of a lovely albeit short European trip. It was part work, part play. In our spare time, my husband and I ventured out to one of our favorite restaurants. As I was perusing the wine list, I was reminded of the importance of categorization (yes, apparently my data-brain is on even at dinnertime). In this post, we'll take a quick look at how categories help us make sense of things: both in life and in data visualization.

Read More