Waffle Chart

Waffle chart in Excel adds the beauty of the visualization to the advancement path directed to your target.

It gives you a quick and clear visual signal of what you want to depict without the utilization of a lot of space and increases the readability of data in your dashboard.

Have you experienced the visualization of data or percentages? If yes, then you have probably revised charts before.

I met with this magic several times while I was reading newspapers and magazines. Another name for them is a pie chart or square pie chart.

Although, a square or waffle chart is an amazing alternative to other Excel Charts, the way of inserting it into your document can be confusing.

However, pasting the ready linked image is the best way to avoid misunderstanding; meanwhile, you can modify the dimensions of the chart as a bonus.

In the below-mentioned instance, there are three KPIs depicted in square pie charts.

Every waffle diagram is a square with a size of 10×10 cells (overall 100), where 1 cell corresponds to one percent of 100 cells. A waffle chart is a great choice to create a dashboard template.

waffle chart animated 02

The number of filled cells means how well you perform on the way to your maximum 100% destination.

The advantages of the Waffle Chart:

  • It is exciting and forwards the attention to your dashboard
  • It is visually clear and detectable. Every square provides various visual communication of attained target per KPI beyond simple visualization of data.
  • It is readable and portrayed data is noticeable at the first glance.
  • The difference between pie charts is that a waffle chart prevents the deformation of the displayed information.

The drawbacks of the Waffle Chart:

  • It is not only information presented as cells, but it also means the worth of the cell excluding all the tones and shades that it has.
  • The effort to create a Waffle chart in Excel is bigger than creating other columns or diagrams
  • It is not always comfortable to understand if you have more than 3 target points. As an example, the shown left graph is obvious when it has only 3 colored range, but when it boosts up to 10, it turns to be unfriendly to get the main idea.

We will try to create an advanced chart using a drop-down list and conditional formatting. It will be able to create variance charts too.

How to create a Waffle chart in Excel

We try to create upgrade-friendly charts by making their way to change while requesting more data points as the user gets more advancement in the process.

Let’s look at the below example:

waffle chart mini 03

1. Create the base Grid

We’ll use conditional formatting and special formulas to build a waffle chart.

First, we will select a grid of 10 rows and 10 columns. If it is done resize it to make it look like the grid as shown in the waffle charts.

waffle chart grid 04

In the 10X10 grid, enter the values with 1% in cell B11 and 100% in the top-right cell of the grid (K2 in our sample worksheet). We should enter it using a formula. It is a quick solution.

=(COLUMNS($B11:B$11)+10*(ROWS(B11:$B$11)-1))/100

waffle chart grid 05

Select the grid then go to the Home Tab. Select the Conditional Formatting –> New Rule option.

waffle chart rule 06

Now let us see the New Formatting Rule dialog box. Select the Rule Type. Select the “Format Only cells that contain” option. Choose the “Edit the rule description” section.

We can enter the values between 0 and N7. Cell N7 contains our linked value.

waffle chart rule 07

We will use the Format button to create an individual style for fill and the font color. Quick tip: Use same fill and font color to hide numbers in the grid area.

waffle chart rule 08

Now Click OK. This will create the specified format for the cells that contain a value less than or equal to the given value.

Finally, Apply the ‘All Border’ format using transparent (or white) border color. an outline to the grid with a gray ‘Outside Borders’ format. If it is necessary use the Outside Borders format to add an outline to the conditionally formatted area.

Within this step, you will customize the waffle chart to the net. The positive side is that the waffle chart is open to changes because it is connected to cell number N7.

In other words, any modification in cell N7 will lead to a new reformation of the whole chart. At this point, further attempts will be to introduce related labels to the KPI value in cell N7.

2. How to Create a Chart Label?

Go to the Insert tab on the ribbon then choose Text –> Text Box to place a text box in the worksheet.

waffle chart insert text 09

Select the text box then type the =N7 in the formula bar. We create a relationship between the text box and the cell.

waffle chart linked cell 10

Format the text box using custom text fill, text outline, and text effects. Finally, move it to the waffle chart area.

waffle chart mini 03

Although your waffle chart is not ready to use in the panel, you have finished the chart. As the next step, you should snip the chart and copy it to your panel as an image, so that you can easily modify the size and angle of view.

Using Linked Pictures to create a custom chart

Select the chart range and copy the cells into the clipboard. Go to Home Tab and use the following commands: Clipboard –> Paste –> Linked Picture

waffle chart 12

Our chart is ready as portrayed. You have the advantage of inserting it to the place you wish either in the same document or in other documents.

The only thing to remember is the factor that it is only the picture of the cells, so if there is any need to modernize the chart, the picture should be snipped and pasted again as it represents only the image of the cells and not the cells themselves.

If you are in hurry just download the example workbook.

Additional resources: