How to Create a Waffle Chart in Excel?
A waffle chart is a square grid chart that fills up to a certain percentage. It is used to visualize different types of data in a very simple yet effective way. One similar example of a waffle chart could be the Battery symbols on our modern-day smartphones. A waffle chart is a wonderful method of representation of numeric data, and it can be used as a replacement for a Pie Chart.
Implementing a Waffle Chart in Excel:
For this article, we are using Excel 2010. But, the following steps remain the same throughout all later versions of Microsoft Excel.
Step 1: Open Excel.
Step 2: Choose the Number of Columns & set the width. You must select entire columns, e.g., B up to K(10 columns). This can be done by Left-clicking on the column letter and then dragging over your mouse to the right for as many columns as you want (or you can hold L-Shift and press the Right arrow key for as many columns as required). This will be the width of your waffle chart. Now, Right Click on your selection and choose “Column Width” from the dropdown menu. The following dialog box will open. You can set it to a value of your choice. (I am choosing the value as 3)
Step 3: Fill the selected boxes from 1 to 100. Choose the bottom-right cell and assign it the value 1. Then, assign 2 to the cell on its left. Continue this all the way up to Column B and then continue with the number 11 in column B, the number 12 in column C, and so on till column K. Repeat this till 100 numbers. Now, set cell A2 with a random number between 1 and 100 (I am taking it as 25 for now).
Refer to this video to accomplish the above tasks easily.
Step 4: Setting up the Rules (0 to A2). Select all the cells numbered from 1 through 100, as shown above. Now, go to the Home Section and select “Conditional Formatting.” From the dropdown menu, choose “New Rule” and wait for the following dialog box to pop-up.
Now, select the second option i.e., “Format only cells that contain.” Now, fill the last two boxes (without quotation marks) with the following – “0” and “=$A$2” (i.e., the A2 cell) respectively and click on “Format.”
The above dialog box, titled, “Formal Cells” will open up. Now go to the “Fill” Section and Choose a color of your preference. Then navigate to the “Font” section and make sure you select the same color you chose a minute ago. This can be done by hovering over the “Color” section and clicking the down arrow near it.
Step 5: Setting up the rules (greater than A2). Now, we must repeat the same process for setting up the rules for all values greater than the one mentioned in A2.
Note: Make sure you choose the same color for the Font and the cells.
So, for choosing all values for all values greater than A2, all we need to do is make one change in Step 4. The rest is the same. Take a look at the image below:
Choose “greater than” and enter the value of A2 i.e., “=$A$2” and click format. After selecting the colors in the dialog box that follows, you’re all set with your Waffle Chart. It is recommended to choose lighter colors so that you can easily identify the difference between both colors.
Feel free to experiment with multiple values in A2 and see the changes in Excel for yourself.