Building Waffle Charts in Excel

May 3rd, 2013 by datapig Leave a reply »

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.

Advertisement

13 comments

  1. Jeff Weir says:

    So it’s an overly obese bullet graph?

  2. Oz du Soleil says:

    I like this waffle chart.

    I’m following the steps and can’t get past Step 5. Both of my red and green scatter marks end up in the horizontal axis.

    Any ideas where I should look for my error?

  3. datapig says:

    Oz:
    An alternative way to do step 5 is:

    1. Select the “Horizontal Lines” series (the red axis markers)

    2. Right click and then choose Select Data

    3. In the dialog box that opens click the “Horizontal Lines” series from the list on the left

    4. Click the Edit button

    5. In the Series X Values input, point to the “Vertical Lines” range

    6. In the Series Y Values input, Point to the “Horizontal Lines” range

    7.Press OK to confirm the change and exit

  4. Steph says:

    In Excel 2010, I’m getting stuck on step 4: “Change the Chart Type for the Horizontal and Vertical Lines series to “Scatter with Markers”.”

    On the Design tab, when I select the ‘Change Chart Type’ button the option to change the series is not available… which makes me conclude I’m digging in the wrong section. I also selected the series and then I searched on the Design, Layout and Format tabs to see if I could figure it out, and no luck.

    Help me DataPig, you’re my only hope.

  5. datapig says:

    Steph: if you have Excel 2007 or 2010, simply right click the Hoizontal Line series, select change Chart Type, then select Scatter.

    Do the same with the Vertical series.

    The screens in 2010 and 2007 are a bit different than what is shown here (2013).

  6. datapig says:

    Steph: Also Feel free to download the sample file. This way, you can simply copy the completed chart, and point each series to YOUR data.

  7. ZY_ANK says:

    I like to do this with conditional format!

  8. Jeff says:

    Thanks – very helpful! The next challenge: how to make a multicolor waffle chart in Excel? :)

  9. Cos says:

    Is there a way to form a 100% waffle chart containing several items (percentages) with different colours? A bit like a pie chart.

  10. classroom says:

    I read this paragraph completely on the topic of the difference of latest and earlier technologies, it’s
    remarkable article.

  11. user says:

    Perfect…Thanks for spending time in making this tutorial…It helped a lot

Leave a Reply