How to Apply Conditional Formatting to a Range in openpyxl

To apply conditional formatting to a range in openpyxl, instantiate a rule object (CellIsRule or FormulaRule), attach a PatternFill or Font style, and register it using ws.conditional_formatting.add(). openpyxl serializes these rules directly into the worksheet XML, and Excel evaluates the conditions dynamically when the file opens.

Working Implementation

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

wb = Workbook()
ws = wb.active

# 1. Populate sample data
for row in range(1, 11):
 ws.cell(row=row, column=1, value=row * 10)

# 2. Define styling overrides
warn_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
bold_red = Font(color="9C0006", bold=True)

# 3. Create & bind the rule
target_range = "A1:A10"
rule = CellIsRule(operator="greaterThan", formula=["50"], fill=warn_fill, font=bold_red)
ws.conditional_formatting.add(target_range, rule)

wb.save("conditional_range.xlsx")

    

Core Mechanics & Range Syntax

The add() method binds formatting logic to contiguous or non-contiguous ranges. Pass a single Excel-style string ("A1:C10") or a comma-separated list without spaces ("A1:A10,C1:C10"). openpyxl does not evaluate conditions; it writes the rule into the xl/worksheets/sheet1.xml conditional formatting block. Excel handles runtime evaluation against each cell in the target range.

For dynamic thresholds in automated reporting, swap CellIsRule for FormulaRule. This enables relative references (e.g., =B1>0.75) that scale across the entire range without hardcoding absolute coordinates. When building pipelines within a broader Advanced Data Transformation and Cleaning workflow, generate rule definitions programmatically from your validation thresholds rather than embedding static values.

Compatibility & Rule Priority

• openpyxl Version: Requires >=3.0.0 for stable CellIsRule/FormulaRule serialization. Versions 2.6.x lack full dxf (differential formatting) support.
• Excel Version: Renders correctly in Excel 2013+. Excel 2010 may ignore PatternFill transparency or misinterpret Font overrides.
• Rule Priority: Rules apply in insertion order. Overlapping ranges default to the last appended rule unless stopIfTrue=True is explicitly set.
• Unsupported Types: IconSetRule and DataBarRule are partially supported. Complex gradients or custom icon sets often require manual XML injection.
• Range Syntax: Named ranges are not supported in conditional_formatting.add(). Use explicit cell references only.

Troubleshooting & Fallbacks

If styling fails to render, verify these common failure points:

1. Formula Syntax Errors: The formula parameter expects a list of strings. Use ["50"] for CellIsRule, not 50. For FormulaRule, always include the = prefix: ["=A1>100"].
2. Invisible Fills: PatternFill requires explicit start_color and end_color. Omitting fill_type="solid" defaults to None, rendering invisibly.
3. Evaluation Delays: Excel's UI cache or calculation state can delay new rules. Reopen the file or trigger Formulas > Calculate Now.
4. Debugging Output: Inspect registered rules before saving:

      print(ws.conditional_formatting.rules)

    

An empty list or malformed XML indicates the rule was never attached.

Fallback Strategy: When CellIsRule fails due to operator quirks or complex logic, switch to FormulaRule with a boolean expression. This aligns directly with Excel’s native evaluation engine:

      from openpyxl.formatting.rule import FormulaRule

fallback_rule = FormulaRule(
 formula=["=AND(A1>50, A1<100)"],
 fill=warn_fill,
 font=bold_red,
 stopIfTrue=True
)
ws.conditional_formatting.add("A1:A10", fallback_rule)

    

For comprehensive rule configurations, edge-case handling, and multi-sheet iteration patterns, refer to the Applying Conditional Formatting with openpyxl reference.

Performance Optimization

Applying conditional formatting to ranges exceeding 100,000 cells increases .xlsx file size and slows Excel's initial load. openpyxl writes each rule as a discrete XML block; overlapping ranges multiply serialization overhead. For large datasets, restrict formatting to summary tables, or use Excel's native Table styles via ws.add_table() instead of per-cell conditional rules.