Waffle chart in Excel add 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 utilization of a lot of space and increasing 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 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 to 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 chart as a bonus.
On the below-mentioned instance, there are three KPI’s 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. Waffle chart is a great choice to create kpi dashboard.
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 during the first glance.
- The difference with pie chart is that waffle chart prevents the deformation of the displayed information.
The drawbacks of 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.
How to create Waffle chart in Excel
We try to create upgrade-friendly charts by making them way to change while requesting more data points as user gets more advancement in process.
Let’s look at below example:
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.
In the 10X10 grid, enter the values with 1% in the 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.
Select the grid then go to the Home Tab. Select the Conditional Formatting –> New Rule option.
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.
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.
Now Click OK. This will create the specified format to the cells that contains a value less than or equal to the given value.
Finally Apply ‘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 conditional formatted area.
Within this step, you will customize waffle chart to the net. The positive side is that the waffle chart is open to changes because it connected to the cell number N7.
In other words, any modification in the cell N7 will lead to new reformation of the whole chart. At this point, further attempts will be to introduce related labels to the KPI value in the cell N7.
2. How to Create a Chart Label?
Go to Insert tab on the ribbon then choose Text –> Text Box to place a text box in the worksheet.
Select the text box then type the =N7 in the formula bar. We create a relationship between the text box and the cell.
Format the text box using custom text fill, text outline and text effects. Finally move it in the waffle chart area.
Although your waffle chart is not ready to use in the panel, you have finished chart. As 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
Our chart is ready as portray. You have an 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.