Creating Waffle Charts in Excel with Conditional Formatting - dummies

Creating Waffle Charts in Excel with Conditional Formatting

By Michael Alexander

A waffle chart is an interesting visualization that helps display progress toward goals. This kind of chart is a relatively effective option when you want to add an interesting visualization to your Excel dashboard without distorting the data or taking up too much dashboard real estate.

As you can see below, a waffle chart is a square 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.

image0.jpg

A relatively easy way to build a waffle chart is to use conditional formatting. Here’s how:

  1. Dedicate a cell for your actual metric and a cell for any cumulative target you may have.

  2. Create a 10×10 grid of percentages that range from 1% to 100%.

    The metric cell (in this example, B5) will capture the actual performance. You can set up an optional target cell (B9), if you want to add an extra layer of coloring to represent quarterly or monthly cumulative target.

    image1.jpg

  3. Highlight your 10×10 grid and select Home→Conditional Formatting→New Rule.

  4. Create a rule that colors each cell in your 10×10 grid if the Cell Value is less than or equal to the value shown in the 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 10×10 grid are hidden.

    image2.jpg

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

  6. Create a rule that colors each cell in your 10×10 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 than that which you selected for the target cell.

    image3.jpg

  7. 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.

    image4.jpg

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

    image5.jpg

  9. 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.

    image6.jpg

  10. 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.

  11. 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.

    image7.jpg

  12. Overlay the text box on top of your waffle chart.

    image8.jpg

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