You may heard of “waffle” charts. They’re usually depicted with little blocks in a larger area to give an easy view of proportions. The idea is not any different from a pie chart, or an area chart, or even a treemap…it all depends on your audience and preference. However, it’s suitable for small number of categories or data series.
There are no built-in waffle chart control in Excel or PowerBI (at the time of writing), so we have to do some prep work ourselves. There are many different techniques to create the chart and with some experimentation, I’ve found my methods that I’m very satisfied with. Besides, my methods are not only not very tedious, but easily scalable, and addresses percentage depiction, raw numeric depiction, single or multiple data series, and even total integration with built-in Scatter plots. I won’t bore you here with step-by-step instructions as they are many different ones for each chart (albeit not too complicated) but I’ll share a high-level concept for each and their outputs. If you need more details, please see the footer in this post on how to contact me. Let’s get started.
The most common use is to show percentage complete, or percentage of sales, etc. The data is entered in a cell, and the chart is updated to easily show where we stand. Below is a great example:
This is achieved by formatting cells, specifically applying conditional formatting on the grid. The key is to set up the grid of cells so it’s 10×10 because we’re talking percentage…100% being the total. So, the data to enter for this chart would be a percentage value.
The above is simple and perfect for a single percentage type of scenario. But what if we have to deal with raw numbers? Let’s say you want to show a party’s attendance visually, by categories of men, women, and children. No problem! Much of the same techniques applied below will apply again. However, keep in mind that if your grid is 10×10 (that is, total 100 cells) then your data input max should be <=100. Obviously, if they’re larger, you can make the grid larger, or depict proportions (e.g. 1000, 2000, 3000, could be divided by 10 or 100 depending on your data range). Below is an example of this:
The thing to notice here is that we’re dealing with 3 different categories and data series. So, each one has to have its unique depiction (e.g. color in this case). Therefore, we have to employ 3 conditional formulas for this chart. The input values are just raw numeric figures, not percentages.
In the next scenario, we want to use the same type of chart but this time, instead of using colored square cells, we’ll use our own markers. To do this, we’ll need a chart…a scatter plot chart to actually plot the dots first. Once the dots are showing correctly, we’ll format the data points with custom icons, images or built-in shapes. In the following example, I use built-in circles. Of course, the color and sizes are all customizable.
Set up for this chart is different from the other two. We don’t create a 10×10 grid here, nor do we use conditional formatting. Instead we’ll use user-defined formulas (UDFs), and trick the chart to use our custom functions for X series and Y series. But what should be the formulas? Before answering that, we should understand how to set up X and Y series.
Data setup
As before, we’ll confine the chart axes to 10×10 for both X and Y. So, first we need to populate the X series values 1 to 10. For each of that the corresponding Y value must remain unique for that block of X. So, we can enter 1s for Y. Then we want to repeat it until we have 10 blocks of X (each 1-10) as we’re confining to a 10×10 area. However, for the second block of X, the corresponding Y values must remain static and unique. So, why not assign them 2? Repeat this and in the end, you’ll have Y set to 10 for the last block of X. Here’s a snapshot of the dataset.
Keep in mind, this has nothing to do with the value we’re going to show on chart! That value is user-input and will determine how many circles will appear in this chart. This is a critical concept. Once you get that, you can scale this chart to anything you want!
Now that we have the axes set up. Let’s get back to the tricky part…the UDFs. We’ll define 2 custom functions…call them what you want but I called them XFormula and YFormula (be sure to pick names that are not used in Excel’s library). The XFormula would be something like: =offset($E$7, 1,0, $H$4*100, 1)
where E7 is the HEADER cell for X, we offset row by 1, none for col, multiply value by 100 (if we’re entering the value to depict as a percentage, which we are in this example), and width is 1 since we’re moving by 1 cell only.
The YFormula will be offsetting from the same starting point but moving by 1 column, so =offset($E$7, 1,1, $H$4*100, 1).
Now that we have these UDFs, they need to connected to the chart and we’re in business! So, select the chart’s data and set Series X values to =Sheet1!XFormula (or whatever the name of the sheet is for your workbook), and Series Y values =Sheet1!YFormula
Now just see the “magic” happen (see the clip above). I hope this gives you some ideas about how waffle charts can be made without needing a built-in chart control.
BONUS CHART: MULTI-SERIES WITH MULTIPLE CUSTOM MARKERS ON SCATTER PLOT
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟