How to Create Waffle Chart in Excel [Step by Step Guide]

If you one of those people who hate to use a pie chart in Excel then there is an alternative 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 of a Waffle Chart

Before you create a waffle chart in Excel 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.

Steps to Create a Waffle Chart in Excel [Super Fast]

Waffle chart is not in the list of default charts. So you need to create it by using conditional formatting and here are the steps:

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

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

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

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

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

Step-6: Create a Label

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

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.

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.

Content Protection by DMCA.com
2018-08-31T11:14:52+00:00
  • Tariq says:

    Very Useful tip Thanks for sharing this.

  • Sudhir Chaudhary says:

    Hi Can we use a triangle to represent the same data

  • Kiran Zatakia says:

    Wow…Simple but Impressive….Thanks for make it vary simple..

  • Emmanuel Martins Udu says:

    You doing a nice job here

  • Rashid Khan says:

    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

  • >