Views > How to create a Waffle Chart in Excel using Conditional Formatting

One specialized graphical method using area as the visual mapping of cell frequency is the "fourfold display" designed for the display of 2x2 tables such as the chart from The Washington Post below. I’m going to reproduce this chart using worksheet cells and Conditional Formatting—rather than Excel charts.

1. On the worksheet, select columns I to AD. Right-click over the selected range and then, on the shortcut menu, click Column Width and set it to 2.
2. On the worksheet, select rows 8 to 29. Right-click over the selected range and then, on the shortcut menu, click Row Height and set it to 14. The worksheet should look like this:

5. On the worksheet, select cells J9:AC28. On the Home tab, in the Font group, click the arrow next to Borders icon, and then click More Borders.
6. In the Format Cells dialog box, on the Border tab, under Line and Color, choose the white color from the dropdown list. Then, under Presets, click the Outline and Inside buttons to indicate the border placement. Hence, I’ve defined four quadrants with 100 cells (10x10) each. The worksheet should look like this:

HOW TO CREATE A WAFFLE  FOURFOLD CHART IN EXCEL USING CONDITIONAL FORMATTING

7. On the worksheet, select cells S8:S29. On the Home tab, in the Font group, click the arrow next to Borders icon, and then click More Borders.
8. In the Format Cells dialog box, on the Border tab, under Line and Style, select a thick solid line from the style area. Then, under Border, click the right edge in the preview diagram.
9. On the worksheet, select cells I19:AD19. On the Home tab, in the Font group, click the arrow next to Borders icon, and then click More Borders.
10. In the Format Cells dialog box, on the Border tab, under Line and Style, select a thick solid line from the style area. Then, under Border, click the top edge in the preview diagram. The worksheet should look like this:

11. Type the numbers from 1 to 100 in the range T9:AC18 as per the following diagram:

12. To flip horizontally the numbers in cells T9:AC18 into cells J9:S18, add the following formula into cell S18
    = INDEX($T$9:$AC$18,ROWS($T$9:$AC$18) - ROWS(S$18:S18) + 1,COLUMNS($S18:S18))
13. Select cell S18, press Ctrl + C to copy the cell, then select cells J9:S18 and click Home > Paste and pick the Formulas paste option. The worksheet should look like this:

14. To flip vertically the numbers in cells J9:AC18 into cells J19:AC28, add the following formula into cell J19
   = INDEX($J$9:$AC$18,ROWS($J$9:$AC$18) - ROWS(J$19:J19) + 1,COLUMNS($J19:J19))
15. Select cell J19, press Ctrl + C to copy the cell, then select cells J19:AC28 and click Home > Paste and pick the Formulas paste option. The worksheet should look like this:

16. Add the data matrix in cells B2:E5 as per the screenshot below:

17. Highlight the range J9:S18 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
18. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
19. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
    = J9 > $D$44.
20. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click on the No Color button under Background Color.
21. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

22. Highlight the range T9:AC18 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
23. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
24. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
    = T9 > $E$44.
25. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click on the No Color button under Background Color.
26. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

27. Highlight the range J19:S28 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
28. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
29. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
   = J19 > $D$54.
30. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click on the No Color button under Background Color.
31. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

32. Highlight the range T19:AC28 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
33. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
34. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
  = T19 > $E$54.
35. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click on the No Color button under Background Color.
36. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

37. In the worksheet select the range J9:AC28. Next, on the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.
38. In the Format Cells dialog box, under Category, click Custom.
39. In the Type list, type “” or ;;;. When you have finished, click Ok. The worksheet should look like this:

40. In the worksheet select the range Q6:V7. Next, on the Home tab, in the Alignment group, click Merge & Center. Next, select Wrap Text.
41. In the merged cell Q6, type the formula = C4. Repeat the same procedure for the other ends of the vertical and horizontal axis by linking to cells C5, D3 and E3 respectively. The worksheet should look like this:

42. Turn off the worksheet gridlines. Add text boxes for the chart title, sources and annotations. Add The Washington Post logo. And your chart should look like this:

3. On the worksheet, select cells J9:S18. Right-click over the selected range and then, on the shortcut menu, click Fill Color icon and choose a color of your choice.
4. Repeat the same procedure for ranges T9:AC18, J19:S28 and T19:AC28 using different colors of your choice. The worksheet should look like this: