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 in square shape 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).
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.
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.
In new rule window, select “Format only cells that contain”.
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.
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.
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.
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 button 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:
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.
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 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.
Now you have an INTERACTIVE CHART in your worksheet which you can use and control with the option buttons.
And in 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.
It looks good and you can easily use it in your dashboard.
You can easily deliver your message to the user without any extra explanation.
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.
When you enter it, it will return the number of cells that contain text.
...here's how this formula works
To understand this formula, you need to split it into three different parts.
First, you have used ISTEXT to check if a cell contains a text or not. Here ISTEXT returns an array where for all the cells in which you have TRUE for the cells with text and FALSE for all other cells.
Second, you have added double minus sign to convert the TRUE/FALSE array into 1/0. Now, for all the cells with the text, you have 1 and 0 for all others.
Third, you have used SUMPRODUCT to sum that array. And that sum is the number of cells which have text in them.
Using SUMPRODUCT here has a benefit that you don’t need to enter your formulas as an array.
3. Formula to Count Cells with Specific Text
Now, let’s dig a deeper little bit. Suppose you need to count the cells where you have a specific text.
Here is the example.
In the below data table, you need to count the cell where you have “John”. And for this, the formula will be:
RANKIF: Have you ever faced a situation where you have to rank values by using some specific [...]
Remove First Character: To remove first or n characters from a string you can use these 5 methods and the best part is […]
About the Author
He 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.
Top 100 Excel Functions with Examples - A Complete List Including Samples Files
I’m sure you agree with me that "Functions can make you great in Excel".
Well, once you master Excel basics this is the next thing you need to master. But the question is:
How you can learn any of these functions in the easiest way?
For this, I have this comprehensive list for you. In this list, you can find Top 100 Excel Function with Examples.
Every single function in this list is explained in a way that you can learn it in no time.
Here’s how this function guide can help you to master some of the most useful functions.
First, you will learn what's the purpose of a function and its syntax.
Next thing is arguments and how to declare an argument.
And in the end, its usage through a simple example so that you can use it in real life.
For example, if you want to learn about IF function, you have a quick intro, syntax description, some additional information and then an example.
In the end, you have links to the relevant sources to learn further. And all the functions are structured in the same way.
What’s an Excel Function (Worksheet)?
In Excel, a function is a predefined formula which performs a specific calculation by using values which a user input as arguments.
Every Excel function has a specific purpose, in simple words, it calculates a specific value.
Each function has its own arguments which you need to specify to get the result value in the cell.
Components of an Excel Function
To understand an Excel function you need to understand its components:
Every Excel function has its name, which actually helps you to identify its purpose, then you need to enter opening parentheses after that specify it’s arguments, and in the end, enter closing parentheses.
Type of Arguments
There are two different types of arguments:
Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
Optional: If you skip specifying these arguments it will not stop a function to calculate its result value.
In this functions guide, I’ll be using the square bracket for every optional argument to make you understand.
Quick Note: There are some functions in Excel which don’t require any arguments, in that case, all you need to do is create type opening and closing parentheses and hit enter.
How to Insert a Function in Excel
The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with a equals to sign.
So if you want to insert the SUM function:
First of all, you need to type = and the then type SUM.
After that, enter the opening parentheses.
Specify the arguments (refer to a cell or you can directly enter values into the function).
In the end, type closing parentheses and hit enter.
A Complete List of Top 100 Excel Functions with Examples which YOU should Learn this Year to 10X YOUR SKILLS in No Time