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

Apply Conditional Formatting to a Range in openpyxl

Attach a conditional formatting rule to a cell range in openpyxl with conditional_formatting.add(), covering range syntax, CellIsRule, and the FormulaRule fallback.

You have a block of cells — a column of totals, a table of metrics — and you want Excel to colour the ones that cross a threshold, without touching each cell by hand. The answer is a single call: build a rule object (CellIsRule, FormulaRule, or ColorScaleRule), give it a PatternFill and/or Font, and register it against an A1-style range with ws.conditional_formatting.add(range_string, rule). openpyxl writes the rule into the worksheet XML; Excel evaluates it against every cell in the range when the file opens.

This page focuses narrowly on the range-binding mechanics — how the range string works, when CellIsRule gives way to FormulaRule, and why a rule sometimes attaches but renders nothing. For the wider tour of every rule type, see the parent guide, Applying Conditional Formatting with openpyxl.

CellIsRule greater than 50 colors every cell above the threshold A column of values from 10 to 100 with a CellIsRule for greater than 50; cells at 60, 70, 80, 90, and 100 are filled while 10 to 50 stay plain. Column A10:A19 CellIsRule('greaterThan', ['50']) > 50 10 30 50 60 80 100 if value > 50 apply blue PatternFill cells above the threshold are colored

Prerequisites

  • Python 3.8 or newer.
  • openpyxl installed (pip install openpyxl). Everything here uses the current 3.1 API.
  • A basic grasp of A1 cell references (A1, A1:C10) — the same notation you type in Excel's formula bar.
  • The colour objects come from openpyxl.styles; if you have not met them, Styling Excel Cells with openpyxl covers PatternFill and Font in full.

No spreadsheet file is required up front — the first snippet builds one from scratch.

Step-by-step: bind a rule to a range

The pattern is always three steps: create the fill/font, build the rule, then add() it to a range string. Here is the complete minimal example.

Bash
pip install openpyxl
Python
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

# 1. Populate a column with sample values: 10, 20, ... 100
for row in range(1, 11):
    ws.cell(row=row, column=1, value=row * 10)

# 2. Style to apply when the condition matches
warn_fill = PatternFill("solid", fgColor="FFC7CE")
bold_red = Font(color="9C0006", bold=True)

# 3. Build the rule and bind it to the range
rule = CellIsRule(operator="greaterThan", formula=["50"], fill=warn_fill, font=bold_red)
ws.conditional_formatting.add("A1:A10", rule)

wb.save("conditional_range.xlsx")
print("Rule applied to A1:A10")

Cells over 50 (60–100) get a red fill and bold red text when the file is opened in Excel. The rule lives in the file, not in the numbers — Excel re-evaluates it every time a value in A1:A10 changes.

Range syntax

add() binds a rule to a range expressed as an A1-style string:

  • Contiguous block: "A1:C10"
  • A single column: "A1:A10"
  • Non-contiguous: "A1:A10 C1:C10" — separate ranges with a space (this is what Excel stores in the sqref attribute)
A space-separated range string binds one rule to two non-contiguous columns A worksheet grid with columns A and C highlighted and column B skipped maps to the range string "A1:A10 C1:C10", where the single space between the two blocks is stored verbatim in the sqref attribute. Worksheet A B C 1 2 3 ⋮ 10 A and C selected, B skipped One range string A1:A10 space C1:C10 sqref="A1:A10 C1:C10" stored verbatim in the worksheet XML
Python
multi_rule = CellIsRule(operator="greaterThan", formula=["50"], fill=warn_fill)
ws.conditional_formatting.add("A1:A10 C1:C10", multi_rule)
wb.save("conditional_range.xlsx")
print("Rule applied to two ranges at once")

Named ranges are not accepted by add() — pass explicit cell references only.

CellIsRule vs FormulaRule

CellIsRule compares each cell against one or two literals: formula=["50"] for a single threshold, formula=["50", "100"] with operator="between". The values stay constant across the whole range.

For thresholds that reference other cells, switch to FormulaRule. Its formula is a relative Excel expression with no leading = — openpyxl writes the string verbatim, and Excel rejects a formula beginning with =. The reference uses the first row of the target range and shifts down automatically:

Python
from openpyxl.formatting.rule import FormulaRule

# Highlight values strictly between 50 and 100
between_rule = FormulaRule(
    formula=["AND(A1>50, A1<100)"],   # note: no leading "="
    fill=warn_fill,
    font=bold_red,
    stopIfTrue=True,
)
ws.conditional_formatting.add("A1:A10", between_rule)
wb.save("conditional_range.xlsx")
print("FormulaRule applied")

When building rules from an Advanced Data Transformation and Cleaning pipeline, generate the threshold strings from your validation limits rather than hardcoding them. If the source data still has gaps or stray text, run it through Cleaning Excel Data with Pandas first — a FormulaRule comparing against a blank cell evaluates unpredictably.

Rule priority

Rules apply in insertion order, and openpyxl numbers their priority accordingly. Constructors do not take a priority keyword — set the attribute afterward when ordering matters:

Python
rule.priority = 1
between_rule.priority = 2
print("Priorities:", rule.priority, between_rule.priority)

For overlapping ranges, set stopIfTrue=True on the higher-priority rule to stop Excel evaluating the rest for a matched cell.

Inspecting attached rules

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

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

Common pitfalls and gotchas

  1. formula must be a list of strings. Use ["50"], not 50. For FormulaRule, omit the leading =: ["A1>100"], not ["=A1>100"].
  2. Invisible fills. PatternFill needs an explicit color and fill_type="solid" (passed positionally above as "solid"); without it the fill defaults to None and renders nothing.
  3. Stale rendering. Excel may cache calculation state. Reopen the file or trigger Formulas → Calculate Now to force re-evaluation.
  4. Re-running stacks duplicates. ConditionalFormattingList has no clear(); reassign a fresh ConditionalFormattingList() to wipe a sheet's rules before re-adding.
  5. Wrong anchor row in FormulaRule. The formula is relative to the first cell of the range, not the cell it happens to sit in. Anchor your reference to that first row (A1, not the mid-range cell) and let Excel shift it down.

Performance and scale

Each rule is a discrete XML block, and overlapping ranges multiply serialization work. Across ranges over ~100,000 cells, file size and Excel's initial load time grow noticeably. Restrict formatting to summary tables, or apply a native Excel table style with ws.add_table() instead of per-cell rules. One rule over A2:A100000 is far cheaper than 100,000 single-cell rules, so always bind the widest range a rule can legitimately cover rather than looping cell by cell.

Anchoring inside a range rule

Relative and anchored references inside a range rule A formula written with dollar signs on the column and none on the row compares each row against its own values. Anchoring both pins every row to the first, so one comparison is repeated down the whole range. $D2 < $E2 column pinned, row moves each row compared to itself what you almost always want $D$2 < $E$2 both pinned every row uses row 2 looks almost right
Python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

ws.conditional_formatting.add(
    f"A2:F{ws.max_row}",
    FormulaRule(formula=["$D2<$E2"], fill=PatternFill("solid", start_color="FFC7CE")),
)

The formula is written as though for the top-left cell of the range, and Excel offsets it for every other cell. That is why $D2 — column pinned, row free — highlights each row against its own target, while $D$2 compares all of them against row two and produces a sheet that is either entirely coloured or entirely plain.

Applying one rule to the whole rectangle rather than one per row keeps the workbook fast and the rule list readable in Excel's own manager, where a hundred near-identical rules is unmaintainable.

Rebuild the range on every run

A rule written once against D2:D500 stops covering the sheet the month it grows past five hundred rows, and nothing about the file looks wrong — the extra rows simply never colour. Deriving the range from ws.max_row inside the job removes the whole failure mode, and clearing the sheet's existing rules first stops last month's ranges accumulating alongside this month's. Two lines at the top of the formatting step, and the rules always match the data they describe.

One rule, one range

Applying a rule once to the whole rectangle is both faster and easier to maintain than applying it per row — and it produces identical output.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

Conclusion

Applying conditional formatting to a range comes down to one call — ws.conditional_formatting.add(range_string, rule) — with the subtlety living in the range string (space-separated for non-contiguous blocks, A1-only, no named ranges) and in the choice between CellIsRule for fixed literals and FormulaRule for cell-relative thresholds. Keep the fill visible with fill_type="solid", set priority/stopIfTrue when rules overlap, and iterate ws.conditional_formatting to confirm the rule actually attached before you save.

Frequently asked questions

How do I apply one rule to several non-contiguous ranges at once? Pass them in a single A1 string separated by spaces: add("A1:A10 C1:C10", rule). That space-separated form is exactly what Excel stores in the sqref attribute.

Can I use a named range instead of cell references? No. add() only accepts explicit A1-style coordinates; named ranges are rejected. Pass the literal range string.

When should I use FormulaRule instead of CellIsRule? Use CellIsRule when comparing each cell against fixed literals. Switch to FormulaRule when the threshold references other cells — its formula is relative to the first row of the range and shifts down automatically, with no leading =.

Why does formula=50 raise an error?formula must be a list of strings, even for a single value: use ["50"], not 50. For between, pass two: ["50", "100"].

My formatting doesn't update after reopening — is the rule wrong? Not necessarily; Excel can cache calculation state. Reopen the file or trigger Formulas → Calculate Now to force re-evaluation, and confirm the rule actually attached by iterating ws.conditional_formatting.