Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Apply Conditional Formatting with xlsxwriter

Write rules Excel re-evaluates: cell and text criteria, colour scales and data bars, a formula rule that highlights whole rows, and the absolute-reference trap that makes a row rule follow the wrong column.

A colour written into a cell is a fact about the moment the file was generated. A conditional format is a rule stored in the workbook, which Excel re-evaluates every time the values change — so a reader who edits a figure, filters the table or pastes in next month's numbers still sees the right cells highlighted.

That difference is why conditional formatting is worth reaching for in generated reports rather than computing the colours in pandas. This guide covers xlsxwriter's rule types, the whole-row formula rule that most reports end up wanting, and the reference-anchoring detail that decides whether it works. It is part of Building Excel Reports with xlsxwriter.

Four rule families and the question each one answers A cell rule answers a yes-or-no question against a threshold. A colour scale shows where each value sits within the range of the column. A data bar compares magnitudes across rows at a glance. A formula rule tests any expression and can format an entire row from one column's value. cell rule -12.5% "is this one bad?" a threshold everyone already agrees on colour scale "where does it sit?" a distribution with no natural cut-off data bar "which is biggest?" magnitude, in the cell instead of a chart formula rule "flag the whole row" one column decides, every column shows it

Prerequisites

Bash
pip install xlsxwriter pandas

The examples build their own workbook, so they run as written. Everything applies equally when xlsxwriter is driven through pd.ExcelWriter(engine="xlsxwriter").

Step 1: Set up a sheet to format

Python
import pandas as pd
import xlsxwriter

df = pd.DataFrame({
    "region":   ["North", "South", "East", "West", "Central"],
    "amount":   [150.25, 274.75, 75.0, 190.4, 88.1],
    "variance": [0.041, -0.062, -0.128, 0.012, -0.005],
    "status":   ["ok", "watch", "breach", "ok", "ok"],
})

wb = xlsxwriter.Workbook("flagged.xlsx")
ws = wb.add_worksheet("Regions")
header = wb.add_format({"bold": True, "bg_color": "#1F4E78",
                        "font_color": "white", "border": 1})

ws.write_row(0, 0, ["Region", "Amount", "Variance", "Status"], header)
for i, row in enumerate(df.itertuples(index=False), start=1):
    ws.write_string(i, 0, row.region)
    ws.write_number(i, 1, row.amount)
    ws.write_number(i, 2, row.variance)
    ws.write_string(i, 3, row.status)

ws.set_column("A:A", 14)
ws.set_column("B:B", 14, wb.add_format({"num_format": '#,##0.00'}))
ws.set_column("C:C", 12, wb.add_format({"num_format": "0.0%"}))
ws.set_column("D:D", 12)

LAST = len(df)                     # last data row, zero-based

Step 2: Cell and text rules

A cell rule compares each cell in the range against a literal:

Python
bad = wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
good = wb.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})

ws.conditional_format(1, 2, LAST, 2, {
    "type": "cell", "criteria": "<", "value": -0.05, "format": bad,
})
ws.conditional_format(1, 2, LAST, 2, {
    "type": "cell", "criteria": ">=", "value": 0.0, "format": good,
})
ws.conditional_format(1, 3, LAST, 3, {
    "type": "text", "criteria": "containing", "value": "breach", "format": bad,
})

Rules are evaluated in the order they are added, and — unlike some spreadsheet behaviour people expect — several can apply to the same cell, with later rules layering on top for any attribute the earlier ones did not set. Where two rules genuinely conflict, add "stop_if_true": True to the first so the second is skipped.

The row and column form (conditional_format(first_row, first_col, last_row, last_col, options)) is worth preferring to the string form ("C2:C6") for the same reason chart ranges are: it is computed from LAST, so it stays correct when the data grows.

Step 3: Colour scales, data bars and icon sets

Where there is no agreed threshold, show the shape of the data instead:

Python
ws.conditional_format(1, 1, LAST, 1, {
    "type": "3_color_scale",
    "min_color": "#FFC7CE", "mid_color": "#FFEB9C", "max_color": "#C6EFCE",
})

ws.conditional_format(1, 1, LAST, 1, {
    "type": "data_bar",
    "bar_color": "#5B5CF0",
    "bar_solid": True,
    "bar_only": False,          # True hides the number and shows only the bar
    "data_bar_2010": True,      # the newer bar style, incl. negative handling
})

ws.conditional_format(1, 2, LAST, 2, {
    "type": "icon_set",
    "icon_style": "3_arrows",
    "icons": [{"criteria": ">=", "type": "number", "value": 0.02},
              {"criteria": ">=", "type": "number", "value": -0.02}],
})

A data bar and a colour scale on the same range is a legitimate combination — the bar shows magnitude, the fill shows position — but it is also the fastest way to make a table look like a toy. Pick one per column.

data_bar_2010: True opts into the later data-bar specification, which draws negative values from a midpoint rather than from the left edge. Without it, a column containing negatives renders in a way most readers misread.

Step 4: Highlight an entire row from one column

This is the rule most reports actually want, and the one that most often comes out wrong. The rule applies to the whole table's range, but its formula must always test the same column while moving down the rows:

Python
row_flag = wb.add_format({"bg_color": "#FEE8F2"})

ws.conditional_format(1, 0, LAST, 3, {
    "type": "formula",
    "criteria": '=$D2="breach"',      # $D anchors the column, 2 is the anchor row
    "format": row_flag,
})

Two things have to line up. The formula is written relative to the top-left cell of the range — here A2 — so the row number in the formula is 2, not 1 and not $2. And the column must carry a $: without it, the rule tests column A in column A, column B in column B, and so on, which produces a scatter of highlighted cells that looks almost right and is not.

What the dollar sign changes in a whole-row rule Written as D2 without a dollar, Excel shifts the reference sideways for each column, so cell A2 tests D2 but B2 tests E2 and C2 tests F2 — columns that hold something else entirely. Written as $D2, every cell in the row tests column D while the row number still advances down the table. criteria: =D2="breach" A2 → D2 B2 → E2 C2 → F2 D2 → G2 each column tests a different cell E2, F2 and G2 are empty or hold other data, so only part of the row lights up criteria: =$D2="breach" A2 → $D2 B2 → $D2 C2 → $D2 D2 → $D2 every column tests the status column and row 3 tests $D3, row 4 tests $D4 — the row number is deliberately left relative The formula is always written for the range's top-left cell start the range at row 2 and the formula says 2 — Excel rewrites the rest

Step 4b: Order the rules deliberately

Rules are stored in the order they are added, and Excel applies them all — later ones layering over earlier ones for any attribute the earlier ones left unset. That is useful for building up a look from small rules, and it is the reason a "why is that cell amber when it should be red" question usually has a boring answer:

How two overlapping rules combine, with and without stop_if_true A cell of minus twelve percent matches both the below-zero rule and the below-target rule. Without stop_if_true the second rule's fill layers over the first, so the cell shows the softer amber. With stop_if_true on the first rule, evaluation halts there and the cell keeps the red that the more serious condition assigned. A cell of −12.5% matches both rules both rules evaluate 1. below zero → red 2. below target → amber (wins) the later rule paints over the earlier one "stop_if_true": True on rule 1 1. below zero → red (wins) 2. never evaluated for this cell the more serious condition keeps the cell

The habit that avoids the question entirely is to add rules from most serious to least, with "stop_if_true": True on any that should be final. Excel's own dialog exposes the same ordering, so a reader who opens the rules manager sees exactly the sequence your script wrote.

Step 5: Rules that reference other cells

Because the criteria is an ordinary Excel formula, a threshold can live in a cell rather than in your code — which lets a reader change it without regenerating the file:

Python
ws.write("F1", "Threshold")
ws.write_number("F2", -0.05, wb.add_format({"num_format": "0.0%",
                                            "bg_color": "#FDEFD8"}))

ws.conditional_format(1, 2, LAST, 2, {
    "type": "formula",
    "criteria": "=$C2<$F$2",         # both anchored: column C by row, F2 absolutely
    "format": bad,
})
wb.close()

$F$2 is fully absolute so every row compares against the same threshold cell, while $C2 keeps its row relative so it walks down the column. Handing the threshold to the reader like this turns a hard-coded report into one they can explore — and it is the kind of small affordance that stops people exporting your output into their own spreadsheet.

Common pitfalls and gotchas

SymptomCauseFix
Only part of a row highlightsColumn not anchored in the formulaUse $D2, not D2
Nothing highlightsFormula row does not match the range's first rowRange starting at row 2 → formula says 2
Every row highlightsRow anchored as well: $D$2Leave the row relative
Two rules fightBoth apply, later one layers onAdd "stop_if_true": True to the first
Negative data bars look wrongOld bar specification"data_bar_2010": True
Colours ignored in LibreOfficeIcon set or bar style unsupported therePrefer cell rules and colour scales for portability
The file grows and opens slowlyOne rule written per cell in a loopOne rule over the whole range
Rule lost after pandas wrote the sheetApplied before to_excel wrote the cellsApply rules after the data is written

Performance and scale notes

The rule count matters, not the cell count: a single rule over A2:D100000 costs almost nothing, while a hundred thousand single-cell rules bloat the file and make Excel slow to open. If you find yourself in a loop calling conditional_format per row, the rule you want is a formula rule over the whole range.

Colour scales and data bars over very large ranges are also computed by Excel on every recalculation, so on a sheet with hundreds of thousands of rows they are noticeably heavier than a plain cell rule. On a detail sheet that large, put the visual emphasis on the summary and leave the detail plain — which is usually better reporting anyway.

Conclusion

Conditional formatting keeps a generated report honest: the highlight belongs to the rule, not to the moment the file was written, so it stays right when a reader edits or filters. Use cell rules where a threshold is agreed, colour scales and data bars where the distribution is the message, and a formula rule with $ on the column when one field should light the whole row. Apply one rule per range rather than per cell, and put the threshold in a cell when it is something readers should be able to change.

Frequently asked questions

Why does my whole-row rule highlight the wrong rows? The column reference in the formula is not anchored. Write $D5 — dollar on the column, none on the row — so every column in the row tests the same cell while the row number still moves down.

Which cell does the formula refer to? The top-left cell of the range you passed. Excel rewrites the reference for every other cell relative to that anchor, which is why the formula and the range must agree.

Can I use a fill colour that is not one of Excel's presets? Yes. Pass any hex colour to add_format with bg_color and font_color. The classic red-amber-green trio Excel offers is only a convention.

Does conditional formatting slow down a large workbook? One rule over a large range is cheap. Thousands of separate single-cell rules are not — apply one rule to the whole range instead of looping over cells.

Up to the parent guide:

Related guides: