Stacked Bar Excel Waffle Charts

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

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.