• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Waffle Charts with Conditional Formatting

You are here: Home / Excel Charts / Excel Waffle Charts with Conditional Formatting
Excel waffle Charts
December 3, 2020 by Mynda Treacy

Excel Waffle Charts are a popular way to visually display parts to a whole. You can think of them as an alternative to pie and doughnut charts.

Waffle charts are better at displaying small segments that often get lost in a pie chart. However, that doesn’t mean you should get carried away with the number of segments you display in a waffle chart. I still recommend no more than 3. After that it’s too difficult to compare the segments.

There are a couple of ways we can build Excel waffle charts, and in this example we’ll look at Conditional Formatting individual cells based on United States Congress data as shown below.

Excel Waffle Charts

Source: https://www.bbc.com/news/election-us-2020-54853289

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Building Relative Excel Waffle Charts

We’ll start with a waffle chart that adds up to 100 or 100% using a 10 x 10 grid of cells, also known as relative waffle charts. The US Senate data works well for this example as there are 100 senators. Of course, it also works well for data that adds up to 100%.

10 x 10 Excel Waffle Charts

Each cell in the waffle charts shown above is numbered from 1 to 100, starting in the bottom left. The numbers are hidden because the cells are too small to display them. However, If your cells are large enough to display the numbers, you can include font formatting colour in the conditional formatting rules for the fill colour (covered later) to hide them.

Waffle Chart Grid

We start by numbering the cells from 1 to 100. Those of us with Dynamic Array formulas can use the SEQUENCE function below to generate the numbers:

=SORT(SEQUENCE(10,10,1,1),,-1)

Enter the SEQUENCE formula in the top left cell, as shown in cell Z7 in the image below:

Excel Waffle Chart grid formula

Note: If you don’t have Dynamic Array functions, you can use the formula below in the top left cell of your table and then copy and paste to the remaining cells in the 10 x 10 grid:

=COLUMNS($A1:A$10)+10*(ROWS($A1:$A$10)-1)

Tip: if you want percentages, divide the formulas by 100:

=SORT(SEQUENCE(10,10,1,1),,-1)/100
=(COLUMNS($A1:A$10)+10*(ROWS($A1:$A$10)-1))/100

Conditional Formatting Relative Waffle Charts

Before I apply the conditional formatting, set the cell fill colour for all cells to a light grey with a white border.

Next, apply the Conditional Formatting to set the cell fill colours based on the values for Democrats and Republicans. Select all of the cells in the 10 x 10 grid then go to the Home tab > Conditional Formatting > New Rule.

If we take the first waffle chart as the example, you can see in the image below that the red fill colour is set when the current cell number is less than or equal to the value for Democrats in cell B35 + the value for Republicans in cell B36:

Excel Waffle Chart conditional formatting rule

Note: This will colour the cells numbered 1-24 red, but once the blue Conditional Formatting rule is set up it will override cells numbered 1-16.

Repeat the steps above to set up the Conditional Formatting for the blue fill colour, referencing the value in the helper cell B35:

Excel Waffle Chart conditional formatting rule

Ensure the order of formatting rules is correct, with blue at the top of the list, then red:

conditional formatting rules manager

Use the up/down arrows in the Rules Manager dialog box (shown above) to rearrange the order.

Lastly, adjust the row height and column width so that each segment is a square.

Building Absolute Excel Waffle Charts

Absolute waffle charts don’t necessarily add up to 100. Instead, each square or dot in the chart represents one unit, with the total number of squares or dots adding up to the total data.

In the example below there are 435 people who make up the United States House of Representatives and each chart displays 435 segments.

Excel Waffle Charts

The formula for the grid is slightly different and will depend on the number of segments you want and the overall shape. To build a 20 row x 22 column grid, which is enough for 435 segments, use the following formula:

=SORT(SEQUENCE(20,ROUNDUP(435/20,0),1,1),,-1)

Tip: Change '435/20' in the ROUNDUP formula above to alter the number of columns returned to suite your needs.

If you don’t have dynamic arrays, you can use this formula:

=COLUMNS($A$1:A1)+22*(ROWS($A1:$A$20)-1)

And to display dots instead of squares, use an IF formula to return the dot symbol:

=IF((COLUMNS($A$1:A1)+22*(ROWS($A1:$A$20)-1))<=435,"●","")

You can modify the number of rows and columns in the formulas to suit your data and desired waffle shape.

Conditional Formatting Relative Waffle Charts

The Conditional Formatting for the squares uses the same logic:

conditional formatting rules manager

There are only two Conditional Formats for the dots because the dot colour can be formatted with font formatting, which means you only need Conditional Formatting for the red and blue colours. However, notice the formula is more complex because the cells now contain dots instead of the numbers, therefore we need use the COLUMNS & ROWS formula to generate the cell numbers inside of the Conditional Format:

conditional formatting rule formulas

Waffle Chart Titles

Because the column width is tiny, I used a Text Box (Insert tab > Shapes) for my chart title.

excel waffle chart titles

Excel Waffle Chart Limitations

In these examples you’ll have noticed that each segment represents a whole number or whole percentage point. With this technique it’s not possible to show fractions of a percentage point, but in next week’s tutorial I’ll demonstrate an alternate way to build waffle charts that get around this limitation.

Excel waffle Charts
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Charts Posts

interactive python charts in excel

Interactive Python Charts in Excel

How to build interactive Python Charts in Excel and regular charts with Slicers connected to data from Power Query.
Circle Progress Charts

Excel Progress Circle Charts

How to easily create dynamic Excel Progress Circle Charts, using doughnut charts and some wizardry, including Slicers to change the data.
professional vs amateur chart formatting

Pro Excel Chart Formatting

10 tell-tale signs that show you’re a chart amateur and the Excel chart formatting you should use instead.

Excel Scroll and Sort Table

Excel scroll and sort table using dynamic array formulas is far simpler than the old approach which required multiple tables, formulas and helper columns.
picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.


Category: Excel Charts
Previous Post:dense ranking in power queryDense Ranking in Power Query
Next Post:Stacked Bar Excel Waffle Chartsstacked bar waffle chart

Reader Interactions

Comments

  1. oden ogbin

    December 10, 2020 at 4:16 pm

    how will have cd for this excel training

    Reply
    • Mynda Treacy

      December 11, 2020 at 10:06 am

      We don’t provide our training on CDs sorry.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x