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.

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

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.

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)
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.

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)")

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.

Where to go next