If you one of those people who hate to use a pie chart in Excel then there is an alternative for you.
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.
Components of a Waffle Chart
Before you create a waffle chart in Excel you need to understand its components and important parts.
- 100 Cells Grid: We need a square grid of 100 cells (10 X 10). We will use each cell to represent 1%.
- 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.
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.
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.
You can use following formula to insert percentage from 1% to 100% in the grid.
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.
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.
- In new rule window, select “Format only cells that contain”.
- 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.
- 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.
Step-5: Change Format
Now, select the grid and apply the following formatting.
- Change font color to white.
- Apply white border to cells in the grid.
- 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.
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.
- Enter cell reference of data point cell and press enter.
- Increase the size of the font and place the text box on your chart.
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.
- Select your waffle chart (Grid).
- Copy cells by using shortcuts key control + C.
- Go to your dashboard sheet & use shortcut key Alt-H-V-I or Go to ➜ Home Tab ➜ Clipboard ➜ Paste ➜ Linked Image.
- It gives a quick view of the progress of a project or about the achievement of target.
- 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 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.
Download this sample from here to learn more.
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.