Conditional Formatting and Data Validation in Excel with Python

A generated workbook rarely stays read-only. Finance adds commentary, operations updates statuses, a regional manager types this month's forecast into a template the script produced. Two problems follow. Highlighting that the script applied as static fills — red for overdue, green for paid — is wrong the moment someone edits a date, because the colour was computed once in Python and never re-evaluated. And free-text entry lets people type closed, Closed , CLOSED and done into a status column that a downstream pandas job expects to contain exactly four values, which then quietly breaks group-bys and dashboards.

Excel has native answers for both: conditional formatting rules that Excel re-evaluates on every change, and data validation that constrains what a cell accepts. Writing them from Python is straightforward once you think in Excel's terms — ranges, relative formulas and differential styles — rather than in terms of looping over cells and painting them. This guide builds a tracker workbook with both, using openpyxl for workbooks you load and modify and xlsxwriter for workbooks you generate from scratch, and shows how to verify the rules actually made it into the file.

Prerequisites

python -m venv .venv && source .venv/bin/activate
pip install "openpyxl>=3.1" xlsxwriter "pandas>=2.2"
mkdir -p in out

openpyxl reads and writes existing workbooks, which is what you need when a template from the business already contains layout and you add rules to it. xlsxwriter only writes new files but has the more complete formatting API and is faster for large sheets. Both produce rules that Excel, LibreOffice and Google Sheets understand. Keep a copy of a real template in in/ — rules that look right in a fresh workbook can conflict with rules already present in a template.

Diagnostic: Inspect Existing Rules Before Adding New Ones

Templates accumulate rules. Copy-pasting rows in Excel duplicates conditional formatting ranges until a sheet carries hundreds of fragmented rules, and adding yours on top makes behaviour unpredictable. List what is there first.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook

TEMPLATE = Path("in/order-tracker-template.xlsx")

def list_rules(path: Path) -> None:
    try:
        wb = load_workbook(path)                     # not read_only: rules are needed
    except (OSError, KeyError) as exc:
        raise SystemExit(f"cannot open {path}: {exc}")
    for ws in wb.worksheets:
        cf_count = 0
        for cf in ws.conditional_formatting:
            for rule in cf.rules:
                cf_count += 1
                formula = rule.formula[0] if rule.formula else ""
                print(f"[{ws.title}] CF  {str(cf.sqref):<22} type={rule.type:<12} "
                      f"op={rule.operator or '-':<10} priority={rule.priority} {formula}")
        for dv in ws.data_validations.dataValidation:
            print(f"[{ws.title}] DV  {str(dv.sqref):<22} type={dv.type:<8} "
                  f"formula1={dv.formula1!r} error_shown={dv.showErrorMessage}")
        print(f"[{ws.title}] {cf_count} conditional rule(s), "
              f"{len(ws.data_validations.dataValidation)} validation(s)")

if __name__ == "__main__":
    list_rules(TEMPLATE)

[Orders] CF  E2:E500                type=cellIs       op=lessThan   priority=1 TODAY()
[Orders] CF  E17:E18 E44            type=cellIs       op=lessThan   priority=2 TODAY()
[Orders] DV  F2:F500                type=list     formula1='"Open,Shipped,Closed"' error_shown=False
[Orders] 2 conditional rule(s), 1 validation(s)

Two findings are typical. The fragmented range E17:E18 E44 is a duplicate created by copy-paste; clean it up rather than adding a third rule. And the existing validation has showErrorMessage false, which means Excel shows the dropdown but still accepts anything typed — the most common reason "validation doesn't work", covered in fix openpyxl data validation not working.

Core Implementation

Step 1: Write the Data and Define the Ranges

Rules are attached to ranges, so compute the ranges from the data rather than hard-coding A2:H500. A table that grows next month then still gets formatted, and a range that stops at the last row avoids formatting thousands of empty rows.

# pip install "pandas>=2.2" "openpyxl>=3.1"
from pathlib import Path
import pandas as pd
from openpyxl.utils import get_column_letter

DEST = Path("out/order-tracker.xlsx")

orders = pd.DataFrame({
    "order_id": ["SO-1001", "SO-1002", "SO-1003", "SO-1004"],
    "customer": ["Northwind", "Contoso", "Fabrikam", "Adatum"],
    "amount": [1250.0, -80.0, 9800.0, 430.5],
    "due_date": pd.to_datetime(["2026-09-01", "2026-09-30", "2026-08-15", "2026-10-10"]),
    "status": ["Open", "Closed", "Open", "Shipped"],
})

HEADROOM = 500                                     # rows users can append below the data

def column_range(frame: pd.DataFrame, column: str, headroom: int = HEADROOM) -> str:
    idx = frame.columns.get_loc(column) + 1
    letter = get_column_letter(idx)
    return f"{letter}2:{letter}{len(frame) + 1 + headroom}"

def table_range(frame: pd.DataFrame, headroom: int = HEADROOM) -> str:
    last = get_column_letter(len(frame.columns))
    return f"A2:{last}{len(frame) + 1 + headroom}"

with pd.ExcelWriter(DEST, engine="openpyxl") as writer:
    orders.to_excel(writer, sheet_name="Orders", index=False)
print(column_range(orders, "due_date"), table_range(orders))   # D2:D505 A2:E505

Headroom rows matter for templates people type into. Without them, rows added under the data get neither formatting nor validation.

Step 2: Add Conditional Formatting Rules

openpyxl provides builders for the common rule types. Colours use PatternFill and Font, but Excel stores them as a differential style — only the properties you set override the cell's own style.

# pip install "openpyxl>=3.1"
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule, ColorScaleRule, DataBarRule
from openpyxl.styles import Font, PatternFill

RED_FILL = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
RED_FONT = Font(color="9C0006")
AMBER_FILL = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")

def add_formatting(path, amount_rng: str, due_rng: str, table_rng: str) -> None:
    wb = load_workbook(path)
    ws = wb["Orders"]
    try:
        # 1. Negative amounts in red text
        ws.conditional_formatting.add(
            amount_rng, CellIsRule(operator="lessThan", formula=["0"], font=RED_FONT))
        # 2. Whole row amber when overdue and not closed (relative row, absolute columns)
        first_row = table_rng.split(":")[0][1:]                    # "2"
        ws.conditional_formatting.add(
            table_rng,
            FormulaRule(formula=[f'AND($D{first_row}<TODAY(),$E{first_row}<>"Closed",$A{first_row}<>"")'],
                        fill=AMBER_FILL, stopIfTrue=True))
        # 3. Data bars on amounts for a quick visual scale
        ws.conditional_formatting.add(
            amount_rng, DataBarRule(start_type="min", end_type="max", color="638EC6"))
        wb.save(path)
    finally:
        wb.close()

The row rule is where most mistakes happen. The formula is written for the first row of the range, with dollar signs on the columns and none on the row: Excel shifts the row reference as it evaluates each row, so $D2 becomes $D3, $D4 and so on, while the column stays pinned to D. $A2<>"" stops empty headroom rows from being highlighted as overdue. The full treatment of row rules, including priority and multiple colours, is in highlight rows with conditional formatting.

Step 3: Add Data Validation

# pip install "openpyxl>=3.1"
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

STATUSES = ["Open", "Shipped", "Closed", "Cancelled"]

def add_validation(path, status_rng: str, amount_rng: str, due_rng: str) -> None:
    wb = load_workbook(path)
    ws = wb["Orders"]
    try:
        status = DataValidation(
            type="list",
            formula1='"' + ",".join(STATUSES) + '"',        # quoted, comma-separated literal list
            allow_blank=True,
            showDropDown=False,                              # False means the arrow IS shown (Excel quirk)
            showErrorMessage=True,                           # without this, invalid input is accepted
            errorTitle="Invalid status",
            error="Pick a status from the list.",
            showInputMessage=True,
            promptTitle="Status",
            prompt="Open, Shipped, Closed or Cancelled",
        )
        amount = DataValidation(type="decimal", operator="between",
                                formula1="-100000", formula2="1000000",
                                showErrorMessage=True, error="Amount out of range.")
        due = DataValidation(type="date", operator="greaterThan", formula1="DATE(2020,1,1)",
                             showErrorMessage=True, error="Enter a real date after 2020.")
        for dv, rng in ((status, status_rng), (amount, amount_rng), (due, due_rng)):
            ws.add_data_validation(dv)                       # attach to the sheet ...
            dv.add(rng)                                      # ... and to the range
        wb.save(path)
    finally:
        wb.close()

Two openpyxl details catch everyone. showDropDown=False is correct for a visible arrow — the attribute name comes from the file format, where it means "suppress the dropdown". And showErrorMessage defaults to False, so without it Excel shows the list but lets users type anything. Inline lists are limited to 255 characters including commas; longer lists belong on a lookup sheet, as shown in add dropdown lists to Excel with openpyxl.

Step 4: Freeze, Filter and Protect the Structure

Rules are more useful when the header stays visible and the structure cannot be broken by accident. Sheet protection with unlocked input cells lets people edit data while leaving rules and formulas intact.

# pip install "openpyxl>=3.1"
from openpyxl import load_workbook
from openpyxl.styles import Protection

def finish_sheet(path, editable_cols: list[str], last_row: int, password: str) -> None:
    wb = load_workbook(path)
    ws = wb["Orders"]
    try:
        ws.freeze_panes = "A2"
        ws.auto_filter.ref = f"A1:{ws.cell(1, ws.max_column).column_letter}{last_row}"
        for col in editable_cols:
            for (cell,) in ws.iter_rows(min_row=2, max_row=last_row,
                                        min_col=ws[col + "1"].column, max_col=ws[col + "1"].column):
                cell.protection = Protection(locked=False)
        ws.protection.sheet = True
        ws.protection.autoFilter = False            # allow filtering on a protected sheet
        ws.protection.sort = False
        ws.protection.set_password(password)
        wb.save(path)
    finally:
        wb.close()

Sheet protection is a guard against accidents, not security — the password hash is weak and trivially removed. Use it to keep people from deleting the formula column, not to hide data.

Edge Cases and Variants

Generating from Scratch with xlsxwriter

When the workbook is created by your code, xlsxwriter expresses the same rules more compactly and writes large sheets faster:

# pip install xlsxwriter "pandas>=2.2"
from pathlib import Path
import pandas as pd

def write_tracker(orders: pd.DataFrame, dest: Path, headroom: int = 500) -> None:
    last = len(orders) + 1 + headroom
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        orders.to_excel(writer, sheet_name="Orders", index=False)
        book, ws = writer.book, writer.sheets["Orders"]
        amber = book.add_format({"bg_color": "#FFEB9C"})
        red_text = book.add_format({"font_color": "#9C0006"})
        ws.conditional_format(f"A2:E{last}", {
            "type": "formula",
            "criteria": '=AND($D2<TODAY(),$E2<>"Closed",$A2<>"")',
            "format": amber, "stop_if_true": True,
        })
        ws.conditional_format(f"C2:C{last}", {"type": "cell", "criteria": "<", "value": 0,
                                              "format": red_text})
        ws.data_validation(f"E2:E{last}", {
            "validate": "list", "source": ["Open", "Shipped", "Closed", "Cancelled"],
            "error_title": "Invalid status", "error_message": "Pick a status from the list.",
        })
        ws.freeze_panes(1, 0)

xlsxwriter formula criteria start with =; openpyxl formulas do not. Mixing the two conventions produces rules that Excel silently ignores or repairs on open.

Rules That Reference Another Sheet

Conditional formatting formulas and validation lists may reference other sheets, but older Excel versions reject direct cross-sheet references in rules. A defined name works everywhere:

# pip install "openpyxl>=3.1"
from openpyxl.workbook.defined_name import DefinedName

def add_named_range(wb, name: str, ref: str) -> None:
    dn = DefinedName(name, attr_text=ref)                 # e.g. "Lists!$A$2:$A$40"
    wb.defined_names[name] = dn                            # openpyxl 3.1 API

Then use formula1=f"={name}" for a validation list or COUNTIF({name},$B2)>0 in a formatting rule.

Colour Scales and Icon Sets

For numeric columns where the pattern matters more than a threshold, a colour scale shows distribution at a glance:

# pip install "openpyxl>=3.1"
from openpyxl.formatting.rule import ColorScaleRule, IconSetRule

scale = ColorScaleRule(start_type="percentile", start_value=10, start_color="F8696B",
                       mid_type="percentile", mid_value=50, mid_color="FFEB84",
                       end_type="percentile", end_value=90, end_color="63BE7B")
icons = IconSetRule("3Arrows", "percent", [0, 33, 67], showValue=True)
# ws.conditional_formatting.add("C2:C505", scale)

Percentile anchors resist outliers far better than min/max, where one enormous order turns every other cell the same colour.

Cleaning Up Fragmented Rules in Old Templates

The diagnostic often reveals dozens of copies of the same rule on scattered ranges, created by users inserting and pasting rows over months. Rebuilding them is safer than trying to merge them in place: collect the distinct rule definitions, clear the sheet's formatting, and re-add each rule once on a clean range.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList

def consolidate_rules(path: Path, sheet: str, clean_range: str) -> int:
    """Re-add each distinct rule once on clean_range; returns the number of rules kept."""
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        distinct = {}
        for cf in ws.conditional_formatting:
            for rule in cf.rules:
                key = (rule.type, rule.operator, tuple(rule.formula or ()))
                distinct.setdefault(key, rule)            # keep the first copy of each rule
        ws.conditional_formatting = ConditionalFormattingList()
        for priority, rule in enumerate(distinct.values(), start=1):
            rule.priority = priority
            ws.conditional_formatting.add(clean_range, rule)
        wb.save(path)
        return len(distinct)
    finally:
        wb.close()

This assumes the fragmented copies were all meant to cover the same table, which is true for the copy-paste pattern but not for templates that deliberately format different blocks differently. Run the diagnostic first and group rules by the range they should cover before consolidating; a quick review of the printed rule list takes a minute and prevents moving a header-only rule onto the data.

Validation

Read the saved workbook back and assert the rules exist on the intended ranges with the intended settings. Do this in the same job that builds the workbook; a refactor that drops a rule otherwise ships unnoticed, because the data still looks right.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook

def verify_rules(path: Path, sheet: str, expect_cf: dict[str, int], expect_dv: dict[str, str]) -> None:
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        cf_by_range: dict[str, int] = {}
        for cf in ws.conditional_formatting:
            cf_by_range[str(cf.sqref)] = cf_by_range.get(str(cf.sqref), 0) + len(cf.rules)
        for rng, count in expect_cf.items():
            assert cf_by_range.get(rng, 0) >= count, f"{rng}: {cf_by_range.get(rng, 0)} rule(s), want {count}"
        dv_by_range = {str(dv.sqref): dv for dv in ws.data_validations.dataValidation}
        for rng, dtype in expect_dv.items():
            dv = dv_by_range.get(rng)
            assert dv is not None, f"no validation on {rng}"
            assert dv.type == dtype, f"{rng}: type {dv.type}, want {dtype}"
            assert dv.showErrorMessage, f"{rng}: error message disabled, invalid input accepted"
        print(f"{path.name}: formatting and validation verified")
    finally:
        wb.close()

if __name__ == "__main__":
    verify_rules(Path("out/order-tracker.xlsx"), "Orders",
                 expect_cf={"A2:E505": 1, "C2:C505": 2},
                 expect_dv={"E2:E505": "list", "C2:C505": "decimal", "D2:D505": "date"})

Python cannot evaluate conditional formatting — only Excel or LibreOffice does — so this checks structure, not appearance. For appearance, open the output in LibreOffice headless and export a PDF of the sheet as part of a release check, or review a sample file by hand whenever the rules change.

Performance and Scale Notes

Rules cost almost nothing to write, but they cost the user recalculation time: a formula rule on 100,000 rows re-evaluates across the whole range whenever anything changes, and volatile functions such as TODAY() and INDIRECT() force re-evaluation on every recalc. Keep ranges to the data plus reasonable headroom rather than whole columns, prefer one rule over many fragmented copies, and avoid INDIRECT in rules altogether. On the Python side, openpyxl loads the whole workbook into memory; for files over a few hundred thousand cells, generate with xlsxwriter in constant-memory mode instead, noting that constant-memory mode requires writing rows in order and adding rules after the data.

Troubleshooting

Complete Working Script

#!/usr/bin/env python3
# pip install "pandas>=2.2" "openpyxl>=3.1"
"""Build an order tracker workbook with conditional formatting and data validation."""
import argparse
import sys
from pathlib import Path

import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

STATUSES = ["Open", "Shipped", "Closed", "Cancelled"]
AMBER = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
RED_FONT = Font(color="9C0006")


def build(csv_path: Path, dest: Path, headroom: int) -> None:
    orders = pd.read_csv(csv_path, parse_dates=["due_date"])
    required = {"order_id", "customer", "amount", "due_date", "status"}
    missing = required - set(orders.columns)
    if missing:
        raise ValueError(f"missing columns: {sorted(missing)}")
    orders = orders[["order_id", "customer", "amount", "due_date", "status"]]
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="openpyxl") as writer:
        orders.to_excel(writer, sheet_name="Orders", index=False)

    last = len(orders) + 1 + headroom
    wb = load_workbook(dest)
    ws = wb["Orders"]
    for row in ws.iter_rows(min_row=2, max_row=len(orders) + 1, min_col=4, max_col=4):
        row[0].number_format = "yyyy-mm-dd"
    ws.conditional_formatting.add(f"C2:C{last}",
                                  CellIsRule(operator="lessThan", formula=["0"], font=RED_FONT))
    ws.conditional_formatting.add(f"A2:E{last}", FormulaRule(
        formula=['AND($D2<TODAY(),$E2<>"Closed",$E2<>"Cancelled",$A2<>"")'],
        fill=AMBER, stopIfTrue=True))
    status = DataValidation(type="list", formula1='"' + ",".join(STATUSES) + '"',
                            allow_blank=True, showErrorMessage=True,
                            errorTitle="Invalid status", error="Pick a status from the list.")
    amount = DataValidation(type="decimal", operator="between", formula1="-100000",
                            formula2="1000000", showErrorMessage=True, error="Amount out of range.")
    for dv, rng in ((status, f"E2:E{last}"), (amount, f"C2:C{last}")):
        ws.add_data_validation(dv)
        dv.add(rng)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{len(orders) + 1}"
    wb.save(dest)
    wb.close()


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("csv", type=Path)
    ap.add_argument("dest", type=Path)
    ap.add_argument("--headroom", type=int, default=500)
    args = ap.parse_args()
    try:
        build(args.csv, args.dest, args.headroom)
    except (OSError, ValueError, KeyError) as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 1
    print(f"wrote {args.dest}")
    return 0


if __name__ == "__main__":
    sys.exit(main())

Frequently Asked Questions

Can pandas Styler write conditional formatting?Styler.to_excel writes static cell styles computed in Python, not Excel rules. It is fine for a frozen report; use openpyxl or xlsxwriter rules for anything people will edit.

Does data validation stop pasted values? No. Excel only validates typed entries; pasting over a validated cell replaces the validation too. Validate again downstream, as in Validating Document Data with Schemas.

Will the rules survive a round trip through Google Sheets? Most do. Formula rules, colour scales and list validation convert well; data bars and icon sets become approximations. Test with a real file before promising users anything.

How do I highlight duplicates? Use FormulaRule(formula=["COUNTIF($A$2:$A$505,$A2)>1"], fill=...) on the key column. The same logic in pandas is in remove duplicate rows in pandas.

Related

• Add Dropdown Lists to Excel with openpyxl — long lists, lookup sheets and dependent dropdowns
• Highlight Rows with Conditional Formatting — row rules, priorities and multiple colours
• Fix openpyxl Data Validation Not Working — every reason a rule is ignored
• Working with Excel Dates and Number Formats — the display side of cell styling
• Automating Excel Report Generation — where these workbooks are produced on a schedule

Part of Python for Excel & CSV Data Processing.