Apply Conditional Formatting to an Excel Table That Grows

A weekly job appends new orders to a tracking workbook. The orders live in an Excel table, and conditional formatting highlights late deliveries and large orders. In week one everything looks right. By week four the newest rows are plain — the highlighting stopped at the row where the table ended when the rules were created. When a person adds rows inside a table in Excel, Excel usually extends the formatting for them; when a script appends rows and resizes the table, the rules' ranges stay exactly where they were. This page shows the problem with openpyxl, fixes it by re-pointing every rule at the table's current range after each append, compares that with the simpler trick of oversized ranges, explains why table references cannot be used inside rule formulas, and adds a check that no data row is left uncovered. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

pip install openpyxl

The examples create an orders workbook with an Excel table and two highlighting rules, then append a week of new orders the way a scheduled job would. Appending to a table is covered in its own right in Append Rows to an Excel Table and Resize It; this page is about keeping the formatting in step.

Step 1 — A table with two rules

Create the workbook: an Orders table and two formula rules over its data rows — late deliveries in red, orders over 5,000 in bold amber:

from datetime import date, timedelta
from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order", "Due", "Delivered", "Amount"])
base = date(2026, 9, 1)
for i in range(8):
    due = base + timedelta(days=i)
    ws.append([f"SO-{1001 + i}", due, due + timedelta(days=(i % 4) - 1), 1200 + i * 900])
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    for c in row:
        c.number_format = "yyyy-mm-dd"

last = ws.max_row
table = Table(displayName="Orders", ref=f"A1:D{last}", tableStyleInfo=TableStyleInfo(name="TableStyleLight9", showRowStripes=True))
ws.add_table(table)
LATE = FormulaRule(formula=["$C2>$B2"], fill=PatternFill("solid", fgColor="FFC7CE"), font=Font(color="9C0006"))
BIG = FormulaRule(formula=["$D2>5000"], fill=PatternFill("solid", fgColor="FFEB9C"), font=Font(bold=True))
ws.conditional_formatting.add(f"A2:D{last}", LATE)
ws.conditional_formatting.add(f"D2:D{last}", BIG)
wb.save("orders_tracker.xlsx")
print("table", table.ref, "| rule ranges:", [str(cf.sqref) for cf in ws.conditional_formatting])

The table covers A1:D9; the late rule covers the data rows A2:D9 and the amount rule D2:D9. Both formulas are written for row 2 with the column anchored and the row relative, so each row tests its own dates and amount. Everything is consistent — for now.

Step 2 — Append a week of orders and watch the rules fall behind

A later run opens the workbook, appends new rows below the table and resizes the table to include them:

from openpyxl import load_workbook

def append_orders(path, new_rows):
    wb = load_workbook(path)
    ws = wb["Orders"]
    t = ws.tables["Orders"]
    for r in new_rows:
        ws.append(r)
        for c in ws[ws.max_row][1:3]:
            c.number_format = "yyyy-mm-dd"
    t.ref = f"A1:D{ws.max_row}"
    wb.save(path)
    return wb

new = [[f"SO-{1009 + i}", base + timedelta(days=8 + i), base + timedelta(days=9 + i), 3000 + i * 1500] for i in range(5)]
wb = append_orders("orders_tracker.xlsx", new)
ws = wb["Orders"]
print("table now", ws.tables["Orders"].ref, "| rule ranges still:", [str(cf.sqref) for cf in ws.conditional_formatting])

The table now covers A1:D14, but both rules still end at row 9. The five new orders — every one of them late, and three of them over 5,000 — get no highlighting at all. Nothing warns about this: the workbook opens normally, the table looks complete, and the only clue is that the newest rows are oddly plain. Rules are attached to cell ranges, not to tables, and openpyxl writes exactly the ranges it was given.

Step 3 — Re-point every rule at the table's current range

The robust fix is to rebuild the conditional formatting after every append, keeping each rule and its column span but extending its rows to the table's last row:

from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.utils.cell import range_boundaries, get_column_letter

def stretch_rules_to_table(ws, table_name):
    t = ws.tables[table_name]
    _, top, _, bottom = range_boundaries(t.ref)
    old = ws.conditional_formatting
    ws.conditional_formatting = ConditionalFormattingList()
    for cf in old:
        for rng in str(cf.sqref).split():
            c1, r1, c2, _ = range_boundaries(rng)
            new_ref = f"{get_column_letter(c1)}{r1}:{get_column_letter(c2)}{bottom}"
            for rule in cf.rules:
                ws.conditional_formatting.add(new_ref, rule)

stretch_rules_to_table(ws, "Orders")
wb.save("orders_tracker.xlsx")
print("rule ranges now:", [str(cf.sqref) for cf in ws.conditional_formatting])

Each rule keeps its starting row and columns — the late rule still starts at A2 and spans A to D, the amount rule stays in column D — and its last row becomes the table's last row. Because the formulas are written relative to the first cell, extending the range is all it takes for the new rows to be tested correctly; the formula text does not change. Rebuilding the list rather than editing ranges in place avoids duplicate entries, and the rules keep their original order and priority. Call this function at the end of every job that changes the table's size — it is idempotent, so calling it when nothing changed is harmless.

Step 4 — Oversized ranges: simpler, with a cost

A common shortcut is to apply rules far below the data from the start — A2:D10000 — so new rows fall inside them. It works, but it has costs worth knowing:

from openpyxl import Workbook

alt = Workbook()
aws = alt.active
aws.append(["Order", "Due", "Delivered", "Amount"])
aws.conditional_formatting.add("A2:D10000", FormulaRule(formula=["AND($C2<>\"\",$C2>$B2)"], fill=PatternFill("solid", fgColor="FFC7CE")))
alt.save("oversized.xlsx")
print("rule range:", [str(cf.sqref) for cf in aws.conditional_formatting], "| sheet dimension:", aws.dimensions)

The rule covers 40,000 cells while the sheet holds only a header. Every rule over a huge range is evaluated across that whole range whenever Excel redraws — a few such rules are fine, dozens of them slow workbooks noticeably (see Find Out Why an Excel File Is Slow to Open). The formula also has to cope with empty rows: here $C2<>"" stops blank rows being compared, because an empty delivery date would otherwise make some rules fire on empty rows. And the range still runs out eventually. Oversized ranges suit a sheet a person edits by hand; for a scripted job, re-pointing the rules after each append is cleaner.

Step 5 — Why not use table references in the rule?

It is tempting to write Orders[Delivered]>Orders[Due] so the rule follows the table automatically. Excel does not allow it: structured references cannot be used directly in conditional-formatting or data-validation formulas. The workaround people use is INDIRECT("Orders[Delivered]"), which works but makes the rule volatile and hard to read. Relative cell references with a range kept in step with the table, as in Step 3, are the dependable choice. Check the result after every run:

def uncovered_rows(ws, table_name):
    _, top, _, bottom = range_boundaries(ws.tables[table_name].ref)
    gaps = {}
    for cf in ws.conditional_formatting:
        covered = set()
        for rng in str(cf.sqref).split():
            _, r1, _, r2 = range_boundaries(rng)
            covered.update(range(r1, r2 + 1))
        missing = [r for r in range(top + 1, bottom + 1) if r not in covered]
        if missing:
            gaps[str(cf.sqref)] = missing
    return gaps

check = load_workbook("orders_tracker.xlsx")["Orders"]
print("rows without highlighting:", uncovered_rows(check, "Orders") or "none")

The check compares every rule's rows with the table's data rows and lists any that are not covered. After the stretch it finds none; run against the workbook from Step 2 it would have listed rows 10–14 for both rules. A test like this at the end of the job turns an invisible problem into a failed run. Rule order and priorities are unaffected by the stretch, which matters when rules overlap — see Control Conditional Formatting Rule Order and Stop If True.

Common pitfalls

• Assuming rules follow the table. Ranges written by a script stay where they were; stretch them.
• Editing ranges in place. Rebuild the list so rules are not duplicated.
• Structured references in rule formulas. Excel rejects them; use relative references.
• Oversized ranges without blank-row handling. Rules can fire on empty rows.
• No coverage check. Uncovered rows are invisible until someone notices.

Conclusion

Conditional formatting rules are attached to cell ranges, so when a script appends rows and resizes an Excel table, the rules stop at the old last row. After every append, rebuild the conditional formatting with each rule's range stretched to the table's new last row — the relative formulas then test the new rows correctly — and check that every data row is covered by every rule. Oversized ranges are a simpler alternative for hand-edited sheets, at a performance cost, and structured references cannot be used in rule formulas at all.

Frequently asked questions

Why doesn't conditional formatting apply to new rows added to my Excel table? The rules have fixed cell ranges. Rows added by a script below the old range are outside it, even if the table itself was resized. Extend each rule's range to the new last row.

How do I extend conditional formatting ranges with openpyxl? Read each rule's range and rules, replace the worksheet's conditional_formatting with a new ConditionalFormattingList, and add each rule again with the range's last row set to the table's last row.

Can I use table column references in conditional formatting? Not directly; Excel does not allow structured references in conditional formatting formulas. INDIRECT with a table reference works but is volatile.

Is it OK to apply conditional formatting to thousands of empty rows? It works, but every rule is evaluated across its whole range, which slows large workbooks, and formulas must ignore blank rows.

Related

• Up one level: Applying Conditional Formatting with openpyxl — rule-based formatting.
• Append Rows to an Excel Table and Resize It — the append itself.
• List and Remove Conditional Formatting with openpyxl — inspecting rules and ranges.
• Make an Excel Chart Grow with Its Data — the same problem for charts.