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

There are a lot of people out there who don’t like to use a PIE chart and they have their valid reasons for this.

And if you are one of those people, then let me introduce you with the WAFFLE CHART.

You can also call it a SQUARE PIE CHART. And today in this post, we will learn to create a WAFFLE CHART in Excel.

What is an Excel WAFFLE CHART?

In Excel, a Waffle chart is a set of grids (squares of equal area) which represents the entire chart. It works on a percentage basis where one square represents one percent of the whole.

Below is an example of a waffle chart which I have created in Excel. As I said it has a total of 100 square and each square represents one percent of the total value.

adding-small-label-with-creating-waffle-chart-in-excel-with-small-label

Components of a WAFFLE Chart

And, here are the main components of a WAFFLE Chart which you need to understand before you create it in Excel.

  • 100 Cells Grid: You need a square grid of 100 cells (10 X 10).
  • Data Point: A data point from where we can take the completion percentage or achievement percentage.
  • Data Label: A data label to show the percentage of completion or percentage achievement.

So now it’s time to learn how to create it in Excel. Well, you can use a static or a Dynamic (as per your need).

Let's get started.

Steps to Create a Static Waffle Chart in Excel

  • First of all, you need a grid of 100 cells (10 X 10) and the height and the width of each cell should be the same. The overall grid of cells should be in square shape and that’s the reason it’s called the square pie chart.
    creating-cell-grid-to-create-waffle-chart-in-excel-e1466071940501
  • After that, you need to enter values from 1% to 100% in cells starting from the first cell of the last row in the grid. You can use the following formula to insert the percentage from 1% to 100% in the grid (all you need to do is enter this formula in the first cell of the last row and after that copy that formula to the entire grid).
    =(COLUMNS($A10:A$10)+10*(ROWS($A10:A$10)-1))/100
    insert-percentage-in-grid-cells-to-create-waffle-chart-in-excel-1
  • Next, 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 in waffle chart further.
    linked-cell-to-create-waffle-chart-in-excel-1
  • Once you create a data point, next you need to apply the conditional formatting rule on this grid and 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 a 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
  • From here, you need to apply a final formatting touch and for this select the grid and do the following:
    • Change font color to white.
    • Apply a white border to cells in the grid.
    • Apply a 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 the chart will get automatically updated.
    ready-to-use-waffle-chart-in-excel.gif
adding-small-label-with-creating-waffle-chart-in-excel-with-small-label

What do you think? Say “Yes” in the comment section if you like this small label.

To add this label we need to follow below steps:

  • First of all, select the entire chart grid and go to the Highlight Cell Rules ➜ Equals.
    adding-small-label-with-creating-waffle-chart-in-excel-open-equals-to
  • Now in the equals to the dialog box, in “Format cells that EQUALS to” select the cell where we have our percentage value.
    adding-small-label-with-creating-waffle-chart-in-excel-select-cell
  • After that, open “Custom Formatting” and go to the “Font” tab.
    adding-small-label-with-creating-waffle-chart-in-excel-apply-font-color
  • From here in the font tab, select the “White” font color and click OK.
    adding-small-label-with-creating-waffle-chart-in-excel-slect-black
adding-small-label-with-creating-waffle-chart-in-excel-with-small-label

Steps to Create an INTERACTIVE Waffle Chart in Excel

At this point, you know how to create a WAFFLE chart but there’s a lot of questions I got about making it an interactive one.

If you think like this, one of the most important thing you should have in an interactive chart is how you control it and you should able to change data.

So in this section of the post, I’d like to share with you steps to create an interactive WAFFLE chart in which you can change data with OPTION Buttons.

So let’s make it INTERACTIVE.

  • First of all, you need to insert five option buttons into the worksheet and for this go to the DEVELOPER Tab ➜ Insert Option Buttons.
    create-waffle-chart-in-excel-interactive-insert-option-buttons
  • After that, you need to connect those option buttons to a cell. So when you select a button that cell can have a number which we can use to extract data from the main table.
  • For this, select all the option buttons and right click and then select “Format Control” (You can also group all the option buttons by using the GROUP option).
    create-waffle-chart-in-excel-interactive-connect-option-buttons
  • Now the next thing is to create a formula and insert it into the achievement cell so that when you select an option button it returns the value for that particular product.
  • So that formula we need here would be like below:
    =INDEX(R6:R10,P3)
  • Enter the above formulas in the achievement cell. In this formula, R6:R10 is the range where you have achievement values and P3 is the cell which is connected with the option buttons.
    create-waffle-chart-in-excel-interactive-enter-formula
  • There’s one more thing which we need to do and that’s creating a dynamic label for the chart (at this point, we have a data label which is connected to the achievement cell but we need to make it dynamic).
  • For this, we need to enter the below formula in the cell next to the achievement cell.
    ="Target Achieved for "&INDEX(Q6:Q10,P3)
    create-waffle-chart-in-excel-interactive-enter-label-formula
  • After that, insert a simple text box which you need to connect with the cell where you just added the above formula and for this select that text box and click on the formula bar and type the address of the cell where you have the formula.
    create-waffle-chart-in-excel-interactive-connect-label

Now you have an INTERACTIVE CHART in your worksheet which you can use and control with the option buttons.

Add a Waffle Chart in a Dashboard

WAFFLE chart looks good but inserting it in a dashboard can be tricky sometimes. But, you don’t have to worry about that.

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

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

In the End

There’s no doubt that a WAFFLE chart is easy to create, looks good and the user can easily understand it.

It is perfect if you need to track a KPI which is critical for your business and if you have more than one KPI then you can go with the INTERACTIVE version.

You can also use this chart to track your project completion.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

8 thoughts

Leave a Comment

Your email address will not be published.

  1. Very great and useful for sales achivement goals and how to insert in target figure in this chart

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