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.

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.

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.

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"].

Where to go next