Writing Conditional Formatting From Code in Workbooks

A table of 400 rows hides its important rows. The ones that are negative, overdue, below budget or suddenly doubled look just like all the others until someone reads each number. Conditional formatting makes Excel color those cells by rule, so the exceptions stand out and the normal rows recede. Writing the rules from code makes the highlighting part of the report rather than something an analyst repeats by hand each month. This article shows how with both common libraries, how to write formula rules for whole-row highlights and how to avoid the mistakes that make a report slow or misleading. It fits into building Excel reports from SQL queries people trust.

How the rules are stored

A conditional format is not a color written into the cell. The cell stays a plain number, and the sheet XML holds a conditionalFormatting element with a range and a list of rules. Excel evaluates the rules when it draws the cells. This has two consequences. The values stay numeric and sortable, and the highlight follows the data if the user sorts or edits. And the rules can be seen and edited under Home, Conditional Formatting, Manage Rules, which is where a reader goes when a highlight surprises them.

Cell value rules with XlsxWriter

The simplest rule compares each cell with a number. Create a format object, then attach the rule to a range.

import pandas as pd

with pd.ExcelWriter("variance.xlsx", engine="xlsxwriter") as xw:
    df.to_excel(xw, sheet_name="Variance", index=False)
    wb, ws = xw.book, xw.sheets["Variance"]
    n = len(df)

    red = wb.add_format({"font_color": "#9C0006", "bg_color": "#FFC7CE"})
    green = wb.add_format({"font_color": "#006100", "bg_color": "#C6EFCE"})

    # Column E holds variance; rows 2..n+1 in Excel terms
    ws.conditional_format(1, 4, n, 4, {"type": "cell", "criteria": "<", "value": 0, "format": red})
    ws.conditional_format(1, 4, n, 4, {"type": "cell", "criteria": ">=", "value": 0, "format": green})

The first four arguments are first_row, first_col, last_row, last_col, zero-based. n rows of data start at index 1 because index 0 is the header. The criteria values include ">", "<", ">=", "between" and "not between"; for between use minimum and maximum keys. The full list is in the XlsxWriter conditional formatting documentation.

The colors in the example are the standard Excel "bad" and "good" palette, which readers recognize.

Color scales and data bars

When the question is "how big is each value relative to the others?" a scale or bar answers it without a threshold.

ws.conditional_format(1, 3, n, 3, {
    "type": "3_color_scale",
    "min_color": "#F8696B", "mid_color": "#FFEB84", "max_color": "#63BE7B",
})

ws.conditional_format(1, 2, n, 2, {
    "type": "data_bar",
    "bar_solid": True,
    "bar_color": "#638EC6",
})

A three color scale maps the lowest value to red, the median to yellow and the highest to green by default. That is helpful for a column where higher is better and a trap where it is not. Reverse the colors for costs. Data bars draw a bar in each cell proportional to the value, and they read well in a narrow column of positive numbers. Use bar_solid for a flat fill; the default gradient fill renders differently across Excel versions.

Rules with openpyxl

openpyxl exposes the same ideas as rule classes.

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule
from openpyxl.styles import PatternFill, Font

wb = load_workbook("variance.xlsx")
ws = wb["Variance"]
last = ws.max_row

red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
ws.conditional_formatting.add(
    f"E2:E{last}",
    CellIsRule(operator="lessThan", formula=["0"], fill=red_fill, font=Font(color="9C0006")),
)
ws.conditional_formatting.add(
    f"D2:D{last}",
    ColorScaleRule(start_type="min", start_color="F8696B", mid_type="percentile", mid_value=50,
                   mid_color="FFEB84", end_type="max", end_color="63BE7B"),
)
ws.conditional_formatting.add(f"C2:C{last}", DataBarRule(start_type="min", end_type="max", color="638EC6"))
wb.save("variance.xlsx")

Ranges are strings in A1 notation here, with the last row computed from the sheet. Note the formula=["0"] list: openpyxl takes the comparison value as a formula string. The details are in the openpyxl conditional formatting guide.

Highlight a whole row with a formula rule

The most useful rule is a formula that decides for a row. Highlight every row where the status column says "Overdue", across columns A to F:

amber = wb.add_format({"bg_color": "#FFEB9C"})
ws.conditional_format(1, 0, n, 5, {
    "type": "formula",
    "criteria": '=$F2="Overdue"',
    "format": amber,
})

Two details make this work. The formula is written as if for the top left cell of the range, row 2, and Excel adjusts it for every other cell. And the dollar sign on the column ($F) fixes the reference to column F while the row stays relative, so each cell in a row looks at its own row's status. Forgetting the dollar sign makes cell B2 look at G2, and the highlight lands in a diagonal mess.

Formula rules can express things a cell rule cannot: a row where actual > budget * 1.1, a duplicate (=COUNTIF($A$2:$A$400,$A2)>1), a weekend date (=WEEKDAY($B2,2)>5), a value outside the average (=ABS($D2-AVERAGE($D$2:$D$400))>2*STDEV($D$2:$D$400)). Test each formula in a spare cell before putting it into a rule. A duplicate check uses a range whose size grows with the data, and on a large sheet it is slow; limit it to the rows that exist (spotting data entry errors with conditional formatting has more of these).

Do not rely on color alone

About one man in twelve has some form of red-green color vision deficiency. A report that marks good as green and bad as red and does nothing else is unreadable for them, and for anyone who prints in black and white. Pair the color with something else:

• A number format that shows a sign or arrow: [Color10]▲ 0.0%;[Red]▼ 0.0% is one example, using an arrow and a color together. Keep the arrow characters in a format code only if your readers' fonts have them, and test on the target systems.

• Bold or italic for the exceptions.

• A status column with words ("Over budget") that is itself the thing being highlighted.

• Colors chosen to differ in lightness as well as hue, so they separate in grayscale.

Include a one-line legend on the sheet or on the README: "Red: more than 10% over budget."

Keep the rules few and the ranges bounded

Excel re-evaluates conditional formatting whenever it redraws, scrolls or recalculates. A handful of rules on a few thousand cells costs nothing. Dozens of rules on whole columns (A:A), or formulas that scan large ranges for every cell, slow a workbook noticeably and make scrolling jerky. So apply each rule to the actual data range, not the entire column; avoid volatile functions like TODAY() and INDIRECT inside rules on large ranges; and prefer one formula rule with an OR to five near-identical rules. If a rule fires on every row, it is decoration. Highlighting works only when most cells are plain.

Thresholds in the open

A threshold hidden in a code string, value: 0.1, becomes folklore. Put it where a reader can see it: in a cell on a Settings or README sheet, and reference that cell in the rule (=$E2>Settings!$B$2). Then the finance team can change 10% to 8% without a developer, and the report shows what the threshold is. Rules that refer to another sheet work in modern Excel; older versions required a named range, so define one if you must support them.

Check the result

Open the file and test the rules with edited values: type a negative number where the rule should fire and a positive one where it should not. Check that the highlight survives sorting and filtering. For an automated check, load the workbook with openpyxl and assert that the expected ranges have rules: ws.conditional_formatting iterates the ranges, each with its rules, which is enough to catch a rule that was dropped by a refactor. See testing a generated Excel report for more.

When conditional formatting is the wrong tool

Conditional formatting is the wrong tool when the highlight must survive being pasted into another system, since it lives in the workbook and not in the data. In that case add a status column that holds the text. It is wrong when the file will be read by a program, which sees only values. And it is wrong when you highlight so much that the report looks like a traffic light, because nothing stands out. For sparklines and in-cell visuals that help a quick scan, see sparklines and conditional formatting for quick scans, and for the number formats that go with it, adding formulas and number formats from Python.