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

# Create Waffle Chart in Excel

If you one of those people who hate to use a pie chart in Excel then there is an alternate for you.

Waffle Chart

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

But, before you create a waffle chart you need to understand its components and important parts.

1. 100 Cells Grid: We need a square grid of 100 cells (10 X 10). We will use each cell to represent 1%.
2. Data Point: A data point from where we can take the completion percentage or achievement percentage.
3. Data Label: A data label to show the percentage of completion or percentage achievement.

## Pros

1. It gives a quick view of the progress of a project or about the achievement of target.
2. It looks good and you can easily use it in your dashboard.
3. You can easily deliver your message to the user without any extra explanation.

## Cons

1. Using more than one data point in waffle chart makes it complicated.
2. You have to spend a couple of minutes to create a waffle chart.
3. You can only present data in percentage.

## Steps

Waffle chart is not in the list of default charts. So we need to create it by using conditional formatting.

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

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

=(COLUMNS(\$A10:A\$10)+10*(ROWS(\$A10:A\$10)-1))/100

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.

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

### 4. Apply Conditional Formatting Rule

Once you create a data point, next you have to apply the conditional formatting rule on the grid.

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

### 5. Change Format

Now, select the grid and apply the following formatting.

1. Change font color to white.
2. Apply white border to cells in the grid.
3. 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.

### 6. Create a Label

Now, you need to create a dynamic label for the chart. Follow these simple steps for this.

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

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

1. Select your waffle chart (Grid).
2. Copy cells by using shortcuts key control + C.
3. Go to your dashboard sheet & use shortcut key Alt-H-V-I or Go to → Home Tab → Clipboard → Paste → Linked Image.

## Conclusion

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.

• Rashid Khan

Hello
The screenshots and formatting rules shown on the Web page and the sample file does not match
Also what formatting to be applied to show the font in White Color on the Waffle Chart
Thanks

• Emmanuel Martins Udu

You doing a nice job here