Last week I presented two data visualization workshops at the UC Davis Human Services 4th Annual Statewide CQI Conference. We talked about how to make informative and impactful data visualizations, how to tell a story with our visuals, and practiced with examples in Excel.
One of the most popular Excel tricks in these workshops is creating small multiple bar charts. This is done by converting a 100% stacked bar chart through the use of gap (filler data) rows for each category. Basically you make sure each category of real data + gap data equals 100%. Doing this makes all the bars for the next category left aligned with one another to give the illusion that you have multiple sets of bar charts.
In this post I walk you through how to create your own small multiples chart. You can also watch the accompanying video tutorial online here. The final small multiples would look something like the chart below.
Step 1: Create gap or filler data
Create a gap or filler column of data for every category in your dataset. The real data and the filler data should add up to 100%. You can do this by entering the formula “=1-cell with the real data” in the gap column. For example, the formula for the gap column for Society for ages 65+ years would be “=1-B72”. The real data for this grouping is 21% and the gap data is 79%. Together these add up to 100% (21%+79%).
Step 2: Insert a 100% stacked bar chart
Next you insert a 100% stacked bar chart. When you do this, you will see shaded bars for both the real and the gap data. So you can follow along more easily, I’ve gone ahead and added the title, data source, and category labels in this step by adding text boxes. You can add those details in a later step when you’re creating your own charts.
Step 3: Remove the fill color for the gap bars
Next you need to remove the default bar colors from the gap data so that no color appears in this space on the chart. Essentially you are hiding this data by changing the default color to no color. To do this you right click on the gap bars, select format data series, and then choose “no fill” for the bar color. Repeat for each of the gap data sections in your chart.
Step 4: Add data labels
Ok. This is the section that inspired this post and I am really excited about it.
I have been making small multiple bar charts for years. During that time I have been manually dragging the data labels to the outside of the bars. While not super time consuming, sometimes it can be a challenge to get the labels all perfectly aligned. Usually when I feel frustrated by something like this it means there is an easier way.
Well, my friends, there is!
Through a little data labels magic, you can trick Excel to add the data labels to the inside base of the fake data bar which is also the outside end of the real data bar (an option that is not available in Excel for stacked bar charts).
- Add data labels to your gap data bar
- Select format data labels
- Choose “value from cells” in the formatting panel
- Highlight the data from the real category adjacent to the gap data
- Uncheck “value” and “show leader lines”
- Change the label position to “inside base”
Step 5: Add at title and category labels
The final step is all about formatting. This is where you would add a journalistic chart title with the key takeaway message. You would also add category labels above each group of bars. Remember to match the color of the text to the color of the bars for ease of interpretation.
There you have it! With a few clicks in Excel you have taken a dense stacked bar chart and created a polished small multiples bar chart.
Have you tried this approach? Share your results with me as a comment or on Twitter. And let me know how it goes with automating the data labels. I am very excited to have learned this new time saver.