Apply Conditional Formatting to a Range in openpyxl

To apply conditional formatting to a range in openpyxl, build a rule object (CellIsRule, FormulaRule, or ColorScaleRule), give it a PatternFill and/or Font, and register it 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.

The minimal working example

pip install openpyxl

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.

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)

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:

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.

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:

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:

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

Troubleshooting

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.

Performance

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.

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.

Where to go next

• Full rule catalog and quirks: Applying Conditional Formatting with openpyxl.
• Clean the data first: Cleaning Excel Data with Pandas.