How to Create a Conditional Waffle Chart in Excel

Create a conditional waffle chart in Excel by entering a percentage, filling a 10-by-10 grid with formulas that mark the first matching number of cells, and using conditional formatting to color those cells. The chart can update when the percentage changes, and you can make its color respond to whether the result meets a target. You will need Microsoft Excel for Microsoft 365 on Windows, a value to chart, and about 100 worksheet cells for the tiles.

What You’ll Need

Excel for Microsoft 365 on Windows. The same formulas and conditional-formatting approach work in current Excel for Mac; the ribbon layout may differ slightly. In Excel for the web, the core formula and formatting features are available, though some dialog options may be arranged differently.
A percentage to display, such as 67%, and optionally a target percentage, such as 75%.
A blank worksheet area for the input values, labels, and 10-by-10 tile grid.

Step-by-Step

Set up the inputs. In A2, type Actual, and enter your value in B2. For example, type 67% in B2, or type 0.67 and apply Percentage format from Home > Number. Keep the value between 0% and 100%. If you want the chart to show whether a goal is met, type Target in A3 and enter a target such as 75% in B3. Format B2 and B3 as percentages so the values are easy to read. These cells are inputs, not part of the tile grid.
Make the 10-by-10 grid. Select D5:M14, which contains exactly 100 cells. Each cell will represent one percentage point. You can make the grid larger or smaller, but a 10-by-10 grid is easy to read and makes a percentage map directly to the number of filled tiles. If you change the grid dimensions later, use the same rectangular range throughout the formulas and conditional-formatting rules.
Enter the tile formula. With the actual percentage in B2 and the grid in D5:M14, enter this formula in D5. It calculates each tile’s position from 1 to 100, then returns 1 if that position falls within the rounded percentage and 0 otherwise: =IF((ROW()-ROW($D$5))*COLUMNS($D$5:$M$14)+COLUMN()-COLUMN($D$5)+1<=ROUND($B$2*ROWS($D$5:$M$14)*COLUMNS($D$5:$M$14),0),1,0). With B2 set to 67% and a 100-cell grid, the formula marks the first 67 cells as 1 and the remaining 33 as 0.
Copy the formula across the whole grid. Select D5, use the fill handle to fill right through M5, then fill the completed row down through row 14. Alternatively, copy D5, select D5:M14, and paste. The row and column references adjust for each tile, while the dollar signs keep the grid’s starting cell and size fixed. The formula arranges the tiles left to right across each row, then continues on the next row.
Hide the helper numbers. Select D5:M14 and press Ctrl+1 on Windows to open Format Cells. Choose Number > Custom, enter ;;; in the Type box, then select OK. The 1s and 0s remain available to conditional formatting, but are not shown. On Mac, use Command+1 to open Format Cells. If the custom format is not available in your Excel for the web interface, set the font color to match the cell fill as a temporary alternative, or open the workbook in the desktop app.
Apply formatting to the filled tiles. Keep D5:M14 selected and go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format. With D5:M14 as the Applies to range and D5 as the active top-left cell, enter =D5=1. Select Format, choose a fill color, and confirm with OK. The relative reference D5 will adjust across the range. This rule colors cells whose formula result is 1.
Give the unfilled tiles a contrasting color. Add a second rule using Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the same D5:M14 range, use =D5=0, choose a pale gray or another quiet fill, and confirm. The first rule colors filled tiles; the second colors the rest. Keep both fills distinct enough that the chart remains readable, including when printed or viewed by someone who has difficulty distinguishing colors.
Make the color respond to a target, if useful. If B3 contains a target percentage, you can replace the single filled-tile rule with two rules so filled tiles change color when the target is reached. With actual in B2, target in B3, and the grid in D5:M14, create one rule with =AND(D5=1,$B$2>=$B$3) and choose a success color. Create another with =AND(D5=1,$B$2<$B$3) and choose a warning color. Leave the zero-value rule in place for empty tiles. These conditions are exclusive: all filled tiles use the success color when actual is at least the target, and the warning color when actual is below it. Confirm that each rule applies to D5:M14.
Shape the grid into a chart. Select D5:M14 and use Home > Format to set the row heights and column widths so the cells look close to square. A cell size around 20–25 pixels can be a useful starting point, but adjust it to fit your sheet. Add thin white borders between tiles if you want clearer separation. You can use Home > Borders to apply borders; avoid thick outlines that overpower the color pattern.
Add a label and check the result. Add a heading such as Progress to C4, and consider displaying the value next to it. Change B2 from 67% to 42%: the grid should now show 42 colored tiles. If you added the target rules, change B2 to a value above and then below B3 and confirm that the filled tile color changes. Restore the intended value and save the workbook.

Common Mistakes

Using the wrong range size: the example formula calculates positions using D5:M14. If you build a grid in another range, update the formula’s starting-cell and range references as well as the conditional-formatting Applies to range. A mismatch can cause repeated tile numbers or a chart that fills incorrectly.

Entering 67 instead of 67%: Excel treats 67 as 6,700% when multiplied by 100 grid cells. Enter 67% or 0.67, not the whole number 67. Check the value in the formula bar if the chart fills completely.

Forgetting to fill every cell: copying only across the top row leaves the other rows blank. Select the full grid and confirm that each cell contains a formula before troubleshooting its colors.

Using absolute references in the conditional-formatting test: the rule should test D5 as a relative reference, so it shifts for each cell. A rule that locks the reference as $D$5 checks only one tile and can color the whole grid alike.

Making the tile shapes too rectangular: worksheet row heights and column widths use different measurement scales. Adjust both visually rather than assuming the same displayed number makes a square.

Tips

Use a single, clear accent color for filled tiles and a neutral background for empty tiles. If the chart appears in a report, add a text label such as 67% beside it; the color pattern alone is not a precise accessible description. You can also add alternative text to the selected grid or place a short description nearby.

For a value that changes regularly, keep B2 linked to the relevant cell in your worksheet instead of typing it again. For example, if the calculated percentage is in F2, enter =F2 in B2. With the percentage result in F2, this link displays the same value in B2 and lets the tile formula update as F2 changes.

Consider placing the input cells beside the chart and labeling them clearly. If someone else will use the workbook, add a note that B2 accepts a percentage from 0% to 100%, and explain what the target means. Locking or protecting the formula grid can help prevent accidental edits, but do that only after confirming users can still change the intended input cells.

Troubleshooting

The grid is all filled or all empty. Check B2 first. A percentage should be stored as a decimal value from 0 to 1: 67% is stored as 0.67. Also confirm that the formula in D5 refers to B2 and that the formula was filled through all 100 cells. A value above 100% will make every tile fill; a value below 0% will leave them empty.

The tiles show 1s and 0s. The formulas are working, but the custom number format may not have been applied to the full range. Select D5:M14, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and set the custom format to ;;;. You can also set the font color to match the cell background, though that workaround may make the values difficult to inspect later.

The number of filled tiles is one more or less than expected. The formula uses ROUND, so it rounds the percentage of 100 tiles to the nearest whole tile. For instance, 67.4% displays as 67 filled cells, while 67.5% rounds to 68. If your value has more decimal places, this is expected. To always round down instead, replace ROUND in the tile formula with ROUNDDOWN and keep the same arguments.

Conditional colors do not change when B2 changes. Open Home > Conditional Formatting > Manage Rules and check that the rules apply to =$D$5:$M$14. Confirm the formulas use the correct input cells and that calculation is set to Automatic under Formulas > Calculation Options. If the rule compares B2 with B3, make sure both cells contain numeric percentages rather than text such as the characters 67%.

Tiles have gaps or look uneven. Adjust the column widths and row heights, then recheck the layout at the zoom level where the chart will be viewed. Cell borders can affect the appearance, so try thin borders or no borders. If the chart is too large for the sheet, reduce both dimensions while keeping the range rectangular and updating the formula references.

Frequently Asked Questions

Does Excel have a built-in waffle chart?
No dedicated waffle-chart type appears in Excel’s standard chart list. This method uses worksheet cells, formulas, and conditional formatting to create a grid that behaves like one.

Can I use a grid other than 10 by 10?
Yes. A 5-by-20 grid still contains 100 cells, so each tile represents one percentage point. For a grid with a different number of cells, the formula uses the total tile count to calculate how many to fill; update its range references consistently.

Can the chart update automatically?
Yes. Change B2 or link it to a calculated percentage, and Excel recalculates the tile formulas and conditional formatting. If automatic calculation is turned off, use Formulas > Calculate Now or restore Automatic calculation.

Can I show more than one series in a waffle chart?
This example shows one percentage against unfilled tiles. For multiple categories, you need separate category values and a rule for assigning each tile to a category, with a distinct conditional-formatting rule and color for each. Ensure the category percentages add to no more than 100% if the remaining tiles are meant to represent unused capacity.

Will the chart work in Excel for Mac or the web?
The basic grid formulas and conditional formatting work in current Excel for Mac and Excel for the web. Menu placement and available format dialogs can differ. If you cannot set the custom number format in the web app, open the file in desktop Excel or use a font color that hides the helper values.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.