In this post, you will learn how to create a Waffle Chart In Excel and how can you use it in your dashboards. You will also learn about benefits & limitations of it. Excel Waffle Chart is easy to create, looks good and the user can easily understand it. You can use this chart to track your project completion, show KPIs, sales achievement etc.
Components of Excel Waffle Chart
In below example, I have created a Dynamic Waffle Chart In Excel. All you have to do is, consider below-mentioned components while creating a waffle chart.
- 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
Waffle chart is not in the list of default charts of Microsoft Excel. So, we have to create a waffle chart before we use it. Here I am using Conditional Formatting to create this waffle chart in excel.
- 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 also be in square shape. Using squares is the reason it is also called square pie chart.
- Enter Values In Grid: Next, you have 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) . After that copy that formula to the entire grid.
Or, you can also insert percentage values manually into the cells.
- Data Point Cell: Now, you need a cell for the data point in which you can capture the percentage of completion or achievement and we will link that cell with waffle chart.
- Apply Conditional Formatting Rule: Once you create a data point, next you have to apply the conditional formatting rule on the grid. Please follow these simple steps to apply a rule.
- Select the entire grid.
- 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 & cell color to hide fonts when conditional will apply.
- Change Format: Now, you have to apply some formatting. select the grid & apply 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 have a waffle chart which is linked to a cell. And, when you change data in that cell chart will get automatically updated.
Create a Label for Waffle Chart
Now, we need a label for our waffle chart. We will create a label like a Dynamic Chart Title and link it to the data cell. Please follow these simple steps to create a dynamic label.
- Insert a text box in your worksheet. To insert it Go to → Insert Tab → Text → TextBox.
- Now select your text box & click in the formula bar.
- Enter cell reference of data point cell.
- Press enter.
- Increase the size of the font & place the text box on your chart.
Now, you have a dynamic label which will change its value when the value in data cell will change.
Add Excel Waffle Chart In Dashboard
Excel Waffle Charts looks good but inserting them in your dashboard is little tricky. But, you don’t have worry about it. The best way to add it into your dashboard is by creating linked image. And, you have a benefit of using this technique that you can change the size of the chart. We have two different options to create a linked image Excel Camera Tool or Paste Special Option. Please follow these simple to create a linked image of waffle chart.
- 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.
You can change the change the size of your chart as per your need.
Pros of using Waffle Chart In Excel
- It gives a quick view to a user about 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.
Cons of using Waffle Chart In Excel
- Using more than one data point in waffle chart makes it complicated. More data points you use in it more complicated it will be.
- You have to spend a couple of minutes to create a waffle chart.
- You can only present data in percentage.
Download Sample File
After understanding pros & cons of waffle chart you can use it in a perfect situation where you want to present a chart with a single data point. As you can make it dynamic it looks cool when in dashboards.
- Sales Funnel Template
- Population Pyramid In Excel
- Pictograph in Excel
- Star Rating Template
- Excel Productivity Guide | Free Excel Webinars | Excel Courses | Recommended Books