Waffle Charts with Conditional Formatting

For those who don’t know, a waffle chart is an interesting visualization that helps display progress toward goal. It’s basically a square divided into a 10×10 grid; each grid box representing 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.

.

A few months ago, I showed you how to build waffle charts using actual Excel chart objects. Today, I’ll show you a much simplified technique using little more than conditional formatting. With this technique, you can even get fancy and add more than one color, representing progress toward an intermediate goal. For example, the yellow boxes shown in this waffle chart represent actual performance, while the blue boxes represent where the metric should be at this time (quarterly goal, weekly goal, whatever).

.

Let’s walk through the steps:

 

Step 1: Set up the component pieces

Create a metric cell (in this example, B5) which will capture the actual performance.

Then set up an optional intermediate target cell (B9) if you want to add an extra layer of coloring to represent a quarterly or monthly intermediate target.

Finally, create a 10×10 grid of percentages that range from 1% to 100%.

 

Step 2: Create the Intermediate Target Grid Boxes

If you choose to include and intermediate target, it’s important to color those first.

Highlight your 10X10 grid and select Home>Conditional Formatting>New Rule.

Create a rule that colors each cell in your 10X10 grid if the Cell Value is less than or equal to the value shown in your intermediate target cell (B9 in this example). Click the OK button to confirm the conditional format. Be sure to apply the same color format for both the fill and the font. This will ensure the percentage values in your 10X10 grid are hidden.

 

Step 3: Create the Actual Performance Grid Boxes

With your 10×10 grid still highlighted, Home>Conditional Formatting>New Rule.

Create a rule that colors each cell in your 10X10 grid if the Cell Value is less than or equal to the value shown in the metric cell (B5 in this example). Click the OK button to confirm the conditional format. Again, be sure to apply the same color format for both the fill and the font. You will want to choose a different color that that which you selected for the target cell.

 

Step 4: Clean up the Formatting

Highlight all the cells in your 10×10 grid and apply a default gray color to the cells and font. Also apply a white border to all the cells.

At this point, your grid should look similar to the one shown here. When you change the metric or target percentages, your grid should automatically adjust colors to reflect the data change.

 

Step 5: Create a Linked Picture

Copy all the cells in your 10×10 grid, and then click the Paste dropdown arrow on the Home tab. Select the Linked Picture icon.

.

Excel will paste a picture of your grid to the worksheet. Resize the picture to the shape and size you need. The linked picture will automatically reflect the state of your actual grid. You can copy and paste this linked picture to your actual dashboard interface.

 

Step 6: Add a Dynamic Label

To add a label to the waffle chart, click on the Insert tab in the Ribbon, select the Text Box icon, and then click on your worksheet to create an empty text box. While the text box is selected, go up to the formula bar, type the equal sign (=), and then click the cell that contains your metric cell.

.

Overlay the text box on top of your waffle chart.

.

The reward for your efforts is an attractive graphic that helps your audience visualize performance against a goal.

8 thoughts on “Waffle Charts with Conditional Formatting

  1. Jon Peltier

    These waffle plots are a distraction. While pretty, they are not as effective as bar charts, because often you have to count little squares just to see which is larger. Of course, you’ve overcome this limitation by overlaying a label onto the waffle, in which case you can skip the chart and use the labels, or even easier, use some worksheet cells, in what some people refer to as a “table”.

    A nice bar chart is clearer, and probably takes up less space as well.

    I don’t mean to be an old curmudgeon, but I can’t help it. I don’t really think these waffle charts are worth the time and effort people waste on them. Now fried chicken and waffles, on the other hand…

  2. datapig

    Jon:
    Fried chicken and waffles are always worth the time!

    Better yet, Peanut Butter and Syrup on Waffles!

    Of course, you’re right.

    These things are what I like to call “TV for dogs”. They’re just a fancy way to show percentages. You could technically get the point across by merely showing the percentages themselves.

    That being said I have found some success using these in real life dashboards where jazzing up “performance vs. goal” was important to the customer.

    They don’t take up too much room and can’t really be used for evil to distort the data.

    I think we can can use these Waffle charts with a relatively clear BI conscience.

  3. Brian Slatas

    As you have said Jon, I agree completely that the Waffle charts are just a fancy way to show percentages and that is sometimes just what a dashboard needs.

    When it comes to showing comparisons like “performance vs. goal” or “my score vs. benchmark score,” have you found a good way to do this besides just showing 2 Waffle charts side by side?

  4. Deepa

    In step 4, how do we go about changing font colour inline with cell colour?
    Unable to achieve this in my worksheet. Please help.

  5. Deepa

    I got the simple solution in conditional formatting itself, sorry for spamming unnecessarily….

  6. Douglas Eckert

    This tool did not work for me. Whether it is as dubious as you say, is another matter. I just want to see if I can do this once.

    For some reason, the waffle squares would display just the first conditional format and not the second one. Suggestions are welcome.

Leave a Reply

Your email address will not be published. Required fields are marked *