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.