Create Waffle Chart in Excel

If you one of those people who hate to use a pie chart in Excel then there is an alternate for you.

Waffle Chart

A set of grids (squares of equal area) which represents the entire chart. It is also called a square pie chart.

In the below example, we have a dynamic waffle chart which you can control with a drop down list.

Excel Waffle Chart In Excel Using Cells Grid And Label For Percentage

Components

But, before you create a waffle chart you need to understand its components and important parts.

  1. 100 Cells Grid: We need a square grid of 100 cells (10 X 10). We will use each cell to represent 1%.
  2. Data Point: A data point from where we can take the completion percentage or achievement percentage.
  3. Data Label: A data label to show the percentage of completion or percentage achievement.

Pros

  1. It gives a quick view of the progress of a project or about the achievement of target.
  2. It looks good and you can easily use it in your dashboard.
  3. You can easily deliver your message to the user without any extra explanation.

Cons

  1. Using more than one data point in waffle chart makes it complicated.
  2. You have to spend a couple of minutes to create a waffle chart.
  3. You can only present data in percentage.

Steps

Waffle chart is not in the list of default charts. So we need to create it by using conditional formatting.

1. Create a Grid

First of all, you need a grid of 100 cells (10 X 10). And, height and width of each cell should be same.

The overall grid of cells should be in square shape. Using squares is the reason it is also called square pie chart.

Creating Cell Grid To Create Waffle Chart In Excel

2. Enter Values in the Grid

Next, you need to enter values from 1% to 100% in cells starting from the first cell of the last row in the grid.

Insert Percentage In Grid Cells To Create Waffle Chart In Excel

You can use following formula to insert percentage from 1% to 100% in the grid.

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

All you have to do, enter this formula in the first cell of the last row (change cell references in your formula) and after that copy that formula to the entire grid.

3. Data Point Cell

Now, you need a cell for the data point in which you can capture the percentage of completion or achievement.

You need to link this cell with waffle chart further.

Linked Cell To Create Waffle Chart In Excel

4. Apply Conditional Formatting Rule

Once you create a data point, next you have to apply the conditional formatting rule on the grid.

For this, please follow these simple steps to apply a rule.

  • Select the entire grid and go to → Home Tab → Styles →Conditional Formatting → New Rule.
Add New Rule Form Conditional Formatting To Create Waffle Chart In Excel
  • In new rule window, select “Format only cells that contain”.
Add Rule To Cell That Contain To Create A Waffle Chart In Excel
  • Now in “format only cells with” specify the values 0 as minimum and $M$4 as maximum values.
  • This rule will apply the conditional formatting to the cells which have the value between this value range.
Add Values To Create Rule To Create Waffle Chart In Excel
  • Click on format button to specify a format to apply.
  • Make sure to apply the same color for font and cell color to hide fonts when conditional will apply.
Apply Format In Conditional Formatting To Create Waffle Chart In Excel

5. Change Format

Now, select the grid and apply the following formatting.

  1. Change font color to white.
  2. Apply white border to cells in the grid.
  3. Apply solid outer border to grid with black color.

After doing this you will get a waffle chart which is linked to a cell. And, when you change data in that cell chart will get automatically updated.

Ready To Use Waffle Chart In Excel

6. Create a Label

Now, you need to create a dynamic label for the chart. Follow these simple steps for this. 

  • Insert a text box in your worksheet, for this Go to → Insert Tab → Text → TextBox.
  • Select the text box and click inside the formula bar.
Add Label To Create Waffle Chart In Excel
  • Enter cell reference of data point cell and press enter.
link text box with cell to create waffle chart in excel
  • Increase the size of the font and place the text box on your chart.
put label on chart to create waffle chart in excel

Congratulation! your waffle chart is ready.

Add a Waffle Chart in a Dashboard

A waffle chart looks good but inserting it in a dashboard is tricky sometimes. But, you don’t have worry about that.

The best way to add it is to create a linked image with camera tool or by pasting spacial. The benefit of using this technique that you can change the size of the chart.

Follow these simple steps.

  1. Select your waffle chart (Grid).
  2. Copy cells by using shortcuts key control + C.
  3. Go to your dashboard sheet & use shortcut key Alt-H-V-I or Go to → Home Tab → Clipboard → Paste → Linked Image.

Sample File

Download this sample from here to learn more.

Conclusion

It’s is easy to create, looks good and the user can easily understand it. You can use this chart to track your project completion, in KPIs, sales achievement etc.

  • Rashid Khan

    Hello
    The screenshots and formatting rules shown on the Web page and the sample file does not match
    Also what formatting to be applied to show the font in White Color on the Waffle Chart
    Thanks

  • Emmanuel Martins Udu

    You doing a nice job here