Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
If you one of those people who hate to use a pie chart in Excel then there is an alternate 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.
But, before you create a waffle chart you need to understand its components and important parts.
Waffle chart is not in the list of default charts. So we need to create it by using conditional formatting.
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.
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.
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.
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.
Now, select the grid and apply the following formatting.
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.
Now, you need to create a dynamic label for the chart. Follow these simple steps for this.
Congratulation! your waffle chart is ready.
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.
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.