Steps to Create a Static Waffle Chart in Excel

WAFFLE chart is not in the list of Excel’s default charts, in fact, one of the ADVANCED charts which you create of your own.

Below are the steps you need to follow to create a 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 square and that’s the reason it’s called the square pie chart.

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

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 the waffle chart further.

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.

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.

Download this sample file where we have a STATIC WAFFLE CHART + achievement data for 5 products and we need to use create a WAFFLE chart where we can present all these in an interactively.

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.

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

Next, you need to name all the five option buttons as per the product names you have. Simply right-click and edit text (I’ve done this for you and you can download this file from here).

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 that is connected with the option buttons.

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)

After that, insert a simple text box that 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.

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

And in the future, if you want to update the data you just need to add a new option button and update the data ranges in the formulas.

Pros

You can easily deliver your message to the user without any extra explanation.

Cons

Using more than one data point in the waffle chart makes it complicated.

You have to spend a couple of minutes to create a waffle chart.

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 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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

10 thoughts

Hello, really nice work. I’ve found an errata in the formula posted for the waffle. If I want to have the same chart as in example the formula I have to use is: =(COLUMNS($A$1:A$10)+10*(ROWS($A1:A$10)-1))/100 explanation for first cell B2: formula step. COLUMNS($A$1:A$10) 1 10*(ROWS($A1:A$10)-1 10*10-1=90 1+90=91 91/100=0.91 or 91%

Thanks a lot puneet. Explanation is very well explained. Just have a quick question:

How can I have this control information in PPT? Example: I want to have those option button in PPT, when I change from one option to other option – figure will change automatically in PPT as well as excel.