How to Create a Waffle Chart in Excel

The Waffle Chart emerges as a captivating masterpiece, transforming raw numbers into visual symphonies. Whether you'll illustrate work progress as a percentage of completion or showcase the balance between goals achieved and targets set, the waffle Chart unveils a canvas of insights with a mere glance.

What is a Waffle Chart

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. 

Advantages of Waffle Chart

The Waffle Chart has a number of advantages, rendering your data visualization journey not only captivating but profoundly effective. A few advantages are mentioned below:

Visual interesting:

Waffle Charts are like eye candy for your data. They turn boring numbers into a colorful, eye-catching pattern that's impossible to ignore.

Numbers that speak loud and clear:

They're like the superheroes of clarity. You can understand them with just a quick glance.

Find the Hidden Stories:

Waffle Chart are like treasure maps for your data. As you explore the colorful boxes, you'll discover hidden patterns and stories that numbers alone can't tell.

They does not disort the data:

Waffle Chart are straight shooters. They show the real numbers without any funny business.

Uses of Waffle Chart

Waffle Chart are your go-to when you've got your data that adds up to 100% and you want to showcase it in a supercool way.

Percentage Power:

These charts are all about percentages. You pick a number, and the Waffle Chart paints a picture by coloring a bunch of squares. The more squares are colored, the bigger the percentage.

Percentage of Progress:

Waffle Charts can be used to show off how much work is done.

Depict the Expenses incurred as against the budget:

Waffle Chart does not play hide and seek with numbers. They lay it all out. What you see is exactly what you get.

Seeing Progress:

Waffle charts helps you see how close you are to reaching them. It;s like a visual progress report that tells a story without words.

Passing the Test:

It also display the pass percentage in an exam in a college or school.

Creating 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 Sheet

To create a waffle chart in Excel Sheet, you need to open your excel sheet. You can open a new sheet and name it.

Step 2: Choose the Number of Column and Set the Width

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 Boxes

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: Set up the Rules

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 choose a minute ago. This can be done by hovering over the "Color" section and clicking the down arrow near it.

Step 5: Repeat the Process

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.

jaygala260

Follow

Improve

Article Tags :

• Excel
• ExcelGuide