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

Applying Conditional Formatting With openpyxl

Add Excel conditional formatting from Python with openpyxl: CellIsRule, ColorScaleRule, and FormulaRule, plus the 3.1 API quirks that trip people up.

Conditional formatting lets a workbook explain itself: negative numbers turn red, top performers turn green, flagged rows stand out — all evaluated by Excel when the file opens, with no macros. openpyxl writes these rules straight into the worksheet XML, so you can attach them to a report generated by an Advanced Data Transformation and Cleaning pipeline.

Unlike the static number and date formats you set once per cell, a conditional rule is re-evaluated by Excel every time a value changes — so the highlighting stays correct as the underlying data is edited. This page covers the three rule types you will use most and the openpyxl 3.1 API details that cause silent failures. Every block runs in order against a sample workbook built in the first step.

A conditional formatting rule highlights matching cells A rule such as value greater than a threshold is registered on a cell range; Excel evaluates it on open and highlights only the cells that match. Rule value > 50 + fill to apply Cell range, evaluated by Excel 42 88 73 19 61 34 50 97 add(range, rule) matching cells highlighted; others unchanged

Install and create a sample workbook

Bash
pip install openpyxl

Build a small worksheet with a header row and numeric scores so the rules below have something to color:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Scores"

ws.append(["Name", "Score", "Status", "Balance"])
rows = [
    ["Alvarez", 92, "Active",   1200],
    ["Boateng", 68, "Pending",  -75],
    ["Chen",    81, "Active",    450],
    ["Dubois",  55, "Pending",   980],
    ["Eriksen", 99, "Active",    -10],
]
for r in rows:
    ws.append(r)

wb.save("formatted_report.xlsx")
print("Sample workbook created")

How a rule is built and attached

Every rule follows the same shape:

  1. Build the style objects you want applied (PatternFill, Font).
  2. Construct a rule (CellIsRule, ColorScaleRule, or FormulaRule) describing the condition.
  3. Register it with ws.conditional_formatting.add(range_string, rule).
  4. Save the workbook.

The range is always an A1-style string such as "B2:B6" (or space-separated for non-contiguous ranges, e.g. "B2:B6 D2:D6"). Named ranges are not accepted here — use explicit coordinates.

The three rule types differ in what they compare and how the result reads. Match the type to the decision you are making:

Choosing between CellIsRule, ColorScaleRule and FormulaRule Three rule types side by side: CellIsRule compares one column to fixed thresholds and fills matching cells in tiers; ColorScaleRule paints a gradient across a range so magnitude reads at a glance; FormulaRule evaluates an expression across several columns and fills the whole matching row. Pick the rule type by the decision you are making CellIsRule Compare one column to fixed thresholds 94 72 55 discrete tiers per cell ColorScaleRule Gradient that reads magnitude at a glance high low continuous across range FormulaRule Row logic across several columns whole row highlighted AND($C2="Pending", $D2>0) true → fill the row

CellIsRule: compare against a value

CellIsRule compares each cell against one or two literals. Highlight failing scores (below 70) in red:

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

red_fill = PatternFill("solid", fgColor="FFC7CE")
red_font = Font(color="9C0006")

fail_rule = CellIsRule(
    operator="lessThan",
    formula=["70"],          # always a list of strings, even for one value
    fill=red_fill,
    font=red_font,
)
ws.conditional_formatting.add("B2:B6", fail_rule)
wb.save("formatted_report.xlsx")
print("CellIsRule applied")

The formula argument is always a list of strings. The between operator takes two: formula=["70", "89"]. To match a text literal, wrap it in double quotes inside the string: formula=['"ERROR"'].

Stacking tiers and rule priority

You can attach several rules to the same range to build performance tiers. openpyxl assigns priorities in insertion order; Excel evaluates lowest priority number first. Note that openpyxl rule constructors do not accept a priority keyword — set the attribute on the rule object after constructing it.

Python
green = PatternFill("solid", fgColor="C6EFCE")
yellow = PatternFill("solid", fgColor="FFEB9C")

high = CellIsRule(operator="greaterThanOrEqual", formula=["90"], fill=green)
mid = CellIsRule(operator="between", formula=["70", "89"], fill=yellow)

# Set priority explicitly when ordering matters across many rules
high.priority = 1
mid.priority = 2

ws.conditional_formatting.add("B2:B6", high)
ws.conditional_formatting.add("B2:B6", mid)
wb.save("formatted_report.xlsx")
print("Tiered rules applied")

To stop Excel from applying lower-priority rules once one matches a cell, set stopIfTrue=True on the rule.

ColorScaleRule: a gradient across the range

For at-a-glance magnitude, a two- or three-color scale beats discrete tiers. Colors are 8-digit ARGB strings (the leading FF is full opacity):

Python
from openpyxl.formatting.rule import ColorScaleRule

scale = ColorScaleRule(
    start_type="min", start_color="FFF8696B",   # red at the low end
    mid_type="percentile", mid_value=50, mid_color="FFFFEB84",
    end_type="max", end_color="FF63BE7B",        # green at the high end
)
ws.conditional_formatting.add("B2:B6", scale)
wb.save("formatted_report.xlsx")
print("Color scale applied")

FormulaRule: cross-column, row-level logic

When the condition depends on other columns, use FormulaRule. The formula is an Excel expression with no leading = — openpyxl writes the string verbatim and Excel rejects a formula starting with =. Use absolute column references and a relative row matching the first row of the range so the rule shifts down correctly:

Python
from openpyxl.formatting.rule import FormulaRule

blue = PatternFill("solid", fgColor="DDEBF7")

# Highlight the whole row when Status is "Pending" and Balance is positive
pending_rule = FormulaRule(
    formula=['AND($C2="Pending", $D2>0)'],   # no leading "="
    fill=blue,
)
ws.conditional_formatting.add("A2:D6", pending_rule)
wb.save("formatted_report.xlsx")
print("FormulaRule applied")

The single-leaf page Apply Conditional Formatting to a Range in openpyxl drills into range syntax and the CellIsRule-to-FormulaRule fallback.

Re-running a script: clearing old rules

Re-running a generator on an existing workbook stacks duplicate rules. ConditionalFormattingList has no clear() method — reassign a fresh instance to wipe the sheet's rules before re-adding:

Python
from openpyxl.formatting.formatting import ConditionalFormattingList

ws.conditional_formatting = ConditionalFormattingList()
ws.conditional_formatting.add("B2:B6", fail_rule)
wb.save("formatted_report.xlsx")
print("Rules reset and reapplied")

Computing the range after writing data

When the row count is dynamic, build the range from ws.max_row so the rule covers exactly the data — no off-by-one that excludes the last row or formats empty cells:

Python
from openpyxl.utils import get_column_letter

last_col = get_column_letter(ws.max_column)   # "D"
target = f"A2:{last_col}{ws.max_row}"
print("Computed range:", target)

ws.conditional_formatting.add(target, pending_rule)
wb.save("formatted_report.xlsx")

Inspecting attached rules

Iterate the collection to confirm rules landed before saving. No output means nothing was attached:

Python
for cf in ws.conditional_formatting:
    print(cf.sqref, "->", len(cf.rules), "rule(s)")

API quirks to remember

  • Rule constructors (CellIsRule, ColorScaleRule, FormulaRule) do not take a priority kwarg — set rule.priority afterward.
  • FormulaRule formulas have no leading =.
  • ConditionalFormattingList has no clear() — reassign a fresh ConditionalFormattingList().
  • PatternFill needs fill_type="solid" (or the positional "solid") or it renders invisibly.
  • Excel caps conditional formatting at 64 rules per worksheet; consolidate overlapping logic into a single FormulaRule where you can.
  • openpyxl writes valid XML but does not render it — open the file in Excel to confirm the visual result.
  • ColorScaleRule color strings are 8-digit ARGB; prefix "FF" to any 6-digit HTML color for full opacity (e.g. "4472C4" → "FF4472C4").
  • CellIsRule with operator="equal" matching a string requires the value wrapped in double-quotes inside the string: formula=['"ERROR"'], not formula=["ERROR"].

Data bars, icon sets and when each reads best

Colour is not the only signal available. openpyxl exposes the same three visual rule families Excel does, and each answers a different question:

Three families of conditional formatting and what each shows A cell rule answers a yes or no question such as below target. A colour scale shows where each value sits in a range. A data bar compares magnitudes across rows at a glance. cell rule -12.5% "is this one bad?" thresholds, exceptions, rules with a clear line colour scale "where does it sit?" distributions, heat maps, no natural threshold data bar "how do they compare?" magnitudes, rankings
Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import DataBarRule, IconSetRule

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
last = ws.max_row

ws.conditional_formatting.add(
    f"D2:D{last}",
    DataBarRule(start_type="min", end_type="max", color="4338CA", showValue=True),
)

ws.conditional_formatting.add(
    f"E2:E{last}",
    IconSetRule("3TrafficLights1", "percent", [0, 33, 67], showValue=True),
)

wb.save("orders_visual.xlsx")

showValue=True on a data bar keeps the number visible behind the bar, which is nearly always what a report wants — a bar alone forces the reader to estimate. Icon sets earn their place for status columns where three states are genuinely meaningful, and they become noise the moment they are applied to a column of continuous values, where a colour scale says more with less.

Rules that reference other columns

The most useful rules are relational: highlight a row when its actual is below its target, not when a number crosses a fixed line. FormulaRule handles that, and the anchoring is what makes it work across the whole range:

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

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
last = ws.max_row

below_target = PatternFill("solid", start_color="FFC7CE")
ws.conditional_formatting.add(
    f"A2:F{last}",                                  # the whole row is highlighted
    FormulaRule(formula=[f"$D2<$E2"], fill=below_target, stopIfTrue=False),
)

wb.save("orders_row_rules.xlsx")

The formula is written as if for the top-left cell of the range, and Excel applies it relatively to every other cell — so $D2<$E2 keeps the columns pinned while the row number moves. Getting this backwards is the classic conditional-formatting bug: with D$2<E$2 every row is compared against row two, and the result looks almost right.

Rules cost something on a large sheet

Each rule is a small piece of XML, but the count matters. One rule over A2:F100000 is cheap; a hundred thousand rules over single cells makes a workbook slow to open and painful to maintain in Excel's own rule manager. Applying a rule to a range rather than to individual cells is the single most important habit here, and it produces identical output.

The second cost is recalculation. A rule whose formula references another sheet, or uses a volatile function such as TODAY(), is re-evaluated far more often than one comparing two columns on the same row. Where a date threshold is needed, writing the cut-off into a cell once and referencing it is much cheaper than embedding TODAY() in a rule that covers a hundred thousand rows.

Clearing rules before re-applying them

Conditional formatting accumulates. A monthly job that adds its rules to a workbook that already has last month's ends up with duplicates, and Excel evaluates all of them:

Python
from openpyxl import load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList

wb = load_workbook("orders_row_rules.xlsx")
ws = wb["Orders"]

print("before:", sum(len(r.rules) for r in ws.conditional_formatting))
ws.conditional_formatting = ConditionalFormattingList()      # start clean
print("after:", sum(len(r.rules) for r in ws.conditional_formatting))

Resetting the whole collection is the blunt version and is right when your job owns the sheet. Where the workbook is someone's template and carries rules you did not add, remove only your own ranges by matching on the range string — and log what you removed, because a rule that quietly disappears from a template is much harder to diagnose than one that was never there.

Highlighting the row, not just the cell

Readers scan rows. A rule that colours a single cell answers "which value is wrong?" while a rule across the whole row answers "which record needs attention?", and the second is usually the question a report is asked:

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

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
last = ws.max_row

late = PatternFill("solid", start_color="FFEB9C")
ws.conditional_formatting.add(
    f"A2:F{last}",
    FormulaRule(formula=['$F2="Late"'], fill=late),
)

missing = PatternFill("solid", start_color="FFC7CE")
ws.conditional_formatting.add(
    f"A2:F{last}",
    FormulaRule(formula=['COUNTBLANK($A2:$F2)>0'], fill=missing, stopIfTrue=True),
)

wb.save("orders_rows.xlsx")

stopIfTrue=True on the second rule matters when rules overlap: a row that is both late and incomplete should show one colour rather than whichever the evaluation order happened to leave on top. Ordering the rules from most to least severe, with stopIfTrue on the ones that should win, makes the result predictable.

Note the mixed anchoring again — $F2 pins the column and lets the row move, which is what applies one rule consistently down a range. A rule written with $F$2 compares every row against row two, and a rule written with F2 drifts sideways as well as down.

Formatting that survives a monthly refresh

A recurring report usually rewrites the data into a workbook that already carries formatting from last month. Two things go wrong: the rules cover the old row count, and they accumulate. Rebuilding the rules as part of the job fixes both:

Python
from openpyxl import load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

def apply_rules(ws):
    ws.conditional_formatting = ConditionalFormattingList()          # drop last month's
    last_row = ws.max_row
    last_col = get_column_letter(ws.max_column)

    ws.conditional_formatting.add(
        f"D2:D{last_row}",
        CellIsRule(operator="lessThan", formula=["0"],
                   fill=PatternFill("solid", start_color="FFC7CE")),
    )
    ws.conditional_formatting.add(
        f"A2:{last_col}{last_row}",
        CellIsRule(operator="equal", formula=['"Cancelled"'],
                   fill=PatternFill("solid", start_color="F0F2F5")),
    )
    return last_row

wb = load_workbook("monthly.xlsx")
rows = apply_rules(wb["Orders"])
wb.save("monthly_formatted.xlsx")
print("rules applied over", rows, "row(s)")

Deriving every range from ws.max_row each run is what keeps the formatting in step with the data. The alternative — a rule written once against D2:D500 — silently stops covering the sheet the month it grows past five hundred rows, and nothing about the file looks wrong.

Conditional formatting versus writing the colour directly

Both approaches produce a coloured cell, and they differ in what happens next:

Conditional formattingDirect PatternFill
Reacts to editsYes — recalculates liveNo — fixed at write time
Rule visible to the readerYes, in Excel's rule managerNo
Depends on Excel evaluating a formulaYesNo
Suits a rule Python decidedPoorlyWell
Cost on a large sheetOne rule per rangeOne style per cell

The dividing line is where the decision was made. If Python looked up a master list and concluded that a region is invalid, that judgement cannot be expressed as a spreadsheet formula, so a direct fill plus a comment is the honest representation — which is what highlighting invalid cells does. If the rule is simply "below zero", conditional formatting is better, because it keeps responding as the reader edits.

Checking the rules landed

Formatting is invisible to a data check, so verify it explicitly rather than opening the file to look:

Python
from openpyxl import load_workbook

wb = load_workbook("monthly_formatted.xlsx")
ws = wb["Orders"]

for rng in ws.conditional_formatting:
    for rule in rng.rules:
        print(f"{rng.sqref}: {rule.type} {getattr(rule, 'operator', '')} {rule.formula}")

Printing the inventory catches the two failures that otherwise reach a reader: a rule whose range stopped short of the data, and a rule that was added twice with slightly different bounds. Both are one line to spot here and a confusing email to diagnose later.

Colour choices that survive printing and colour blindness

A rule is only useful if its meaning reaches the reader. Excel's own "Bad", "Neutral" and "Good" pairs — FFC7CE/9C0006, FFEB9C/5A4A00 and C6EFCE/0B3D1A — are worth defaulting to for three practical reasons: they are familiar, they carry a matching font colour so the text stays legible, and they survive greyscale printing as distinguishable shades.

Red and green alone are the pairing to avoid, because the most common form of colour blindness makes them nearly identical. Where a two-state rule must be unmistakable, pair the colour with something else — a symbol in an adjacent column, a bold font, or an icon set — so the signal does not depend on hue alone. That redundancy costs one extra rule and makes the report readable to everyone who receives it.

Formatting the summary, not the detail

A hundred thousand highlighted cells communicate nothing — the eye needs contrast, and a sheet where a third of the rows are coloured reads as noise. The technique that keeps highlighting meaningful is to apply it where the reader is looking: a small summary block with a handful of coloured cells, and a detail sheet left plain for filtering.

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

wb = load_workbook("report.xlsx")
summary = wb["Summary"]

summary.conditional_formatting.add(
    f"C2:C{summary.max_row}",
    CellIsRule(operator="lessThan", formula=["0"],
               fill=PatternFill("solid", start_color="FFC7CE")),
)
summary.conditional_formatting.add(
    f"C2:C{summary.max_row}",
    CellIsRule(operator="greaterThanOrEqual", formula=["0.1"],
               fill=PatternFill("solid", start_color="C6EFCE")),
)

wb.save("report_summary_formatted.xlsx")

Two rules over a dozen rows carry more meaning than ten thousand cells of graded colour, and the workbook stays fast. Where the detail sheet genuinely needs a signal, a single data bar on one column is usually enough — it conveys magnitude without asking the reader to decode a palette.

The same restraint applies to the number of distinct colours. Three states is about the limit for something a reader interprets without a legend; beyond that, add the legend or reconsider whether the distinction belongs in colour at all.

Frequently asked questions

Why doesn't passing priority=1 to CellIsRule work? The rule constructors don't accept a priority keyword and will raise a TypeError. Build the rule first, then set the attribute on the object: rule.priority = 1.

Why does my FormulaRule show no effect or break the file? The formula must be a plain Excel expression with no leading = — openpyxl writes the string verbatim and Excel rejects a formula that starts with =. Use 'AND($C2="Pending", $D2>0)', not '=AND(...)'.

Why is my PatternFill invisible in Excel? A PatternFill needs fill_type="solid" (or the positional "solid") together with a color; without the fill type it defaults to None and renders nothing.

Why do my rules pile up every time I re-run the script?add() appends, and ConditionalFormattingList has no clear() method. Reassign a fresh ws.conditional_formatting = ConditionalFormattingList() before re-adding to wipe the sheet's existing rules.

Is there a limit to how many rules I can add? Excel caps conditional formatting at 64 rules per worksheet. Consolidate overlapping logic into a single FormulaRule where you can rather than stacking many narrow rules.

Conclusion

Conditional formatting in openpyxl comes down to three moves: build the style, construct the right rule type, and register it against an A1 range. Reach for CellIsRule when a single column is compared to fixed thresholds, ColorScaleRule when you want a gradient that reads magnitude at a glance, and FormulaRule when the decision depends on other columns in the same row. The failures people hit are almost always API-shape issues rather than logic: priority set as an attribute not a keyword, a FormulaRule with no leading =, a PatternFill missing its "solid" fill type, and rules stacking on re-run. Keep the range dynamic off ws.max_row, stay under Excel's 64-rule cap, and open the saved file in Excel to confirm the visual result — openpyxl writes the XML but never renders it.

Where to go next