Contents
A Waffle Chart is one of the types of charts commonly used to visualize progress. The logic here is extremely simple and obvious – the more filled squares, the closer to the goal:
On the move, you can think of a bunch of situations where such a diagram would be “in the subject”. For example, with its help it is convenient to visualize:
- project progress
- various KPIs in any business
- occupancy of areas or volumes (warehouses, construction…)
- … etc.
One problem: Microsoft Excel does not have this type of chart among the built-in features. However, this limitation can be bypassed quite easily and even in more than one way.
Method 1: Waffle Chart with Conditional Formatting
We frame a plate of 10×10 square cells and fill it (by copying, and not manually, by itself) from bottom to top with increasing values from 1% to 100%
Then select the entire marked range (D3:M12) and select Home — Conditional Formatting — Create Rule (Home — Conditional Formatting — Create Rule).
In the window that opens, select the type of rule Format only cells that contain (Format only cells than contains), a little lower in the drop-down list, select the option Less or equal (Less or equal) and indicate next to the link to the cell with the original value ($B$2). Set the fill color by clicking on the button Framework (Format):
After clicking on OK we get an almost finished diagram:
For greater beauty, you can hide the percentage values in the cells of the table by setting it in the window Cell format (Format cells) tab Number (number) custom format consisting of three semicolons in a row:
Then on the tab Insert (Insert) press the button WordArt and, having chosen the inscription design that you like and inserting it on top of our table, we enter the “equal” sign into the formula bar and make a link to the cell with the original value:
We get an inscription, the text of which is automatically updated from cell B2, displaying the current value of our parameter on top of our “waffle”.
Method 2: Waffle Chart from Bar Chart
This way of creating a waffle chart is based on cutting it out of the standard bar chart built into Excel (horizontal bar chart). However, first we have to prepare a table – the data source for the future chart.
We start by creating a percentage series from 0% to 100% in 10% increments (range A4:A13). Then we add a column to it with the calculation of the difference between the values of the series and our original value, which needs to be visualized from cell B2:
Then add a column with nested functions IF (IF)to implement the following logic:
- negative values are replaced by 0
- values greater than 10% by 10
- the rest are displayed as is, but we add multiplication by 100 (because percentages in Excel are numerical values from 0 to 1, and we need to get numbers from 0 to 10 at the output)
Select the last calculated column (range C4:C13) and build a bar chart on it on the tab Insert (Insert):
… and we get this picture:
It remains to make this diagram more like a waffle. For this:
- Right-click on the blue columns, select the option Data series format (Format data series) and remove Side clearance (Gap width) down to zero. The columns become as wide as possible and merge into a single whole:
- In the same place, on the formatting tab, we set a translucent fill for the blue columns:
- Click on the horizontal axis with the right mouse button, select the command Axis Format (Format axis) and set the limits from 0 to 10 and the step of the main divisions equal to 1:
- Using the plus sign icon in the upper right corner of the chart, turn off the chart title, axes, axes titles, and vice versa, add the main vertical and horizontal grid lines:
Additionally and if desired, you can also adjust the color of the grid lines, making them brighter.
- We add a caption with the current percentage on top of the chart, tied to the original cell B2 – as we did in the previous method:
That’s all – the waffle is ready 🙂
Nuances
A couple of nuances and life hacks in pursuit:
- If, after creating the chart, you want to hide the auxiliary table A4:C13, leaving only cell B2 with the original value, then it is better to right-click on the chart, then the command Select Data – Hidden and Empty Cells (Select data source — Hidden and Empty cells) and enable the checkbox Show data in hidden rows and columns (Show data from hidden rows and columns). Otherwise, after hiding the source data, the diagram will also disappear.
- If you have to do this type of diagram more than once, then it makes sense to save the created diagram as a template by right-clicking on it and selecting the command Save as template (Save as Template):
After that, a waffle chart (according to the prepared table!) Can be created by selecting Excel chart types in the standard window, finding it in the section Patterns (Templates):
That’s all. Now you can make waffles not only in the kitchen, but also in Excel 🙂
- What can conditional formatting do in Excel
- Plan-fact charts in Microsoft Excel
- Simulate histograms with icons with REPEAT and CHAR functions