Last week we looked at building Excel waffle charts using Conditional Formatting. This week we’re looking at building Stacked Bar Excel waffle charts.
Some might say this technique is easier, although it may appear daunting when you look at the number of steps. Don’t let that put you off though, it’s super easy.
The other benefit with using this technique is you can represent precise proportions, as shown below where the Gamma series is 8.5%:
You can also easily add/remove series from stacked bar waffle charts.
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Stacked Bar Waffle Charts Step by Step
The data layout that supports the charts uses formulas to determine which segments should be coloured for each series, plus it has a dummy series for the error bars in column B:
Tip: you can download the file for this tutorial and simply change the data in cells B4:C6, and add/remove series to the table as required. Modify the formulas by following the pattern of changes from the formulas in column E to F.
Step 1: Insert Stacked Bar Chart
Select all chart data (cells B9:E19) > Insert tab > Charts: 2-D Stacked Bar Chart:
Step 2: Change Error Bar series chart type
Select the Error Bar series in the chart > right-click and select Change Series Chart Type…
Change the Error Bar series to a scatter with straight lines. This will automatically place it on the secondary axis.
Step 3: Set axis maximum and minimum
Set the X axis and secondary Y and X axis minimum to 0 and maximum to 10. Select the secondary Y axis > CTRL+1 to format:
Repeat for X axis.
Step 4: Remove gap width for remaining bars
Select one of the bars in the chart > CTRL+1 to format > Set gap width to 0%:
Step 5: Add Error Bars to the Error Bar series
Select the Error Bar series line > click the plus sign to reveal the chart elements list > Error bars > More Options:
In the Error Bar Options set the vertical error bar to Both directions, No Cap and Fixed Value of 10:
Then go to the Paint Bucket icon and set the formatting to a solid white line:
Repeat for the horizontal error bars. Select the Horizontal Error bar from the ‘Error Bar Options’ drop down:
Step 6: Hide Axis Labels
Select the chart > click on the + menu > Deselect Axis labels:
Step 7: Hide Error Bar series
Select the Error Bar series scatter chart line and format to ‘no line’:
Step 8: Set Plot Area Fill Colour
Select the plot area and set a fill colour for the remaining squares:
Step 9: Hide Chart Title [Optional]
I don't need the title, so I'm deselecting it from the + menu, but you can keep it if you want:
Step 10: Remove Error Bar series from the legend
Select the legend item (left click twice to select), then press delete:
Step 11: Resize the chart
Lastly, resize the chart so that each segment is square. And there you have a Stacked Bar Excel Waffle Charts:
Thanks
Thanks to Nick Hillman, Associate Professor, University Wisconsin-Madison who shared his template for waffle charts here.
Jomili
I’m following the directions, but my results don’t match yours. On the horizontal error bars, if I set the direction as Both I get another set of columns to the left of the chart. Setting it to “Plus” corrects that. I’m also not getting the white lines coming all the way down. the Endpoints of the white lines follow the (invisible) line of the Error bar. I’d attach my chart or a picture of it but don’t know how.
Mynda Treacy
Hi Jomili, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
jomili
Hmm, are we missing steps 11 and 12? We go from m10 to 13.
Mynda Treacy
Oops! No, I misnumbered the steps. Fixed now 🙂