Excel Waffle Charts with Conditional Formatting

Excel Waffle Charts are a popular way to visually display parts to a whole. You can think of them as an alternative to pie and doughnut charts.

Waffle charts are better at displaying small segments that often get lost in a pie chart. However, that doesn’t mean you should get carried away with the number of segments you display in a waffle chart. I still recommend no more than 3. After that it’s too difficult to compare the segments.

There are a couple of ways we can build Excel waffle charts, and in this example we’ll look at Conditional Formatting individual cells based on United States Congress data as shown below.

Source: https://www.bbc.com/news/election-us-2020-54853289

Watch the Video

Download Workbook

Building Relative Excel Waffle Charts

We’ll start with a waffle chart that adds up to 100 or 100% using a 10 x 10 grid of cells, also known as relative waffle charts. The US Senate data works well for this example as there are 100 senators. Of course, it also works well for data that adds up to 100%.

Each cell in the waffle charts shown above is numbered from 1 to 100, starting in the bottom left. The numbers are hidden because the cells are too small to display them. However, If your cells are large enough to display the numbers, you can include font formatting colour in the conditional formatting rules for the fill colour (covered later) to hide them.

Waffle Chart Grid

We start by numbering the cells from 1 to 100. Those of us with Dynamic Array formulas can use the SEQUENCE function below to generate the numbers:

=SORT(SEQUENCE(10,10,1,1),,-1)

Enter the SEQUENCE formula in the top left cell, as shown in cell Z7 in the image below:

Note: If you don’t have Dynamic Array functions, you can use the formula below in the top left cell of your table and then copy and paste to the remaining cells in the 10 x 10 grid:

=COLUMNS($A1:A$10)+10*(ROWS($A1:$A$10)-1)

Tip: if you want percentages, divide the formulas by 100:

=SORT(SEQUENCE(10,10,1,1),,-1)/100

=(COLUMNS($A1:A$10)+10*(ROWS($A1:$A$10)-1))/100

Conditional Formatting Relative Waffle Charts

Before I apply the conditional formatting, set the cell fill colour for all cells to a light grey with a white border.

Next, apply the Conditional Formatting to set the cell fill colours based on the values for Democrats and Republicans. Select all of the cells in the 10 x 10 grid then go to the Home tab > Conditional Formatting > New Rule.

If we take the first waffle chart as the example, you can see in the image below that the red fill colour is set when the current cell number is less than or equal to the value for Democrats in cell B35 + the value for Republicans in cell B36:

Note: This will colour the cells numbered 1-24 red, but once the blue Conditional Formatting rule is set up it will override cells numbered 1-16.

Repeat the steps above to set up the Conditional Formatting for the blue fill colour, referencing the value in the helper cell B35:

Ensure the order of formatting rules is correct, with blue at the top of the list, then red:

Use the up/down arrows in the Rules Manager dialog box (shown above) to rearrange the order.

Lastly, adjust the row height and column width so that each segment is a square.

Building Absolute Excel Waffle Charts

Absolute waffle charts don’t necessarily add up to 100. Instead, each square or dot in the chart represents one unit, with the total number of squares or dots adding up to the total data.

In the example below there are 435 people who make up the United States House of Representatives and each chart displays 435 segments.

The formula for the grid is slightly different and will depend on the number of segments you want and the overall shape. To build a 20 row x 22 column grid, which is enough for 435 segments, use the following formula:

=SORT(SEQUENCE(20,ROUNDUP(435/20,0),1,1),,-1)

Tip: Change '435/20' in the ROUNDUP formula above to alter the number of columns returned to suite your needs.

If you don’t have dynamic arrays, you can use this formula:

=COLUMNS($A$1:A1)+22*(ROWS($A1:$A$20)-1)

And to display dots instead of squares, use an IF formula to return the dot symbol:

=IF((COLUMNS($A$1:A1)+22*(ROWS($A1:$A$20)-1))<=435,"●","")

You can modify the number of rows and columns in the formulas to suit your data and desired waffle shape.

Conditional Formatting Relative Waffle Charts

The Conditional Formatting for the squares uses the same logic:

There are only two Conditional Formats for the dots because the dot colour can be formatted with font formatting, which means you only need Conditional Formatting for the red and blue colours. However, notice the formula is more complex because the cells now contain dots instead of the numbers, therefore we need use the COLUMNS & ROWS formula to generate the cell numbers inside of the Conditional Format:

Waffle Chart Titles

Because the column width is tiny, I used a Text Box (Insert tab > Shapes) for my chart title.

Excel Waffle Chart Limitations

In these examples you’ll have noticed that each segment represents a whole number or whole percentage point. With this technique it’s not possible to show fractions of a percentage point, but in next week’s tutorial I’ll demonstrate an alternate way to build waffle charts that get around this limitation.