Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Highlight Cells Above a Threshold with openpyxl

Draw the eye to the numbers that matter — CellIsRule for fixed thresholds, FormulaRule for a threshold that lives in a cell, whole-row highlighting, and rule priority.

A report with four hundred rows and one number that needs attention is a report nobody reads carefully. Conditional formatting solves that by making the exceptions visible without changing any values — and unlike a static fill, the rule stays live, so a cell that later falls below the threshold loses its highlight automatically. This guide covers fixed thresholds, thresholds that live in a cell so readers can change them, whole-row highlighting, and what happens when rules overlap. It extends Applying Conditional Formatting with openpyxl.

A live threshold rule reading from an editable cell A small report with a threshold cell holding four thousand at the top. Rows whose revenue exceeds that threshold are tinted teal; rows below it stay plain. Because the rule references the threshold cell rather than a hard-coded number, a reader who types a different threshold sees the highlighting update immediately without the report being regenerated. threshold 4,000 B1 — readers can change this region revenue North 5,150.00 above → tinted South 4,268.50 above → tinted West 3,511.25 below → plain the rule is live: change B1 and the highlighting follows, with no regeneration

Prerequisites

Bash
pip install openpyxl pandas

A sheet to format:

Python
import pandas as pd

pd.DataFrame({
    "region": ["North", "South", "West", "East", "Central"],
    "revenue": [5150.00, 4268.50, 3511.25, 2980.10, 6402.75],
    "target": [4500.00, 4500.00, 3000.00, 3500.00, 5000.00],
}).to_excel("report.xlsx", index=False, startrow=1)

Note startrow=1, leaving row 1 free for the threshold cell.

Step 1 — A fixed threshold with CellIsRule

CellIsRule covers the common comparisons against a constant:

Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill, Font

wb = load_workbook("report.xlsx")
ws = wb.active

above = PatternFill(start_color="D9F4F1", end_color="D9F4F1", fill_type="solid")
above_font = Font(color="0B6157", bold=True)

ws.conditional_formatting.add(
    "B3:B7",
    CellIsRule(operator="greaterThan", formula=["4000"],
               fill=above, font=above_font),
)

wb.save("report_formatted.xlsx")

Two details trip people up. The formula argument is a list of strings, even for a single number — ["4000"], not 4000. And the fill must be a solid PatternFill with both colours set; a fill with only start_color renders as nothing in some Excel versions.

The operators available:

OperatorMeaning
greaterThanstrictly above
greaterThanOrEqualat or above
lessThan / lessThanOrEqualbelow / at or below
between / notBetweentwo values in formula
equal / notEqualexact match
Python
ws.conditional_formatting.add(
    "B3:B7",
    CellIsRule(operator="between", formula=["3000", "4000"],
               fill=PatternFill("solid", start_color="FDEFD8",
                                end_color="FDEFD8")),
)

Step 2 — A threshold readers can change

Hard-coding 4000 means regenerating the report whenever somebody wants a different cut-off. FormulaRule with an absolute reference lets the threshold live in a cell:

Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill, Font

wb = load_workbook("report.xlsx")
ws = wb.active

ws["A1"] = "Threshold"
ws["B1"] = 4000
ws["B1"].number_format = "#,##0.00"
ws["A1"].font = Font(bold=True)

ws.conditional_formatting.add(
    "B3:B7",
    FormulaRule(
        formula=["AND(B3<>\"\", B3>$B$1)"],
        fill=PatternFill("solid", start_color="D9F4F1", end_color="D9F4F1"),
        font=Font(color="0B6157", bold=True),
    ),
)

wb.save("report_dynamic.xlsx")

The reference style is the whole trick. $B$1 is absolute, so every cell in the range compares against that one threshold cell. B3 is relative and refers to the top-left cell of the range — Excel slides it down as it evaluates each row, so writing B3 gives you "this row's revenue".

The AND(B3<>"", ...) guard matters: without it, blank cells compare as zero and the rule fires or does not fire on cells that hold nothing, which looks like a bug to a reader.

Step 3 — Highlight the whole row

Tinting one cell tells the reader which value is high; tinting the row tells them which record is. The difference is one anchor.

Three anchorings, three very different rules Three formula references compared. A fully relative reference such as B3 slides both down and across, so applied to a whole-row range each column tests its own value — rarely what is wanted. A fully absolute reference such as dollar B dollar 3 never moves, so every cell in every row tests the same single cell. A column-anchored reference such as dollar B3 keeps the column fixed while the row slides, which is exactly the behaviour a whole-row highlight needs. B3 fully relative slides down AND across column A tests A3, column C tests C3 rarely what you want $B$3 fully absolute never moves at all every row tests the same single cell right for the threshold cell $B3 column anchored slides down, not across every column in a row tests that row's B right for whole-row highlighting
Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

wb = load_workbook("report.xlsx")
ws = wb.active
ws["A1"], ws["B1"] = "Threshold", 4000

# Apply across every column of the data, testing column B in each row.
ws.conditional_formatting.add(
    "A3:C7",
    FormulaRule(
        formula=['AND($B3<>"", $B3>$B$1)'],
        fill=PatternFill("solid", start_color="D9F4F1", end_color="D9F4F1"),
    ),
)

wb.save("report_rows.xlsx")

$B3 — column anchored, row relative — is the pattern to remember. It is the single most useful reference form in conditional formatting, and getting it wrong is why a whole-row rule so often highlights a diagonal.

Comparing against another column rather than a fixed cell is the same shape, and is often more useful than an absolute threshold:

Python
# Highlight rows where revenue beat the row's own target.
ws.conditional_formatting.add(
    "A3:C7",
    FormulaRule(formula=["$B3>$C3"],
                fill=PatternFill("solid", start_color="D9F4F1",
                                 end_color="D9F4F1")),
)

Step 4 — Order overlapping rules

Rules are evaluated in the order they were added, and several can apply to one cell — their formats merge, which produces muddled results when they conflict. Add the most specific first and stop evaluation:

Python
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

RED = PatternFill("solid", start_color="FEE8F2", end_color="FEE8F2")
AMBER = PatternFill("solid", start_color="FDEFD8", end_color="FDEFD8")
GREEN = PatternFill("solid", start_color="D9F4F1", end_color="D9F4F1")

critical = CellIsRule(operator="greaterThan", formula=["6000"], fill=RED)
critical.stopIfTrue = True
ws.conditional_formatting.add("B3:B7", critical)

warn = CellIsRule(operator="greaterThan", formula=["4000"], fill=AMBER)
warn.stopIfTrue = True
ws.conditional_formatting.add("B3:B7", warn)

ws.conditional_formatting.add(
    "B3:B7", CellIsRule(operator="lessThanOrEqual", formula=["4000"], fill=GREEN)
)

Without stopIfTrue, a value of 6,500 matches both the critical and the warning rule, and Excel merges the two formats — usually giving you the first rule's fill with the second's font, which is neither. The banding is covered further in adding data bars and colour scales with openpyxl.

Step 5 — Apply it after a pandas write

Why the most specific rule must be added first A value of six thousand five hundred is tested against three rules in the order they were added. The critical rule, above six thousand, matches first and because stopIfTrue is set no further rules are evaluated, so the cell gets the red format alone. Without stopIfTrue the warning rule would also match and Excel would merge the two formats, typically taking the fill from one and the font from the other. value 6,500 matches two rules 1 · above 6,000 · stopIfTrue matches — evaluation ends here 2 · above 4,000 — never reached 3 · 4,000 or below — never reached one clean format red fill, red bold font without stopIfTrue rules 1 and 2 both match and merge

to_excel replaces the sheet, so conditional formatting applied first is discarded. Either format after writing with openpyxl, or add the rule through the xlsxwriter engine while the writer is open:

Python
import pandas as pd

df = pd.DataFrame({
    "region": ["North", "South", "West", "East", "Central"],
    "revenue": [5150.00, 4268.50, 3511.25, 2980.10, 6402.75],
})

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Report", index=False, startrow=1)
    book, sheet = writer.book, writer.sheets["Report"]

    sheet.write(0, 0, "Threshold")
    sheet.write_number(0, 1, 4000, book.add_format({"num_format": "#,##0.00"}))

    high = book.add_format({"bg_color": "#D9F4F1", "font_color": "#0B6157",
                            "bold": True})
    sheet.conditional_format(
        2, 1, len(df) + 1, 1,
        {"type": "formula", "criteria": '=AND($B3<>"", $B3>$B$1)',
         "format": high},
    )
    sheet.set_column("A:A", 14)
    sheet.set_column("B:B", 14,
                     book.add_format({"num_format": "#,##0.00"}))

xlsxwriter's conditional_format takes zero-based row and column bounds, where openpyxl takes an A1 range string — an easy source of off-by-one errors when porting between the two.

Common pitfalls and fixes

SymptomCauseFix
TypeError on the ruleformula given as a numberPass a list of strings: ["4000"].
Rule added but nothing highlightsFill missing end_colorUse a solid fill with both colours set.
Whole-row rule highlights a diagonalFully relative referenceAnchor the column: $B3.
Every row highlights identicallyFully absolute referenceLeave the row relative.
Blank cells highlightedBlanks compare as zeroAdd an AND(cell<>"", ...) guard.
Two rules produce a muddled formatBoth matched and mergedSet stopIfTrue on the more specific rule.
Formatting gone after the job runsto_excel replaced the sheetApply the rules after writing.
Off-by-one in the rangexlsxwriter is zero-based, openpyxl is A1Check which API you are using.

Performance and scale notes

A conditional format is a single rule object covering a range, so it costs the same whether the range is ten rows or a hundred thousand — unlike per-cell fills, which create a style entry each and can approach Excel's ceiling of roughly 64,000 distinct formats.

That makes the comparison stark:

Python
from openpyxl.styles import PatternFill

# Expensive: one style per cell, and it goes stale on the first edit.
tint = PatternFill("solid", start_color="D9F4F1", end_color="D9F4F1")
for (cell,) in ws.iter_rows(min_row=3, max_row=100_003, min_col=2, max_col=2):
    if cell.value and cell.value > 4000:
        cell.fill = tint

# Cheap: one rule object, and it stays live.
ws.conditional_formatting.add(
    "B3:B100003",
    CellIsRule(operator="greaterThan", formula=["4000"], fill=tint),
)

Two further habits. Use one rule over one range rather than many small ranges — Excel evaluates each rule over each of its ranges, and a hundred single-row rules is a hundred times the work of one covering all hundred rows. And avoid volatile functions in the formula: INDIRECT, OFFSET, TODAY and NOW force re-evaluation on every recalculation, which makes a large sheet sluggish. Compute the value in Python and write it to a cell the rule references instead.

The static-fill approach retains one advantage worth noting: it survives a conversion to PDF or CSV, where conditional formatting does not always render. For a report destined for PDF export, verify the highlighting appears in the output, and fall back to static fills for that specific artefact while keeping live rules in the workbook readers open.

Conclusion

Conditional formatting makes exceptions visible without touching the data, and it stays correct when the data changes — which a static fill does not. Use CellIsRule for a fixed comparison, FormulaRule with an absolute $B$1 reference when the threshold should live in a cell readers can edit, and $B3 — column anchored, row relative — when the whole row should highlight. Order overlapping rules from most to least specific and set stopIfTrue, guard against blank cells comparing as zero, and always apply the rules after the data has been written.

Frequently asked questions

What is the difference between a conditional format and just setting a fill? A conditional format is a live rule Excel re-evaluates whenever the data changes, so a cell that later drops below the threshold loses its highlight. A static fill is baked in and stays wrong the moment somebody edits a value.

How do I reference a threshold stored in a cell? Use FormulaRule with an absolute reference such as $B$1. Keep the row and column anchored so every cell in the range compares against the same threshold cell rather than a shifting one.

Why does my whole-row rule highlight only one column? The formula uses a fully relative or fully absolute reference. Anchor the column and leave the row relative — $B3 — so the rule slides down the rows while always testing column B.

Which rule wins when two overlap? The one added first, unless a rule sets stopIfTrue. Order your rules from most specific to least specific and set stopIfTrue on the ones that should end evaluation.

Do conditional formats survive a pandas write? No. to_excel replaces the sheet, so apply conditional formatting after all data has been written, or add it through the xlsxwriter engine while the writer is open.