Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Waffle or Progress Chart in Excel

Show progress towards 100% — share of a target reached, budget used, survey completion — as a waffle chart of 100 cells or as a progress bar, built from worksheet cells and conditional formatting with openpyxl rather than a chart object: number the grid so it fills from the bottom, colour cells with one formula rule driven by the percentage cell, hide the numbers, add data-bar progress bars with fixed 0–100% limits, and render the sheet to check.
Charts & imagesopenpyxl

Some figures are percentages of a whole that people want to feel rather than read: 73% of new starters onboarded, 41% of the budget spent, 88% of the survey returned. A pie or doughnut chart can show them, but small differences in angle are hard to judge. A waffle chart is a 10 × 10 grid of squares in which one square is one percent, so 73% is 73 coloured squares out of 100 — easy to count, easy to compare across several measures side by side. A progress bar is the one-dimensional version: a bar that fills from left to right. Excel has neither as a chart type, but both are simple to build from worksheet cells with conditional formatting, which means they update instantly when the percentage changes, print crisply and need no chart object at all. This page builds a row of waffles and a set of progress bars with openpyxl and renders the result. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl

LibreOffice is used to render the sheet for checking; Excel draws the same conditional formatting.

How the waffle works

Each of the 100 cells in the grid holds a number from 1 to 100 — its position in the filling order. One conditional formatting rule colours a cell when its number is less than or equal to the percentage times 100. If the percentage cell holds 73%, cells 1 to 73 are coloured and 74 to 100 stay grey. The numbers themselves are hidden with the number format ;;;, which displays nothing for any value, so only the colours show. Changing the percentage — by hand or through a formula linked to the data — recolours the grid immediately.

From a percentage to coloured squares Each cell in the 10 by 10 grid holds its position from 1 to 100, hidden by a blank number format. A conditional formatting rule compares the position with the percentage times 100 and colours the cell when it is within it. Percentage cell e.g. 73% Grid cells 1–100 position in fill order Rule: n ≤ 73 one formula per grid Coloured squares numbers hidden

Step 1 — A numbered grid for each measure

Python
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter as col

measures = [("Onboarding complete", 0.73), ("Budget spent", 0.41), ("Survey returned", 0.88), ("Training booked", 0.25)]

wb = Workbook()
ws = wb.active
ws.title = "Progress"
gap = Side(style="thin", color="FFFFFF")
grey = PatternFill("solid", fgColor="E7E6E6")
GRID_TOP, BLOCK = 5, 11                                   # grid starts on row 5; each waffle is 10 columns + 1 gap

for k, (name, pct) in enumerate(measures):
    left = 2 + k * BLOCK
    ws.merge_cells(start_row=2, start_column=left, end_row=2, end_column=left + 9)
    ws.merge_cells(start_row=3, start_column=left, end_row=3, end_column=left + 9)
    ws.cell(2, left, name).font = Font(bold=True, size=10)
    value = ws.cell(3, left, pct)
    value.number_format, value.font = "0%", Font(bold=True, size=18, color="1F5FA8")
    for r in range(10):
        for c in range(10):
            cell = ws.cell(GRID_TOP + r, left + c, (9 - r) * 10 + c + 1)   # 1 is bottom-left, 100 top-right
            cell.number_format = ";;;"
            cell.fill = grey
            cell.border = Border(left=gap, right=gap, top=gap, bottom=gap)
    for c in range(11):
        ws.column_dimensions[col(left + c)].width = 2.4
for r in range(GRID_TOP, GRID_TOP + 10):
    ws.row_dimensions[r].height = 14
print(ws.cell(GRID_TOP + 9, 2).value, ws.cell(GRID_TOP, 11).value)   # bottom-left and top-right of the first grid

The grid is numbered so that it fills from the bottom-left corner, row by row upwards, the way a glass fills: position 1 is the bottom-left square and 100 the top-right. The formula (9 - r) * 10 + c + 1 does that for row r and column c within the grid. Filling left to right, top to bottom, is just as valid if that suits the report better; only the numbering changes. Columns 2.4 characters wide and rows 14 points high make the cells roughly square at normal zoom. A thin white border on every cell creates the gaps between the squares, and the grey fill is the "empty" colour that the conditional format overrides.

Step 2 — One rule colours each grid

Python
from openpyxl.formatting.rule import FormulaRule

blue = PatternFill("solid", fgColor="1F5FA8", bgColor="1F5FA8")
for k in range(len(measures)):
    left = 2 + k * BLOCK
    grid = f"{col(left)}{GRID_TOP}:{col(left + 9)}{GRID_TOP + 9}"
    pct_cell = f"${col(left)}$3"
    ws.conditional_formatting.add(grid, FormulaRule(formula=[f"{col(left)}{GRID_TOP}<=ROUND({pct_cell}*100,0)"], fill=blue))
print(len(ws.conditional_formatting), "rules")

The rule's formula is written for the top-left cell of the range, with a relative reference to it (B5) and an absolute reference to the percentage ($B$3). Excel shifts the relative part for every other cell in the range, so each cell compares its own number with the same percentage. ROUND(... * 100, 0) turns 73% into 73 squares and avoids a 72.99999% leaving the last square empty. The fill is given as both foreground and background colour, because conditional formatting fills use the background colour in Excel and the foreground in some other applications.

Step 3 — Progress bars from data bars

Python
from openpyxl.formatting.rule import DataBarRule

top = GRID_TOP + 12
ws.cell(top, 2, "Progress bars").font = Font(bold=True)
for i, (name, pct) in enumerate(measures, start=1):
    r = top + i
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=10)
    ws.cell(r, 2, name)
    ws.merge_cells(start_row=r, start_column=13, end_row=r, end_column=30)
    bar = ws.cell(r, 13, f"={col(2 + (i - 1) * BLOCK)}3")      # linked to the waffle's percentage
    bar.number_format = "0%"
    bar.alignment = Alignment(horizontal="left")
ws.conditional_formatting.add(f"M{top + 1}:M{top + len(measures)}",
                              DataBarRule(start_type="num", start_value=0, end_type="num", end_value=1, color="1F5FA8"))
wb.save("progress.xlsx")
print("saved")

A data bar is conditional formatting that draws a bar inside the cell in proportion to its value. Left to itself, Excel scales data bars to the smallest and largest values in the range, so the biggest of four percentages always fills the whole cell — misleading for progress, where 88% should look clearly short of complete. Setting the start to the number 0 and the end to the number 1 fixes the scale at 0–100%, so each bar's length is its true share. The bar cells are formulas linked to the waffles' percentage cells, so updating a percentage updates both visuals. Merging each bar cell across many narrow columns gives it room to be a proper bar.

Step 4 — Render and check

Bash
soffice --headless --convert-to pdf progress.xlsx > /dev/null 2>&1
pdftoppm -r 70 -f 1 -l 1 -png progress.pdf preview && ls preview*

The rendered page shows four waffles side by side — 73, 41, 88 and 25 blue squares filling from the bottom — with each percentage above its grid, and below them four progress bars whose lengths are directly comparable because they share the fixed 0–100% scale. Counting is easy where it matters: the onboarding waffle has seven full rows and three squares of the eighth. Two details are worth knowing from the render. Data bars are drawn with a gradient fill by default, fading towards the end of the bar; a solid fill needs the newer data bar extension that openpyxl does not write, so accept the gradient or use a REPT-based text bar if a solid look matters. And four waffles side by side are wider than a portrait page — the fourth is cut off in the default print layout — so set the sheet to landscape and fit-to-width before printing or exporting to PDF.

What the four waffles show Onboarding complete fills 73 squares, seven full rows and three more. Budget spent fills 41, survey returned 88 and training booked 25. Each progress bar below has the same length as its percentage of a full bar. A B C D 1 Measure Percentage Full rows Extra squares 2 Onboarding complete 73% 7 3 3 Budget spent 41% 4 1 4 Survey returned 88% 8 8 5 Training booked 25% 2 5

Choosing between them

A waffle takes a lot of space for one number, so it suits a summary page with a handful of headline percentages, where its countable squares make the numbers concrete. Progress bars are compact and line up well in a table — one per project, region or person — and the fixed scale makes them easy to compare. Neither is a good choice for values above 100% (a budget overspent by 12% does not fit in a 100-square grid) or for small differences between similar percentages, where the numbers themselves are clearer. For a measure that must be read against a target rather than against 100%, a bullet chart is better; see Create a Bullet Chart in Excel with Python.

Waffle or progress bar A waffle uses 100 squares for one percentage, is easy to count and suits a few headline figures. A progress bar fits in one row, lines up in tables and suits comparing many items on a fixed scale. Waffle chart 100 squares, 1% each easy to count a few headline figures takes space Progress bar one bar per row fixed 0–100% scale many items in a table compact

Common pitfalls

  • Visible numbers in the grid. Apply the ;;; number format to hide them.
  • Absolute reference to the grid cell. The rule needs a relative cell and an absolute percentage.
  • Automatic data bar scaling. Fix the bar's start at 0 and end at 1.
  • Rectangular squares. Adjust column width and row height together until the cells look square.
  • Values over 100%. Waffles and progress bars cannot show them; use a different chart.

Conclusion

To build a waffle chart in Excel with openpyxl, number a 10 × 10 grid of cells from 1 to 100 in the order they should fill, hide the numbers with ;;;, and add one conditional formatting rule that colours cells whose number is at most the rounded percentage times 100. For progress bars, use data bars with a fixed 0–1 scale on cells linked to the same percentages. Both update as soon as the percentage changes and render in Excel and LibreOffice alike.

Frequently asked questions

Can Excel make a waffle chart? There is no waffle chart type, but a 10 × 10 grid of cells coloured by a conditional formatting rule does the job and updates automatically.

How do I hide numbers in Excel cells but keep conditional formatting? Apply the number format ;;;, which displays nothing for positive, negative and zero values while the values still drive the formatting.

How do I make data bars use a fixed 0 to 100% scale? Create the DataBarRule with start_type="num", start_value=0, end_type="num", end_value=1.

Should the waffle fill from the top or the bottom? Either works; number the cells in the order they should fill. Filling from the bottom up reads naturally as "filling up".

More on charts & images

More on charts & images →