Waffle charts are the new kids on the block when it comes to data visualization in Excel. Unlike the trusty pie chart that often gets a bad rap for being clunky and hard to read, especially with small segments, a waffle chart, essentially a square pie chart, offers a fresh and organized way to represent proportions.
I’ve seen the grumbles about pie charts; sometimes they just don’t cut it. That’s where Excel’s versatility shines. Creating a waffle chart is a great alternative, with a clear display that’s as straightforward as it is visually appealing. So, let’s ditch the round pastries and get squared away with how to churn out a waffle chart in a few simple steps.
What is an Excel WAFFLE CHART?
An Excel Waffle chart is essentially a collection of 100 squares, where each square visualizes one percent of data, aiding in a clear representation of proportions. It’s like a visual pie chart, but with a twist—each square equals a bite-sized piece of the whole pie.
Here’s a rundown:
- Visual Design: Grid layout, 10×10 squares.
- Representation: Each square = 1% of the total.
- Purpose: Illustrates percentages for easy comparison.
And yes, I’ve created one in Excel too, check it out:
Components of a WAFFLE Chart
Here’s the lowdown on what makes up a WAFFLE chart in Excel:
-
Grid: I start with a 10 x 10 grid, which gives me 100 square cells. Each square represents 1%.
-
Cells: The cells within the grid show the percentage value. I customize the fill color based on the data.
-
Conditional Formatting: This lets me change cell color when certain conditions are met, highlighting data changes.
-
Label: Atop the grid, a text box is often placed to display the data label, indicating what percentage is represented.
-
Formatting Rule: I create rules for formatting text, borders, and number formatting to emphasize the chart details.
Adjusting row height and column width helps maintain the square shape of each grid cell, while font size and color make the chart readable. Formulas link the cells to the data point, ensuring the chart reflects real-time data.
Steps to Create a Waffle Chart in Excel
Creating a waffle chart in Excel is sort of like baking an actual waffle—there’s a bit of preparation, some cooking magic, and then you get something really sweet to present. Let’s dive in.
Prep the Grid
First up, I make a 10 by 10 grid in my worksheet—that’s a neat square of 100 cells. Every cell represents 1%, with the whole grid together accounting for 100%. I make sure that all cells are equally sized to keep it looking tidy.
Fill It Up
For the actual data, I need to fill in each cell from 1% to 100%. So, I start in the bottom left cell of my grid—let’s call it home base—and enter a simple formula to calculate this percentage across the grid. The formula looks something like this:
=(COLUMNS($A10:A$10)+10*(ROWS($A10:A$10)-1))/100
Bingo, home base and its friends are now showing sequential percentages from 1% to 100%.
Link to the Data
I pick a nearby cell outside the grid to serve as a dynamic data point—let’s say it’s a cell that measures my progress on the latest project. This cell’s value will reflect on the waffle chart.
Some Formatting Magic
Next step: conditional formatting to visualize the data. This is where my chart starts getting personality.
Following that, I throw in some final touches to the grid:
- Change all font colors to white.
- Add a neat white border around each cell.
- And, of course, frame the whole thing with a bold, solid border for that oomph.
Now, any change in my data cell automatically updates my waffle chart. It’s like watching dough rise, but better.
Adding Labels
Let’s not forget labels, the unsung heroes. I insert a textbox, scale it right, and link it to my data cell so it updates as I go along. If I’m feeling fancy, I add a small label to the chart for that extra crisp detail—a direct hit to the chart’s value.
You Did It!
And there we have it, my freshly made waffle chart, ready to serve at any data breakfast. If you want to follow along with my exact steps, grab the sample file and give it a whirl.
Download Sample File – Static Waffle Chart
Look at that, we’re chart chefs now. Go ahead and show off this delicious piece of Excel artistry in your next report or presentation.
Steps to Create an INTERACTIVE Waffle Chart in Excel
Here’s how you can make your waffle chart interactive in Excel. Interactive charts are fantastic because they allow you to change and control the data presented, making them much more dynamic and engaging for the user.
-
I start by inserting five option buttons into my worksheet. I do this via the Developer tab, clicking on Insert, and then Option Buttons. To make our chart interactive, these buttons are essential since they let us switch between different data sets.
-
Next, I need to make the buttons useful by linking each to a specific cell. This way, when I choose a button, it’ll correspond to a number which then fetches data from my main table.
-
To link, I select all the buttons, right-click, and choose “Format Control”. This enables me to set up a cell that updates whenever an option is selected.
-
It’s all about labeling now. Each button needs to get a name, and I do this by right-clicking the button and editing the text. You can make this easier by downloading a sample file with buttons already labeled for you.
-
For interactivity, a formula is needed to display the correct data when a button is pressed. What I use is:
=INDEX(R6:R10,P3)I enter this formula in the achievement cell, where R6 is where the data resides and P3 is linked to the option buttons.
-
To add clarity to my chart, I create a dynamic label using the formula:
="Target Achieved for "&INDEX(Q6:Q10,P3)This formula crafts a label based on the product selected with the option buttons.
-
Finally, I insert a text box to display this dynamic label. I connect it by selecting the text box, clicking in the formula bar, and typing the cell address with the formula we created in the previous step.
And there you have it! My worksheet now showcases an interactive waffle chart, and by using the option buttons, I can toggle between data sets to display different information for the products dynamically.
If I ever need to update the chart with new data, all I’d have to do is add a new option button and update the range in my formulas. Feel free to download the sample file to try this out for yourself and get a better idea of how it works.
Add a Waffle Chart in a Dashboard
Pros
- I love how it effortlessly showcases the completion level, like showing my project’s progression. It really pops on the dashboard.
- The waffle chart isn’t just eye-catching; it’s a breeze to integrate into my dashboards. Plus, resizing is a snap thanks to linked images. Using Excel’s Camera Tool keeps it flexible.
- It’s straightforward. Just a glance and everyone gets the gist. No need for lengthy explanations.
Cons
- Trying to represent multiple datasets? That’s a no-go; my waffle chart starts looking like a confusing Sudoku puzzle.
- I’ll admit, it’s not a plug-and-play job. Carving out those few minutes to set it up is a must.
- Percentage is the name of the game with waffle charts. It’s all or nothing, really. It’s got no room for those nitty-gritty number crunching details.
In the End
When I’m tracking my KPIs, I’ve found waffle charts in Excel to be super handy. They’re simple to create and interpret—perfect for keeping an eye on business-critical metrics. For those times when I’ve got multiple KPIs to monitor, nothing beats an interactive waffle chart for staying on top of project progress. Here’s how they add value:
- Pie Charts: Traditional but limited for KPI tracking.
- Waffle Charts: Squared fun with simple visuals for KPI clarity.
- Bullet Charts: Great for performance against targets.
- Advanced Excel Charts: For nuanced data insights.
- Sankey Diagrams: Ideal for visualizing flow and relationships.
- Automation Tools: Office Scripts and Power Automate can streamline chart updates.
- Management Insight: These charts provide managers with a clear view of project and KPI status at a glance.
