I've been toying around with Excel Waffle charts (sometimes called Square Pie Charts). It's an interesting visualization that I've recently used to display progress toward goal.
.
As you can see, a Waffle chart is basically a square is divided into a 10×10 grid. Each grid box represents 1% toward a goal of 100% percent. The number of grid boxes that are colored or shaded is determined by the associated metric. This kind of chart is a relatively effective option when you want to add an interesting visualization to your dashboard without distorting the data or taking up too much dashboard real estate.
There are several ways to achieve this visualization in Excel. My personal preferred method is to use an actual chart object. Although there are easier ways to implement this type of visualization (with conditional formatting in cells), using an actual chart object allows me to easily resize and move the visualization to fit my dashboard.
.
In this post, I'll walk you through the steps to set up a waffle chart template and how to duplicate it for as many metrics as you need.
.
Preparing the Data for your Waffle Chart
The first step is to create three ranges of data. Each range will play a part in building out the structure of your waffle chart.
- A range for Horizontal Lines: This range will help you draw the horizontal grid lines for your chart. Here, we simply enter the numbers 1 through 10.
- A range for Vertical lines: This range will help you draw the vertical grid lines for your chart. Here, we fill the entire range with 0.
- A Range for the Box Values: This Range will hold the values that determine which grid boxes get shaded. We'll fill this range with a formula.
.
To fill the Box Values, we enter this formula: MAX(MIN(E$3*100-($B5-1)*10,10),0)
Note the absolute reference designations (the $ signs) in the formula. These will enable us to easily copy and paste our formulas down and across when it comes time to duplicate our waffle char – more on that later.
.
As you can see in the screenshot below, this formula will take our actual metric value and parse it into groups of 10. Notice that the metric is 45%, and the Box Value range shows four 10s and one 5.
.
Changing the metric to 67% will cause the Box Value range to show six 10s and one 7.
.
At this point, we have all you need to start building your chart.
.
Building the your Waffle Chart
Creating the actual waffle chart can be a little tricky, but the good news is that once you have the chart built, you can easily duplicate it and point it to as many metrics you need.
Here are the detailed steps:
.
Step 1: Plot the Box Values into a Clustered Bar chart.
.
Step 2: Copy the ranges for the Horizontal and Vertical lines, click on the chart, and paste them in.
.
Step 3: Select the Plot Area and apply grey shading.
Note: In Excel 2013, you can right-click on the chart to see a dropdown of all the chart elements. In Excel 2007 and 2010, you can find this dropdown on the far-left of the Layout tab.
.
Step 4: Change the Chart Type for the Horizontal and Vertical Lines series to "Scatter with Markers".
.
Step 5: Click the series for the Horizontal Lines and add a "Y Values" range by pointing to the zeros in the Vertical Lines range. Tip: You can do this right in the formula bar.
.
At this point, your chart will look similar to this
.
Step 6: Set both the primary and secondary axes to a fixed max of 10.
.
Step 7: Delete all axis labels.
.
Step 8: Add Error Bars to both the Horizontal and Vertical Lines series.
.
Step 9: Select the "Horizontal Lines Y Error Bars" element and delete it.
Note: In Excel 2013, you can right-click on the chart to see a dropdown of all the chart elements.
In Excel 2007 and 2010, you can find this dropdown on the far-left of the Layout tab.
.
Step 10: Select the "Horizontal Lines X Error Bars" and format it to Plus – No Cap – Fixed value of 10.
.
Step 11: Select the "Vertical Lines X Error Bars" element and delete it.
.
Step 12: Select the "Vertical Lines Y Error Bars" and format it to Plus – No Cap – Fixed value of 10.
.
Step 13: Select the Vertical Lines series and set the Marker Options to NONE.
.
Your chart will now look similar to this.
.
Step 14: Format the Error Bars so that they are white.
.
Step 15: Highlight the Box Values series and set the Gap Width to 0.
.
Step 16: Expand the plot area so that it fills the entire chart, and then apply an appropriate color to the Box Values series.
.
Step 17: Optionally, you can make your chart title read back the actual metric value by clicking the chart title, then clicking inside the formula bar, then pointing to the cell holding the metric value.
.
At this point, you have completed your waffle chart.
.
Duplicating your Waffle Chart for more Metrics
As mentioned before, since the waffle chart takes some time and effort to create, you wouldn't want to build each one from scratch. Instead, you can simply duplicate your waffle chart and point it to a new set of data.
.
First, you will want to copy the range that holds your metric value and Box Values.
.
Now you can enter the appropriate metric value into you newly pasted range.
Next, copy your waffle chart and paste it as a new chart on the spreadsheet.
Finally, click the Box Values series for you newly copied chart and point it to the Box Values range for your new metric.
.
You can repeat this process for as many metrics you need to cover.
And remember, because these are charts, you can resize them to as large or small as you need them. You can also move them around as needed.
Feel free to Download the sample file to get a starter model with these charts already built out.
..
So how do these fit into dashboarding best practices?
Look, let's call a spade a spade here. These charts give you a way to jazz up performance against a goal. You could technically get the point across by merely showing the percentages.
I would say these are marginally better than standard gauges, because they can more be easily compared and they can be more effectively shrunk down to take up less real estate than gauges (because of their square-ness). Nevertheless, they still have the same drawbacks as gauges (they effectively show only one metric, they lack trending, etc.)
.
That being said I have found some success using these in real life dashboards where jazzing "performance vs. goal" was important to my customer. I can definitely use these Waffle charts with a relatively clear BI conscience.
