Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Highlight an Entire Row Based on a Cell Value with openpyxl

Colour a whole row when its Status is Overdue or its amount crosses a limit, using a formula-based conditional format with the right mix of absolute and relative references — so the colour follows the data after edits and sorts.
Conditional formattingopenpyxl

Highlighting a single cell above a threshold is the conditional formatting example everyone knows. What people usually want in a tracker or an invoice list is different: the whole row turns red when the Status column says Overdue, amber when it says Pending, and grey when the record is closed. Excel does this with a formula rule applied across the full width of the table, and the only hard part is getting one dollar sign in the right place. This guide, part of Applying Conditional Formatting With openpyxl, writes those rules from Python.

Rows coloured by the value in the Status column An invoice list where the row for an overdue invoice is pink across all columns, a pending invoice row is amber, and a paid invoice row is left uncoloured. The colour is driven only by column D. A B C D 1 Invoice Customer Amount Status 2 INV-201 Northwind 1,250 Overdue rule: $D2="Overdue" 3 INV-202 Contoso 980 Paid 4 INV-203 Fabrikam 4,300 Pending rule: $D2="Pending" 5 INV-204 Litware 615 Paid one rule per colour, applied to A2:D5

Prerequisites

Bash
pip install openpyxl

The sample invoice list:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Invoices"
ws.append(["Invoice", "Customer", "Amount", "Status", "Due"])
rows = [
    ("INV-201", "Northwind", 1250, "Overdue", "2026-08-31"),
    ("INV-202", "Contoso", 980, "Paid", "2026-09-05"),
    ("INV-203", "Fabrikam", 4300, "Pending", "2026-09-30"),
    ("INV-204", "Litware", 615, "Paid", "2026-09-12"),
    ("INV-205", "Adatum", 7200, "Pending", "2026-10-02"),
]
for row in rows:
    ws.append(row)
wb.save("invoices.xlsx")

Step 1 — Write a formula rule across the whole row range

A FormulaRule applies formatting wherever its formula is true. The rule is written for the top-left cell of the range it covers, and Excel adjusts it for every other cell exactly as it would copy a formula. Apply it to all columns of the table and test the Status column with $D2.

Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

wb = load_workbook("invoices.xlsx")
ws = wb["Invoices"]
last_row = ws.max_row

overdue = FormulaRule(
    formula=['$D2="Overdue"'],
    fill=PatternFill("solid", start_color="FFC7CE", end_color="FFC7CE"),
    font=Font(color="9C0006"),
)
ws.conditional_formatting.add(f"A2:E{last_row}", overdue)
wb.save("invoices_highlighted.xlsx")

$D2 is the whole trick. The dollar sign fixes the column, so every cell in a row — A, B, C, D, E — looks at column D. The row number has no dollar sign, so it moves: cells in row 3 test $D3, row 4 tests $D4. The colours are Excel's built-in "Light Red Fill with Dark Red Text" pair, which keeps text readable.

How the reference style decides what gets coloured With D2 relative in both directions, each column tests a different column and only some cells colour. With $D$2 fully absolute, every row tests row 2 so the whole range follows the first record. With $D2, column fixed and row relative, each row tests its own Status cell, which colours entire rows correctly. D2 column shifts: B tests E row shifts: correct patchy colouring $D$2 column fixed row fixed to 2 all rows copy row 2 $D2 column fixed to D row follows each row whole rows, correctly fix the column, free the row

Step 2 — Add one rule per status

Each colour is its own rule over the same range. Rules are evaluated in the order they were added, and stopIfTrue prevents a later rule from also applying — useful when conditions can overlap.

Python
def fill(hex_colour: str) -> PatternFill:
    return PatternFill("solid", start_color=hex_colour, end_color=hex_colour)

rules = [
    ('$D2="Overdue"', fill("FFC7CE"), Font(color="9C0006")),
    ('$D2="Pending"', fill("FFEB9C"), Font(color="9C5700")),
    ('$D2="Paid"', fill("F2F2F2"), Font(color="7F7F7F")),
]

wb = load_workbook("invoices.xlsx")
ws = wb["Invoices"]
target = f"A2:E{ws.max_row}"
for formula, rule_fill, rule_font in rules:
    ws.conditional_formatting.add(
        target, FormulaRule(formula=[formula], fill=rule_fill, font=rule_font, stopIfTrue=True)
    )
wb.save("invoices_status.xlsx")
print(len(ws.conditional_formatting[target]), "rules on", target)

Text comparisons in Excel formulas are case-insensitive, so "overdue" typed in lower case still matches. That is convenient, and also a reason not to rely on case to distinguish statuses.

Step 3 — Highlight on numbers, dates and combinations

The formula can be any Excel expression that returns TRUE or FALSE for the row. Numeric thresholds, date comparisons with TODAY(), and combinations with AND and OR all work, as long as each column reference keeps its dollar sign.

Python
wb = load_workbook("invoices.xlsx")
ws = wb["Invoices"]
target = f"A2:E{ws.max_row}"

# Large pending invoices: both conditions on the same row
ws.conditional_formatting.add(target, FormulaRule(
    formula=['AND($D2="Pending",$C2>=5000)'], fill=fill("FFEB9C"), font=Font(bold=True)))

# Past due date and not yet paid (Due is stored as text here, so convert it)
ws.conditional_formatting.add(target, FormulaRule(
    formula=['AND($D2<>"Paid",DATEVALUE($E2)<TODAY())'], fill=fill("FFC7CE")))

wb.save("invoices_rules.xlsx")

TODAY() is recalculated whenever the workbook opens, so a due-date rule keeps working long after the file was written — the colours reflect the day someone looks at the sheet, not the day the script ran. If the dates are real Excel dates rather than text, compare $E2<TODAY() directly; converting text dates properly at write time is covered in Format Dates in Excel Cells with Python.

Example row rule formulas and what they mean A text equality on the status column, a numeric threshold on the amount column, an AND combining status and amount, a due date before today for unpaid rows, and a MOD on ROW for banded rows. $D2="Overdue" status equals a word (case-insensitive) $C2>=5000 amount at or above a limit AND($D2="Pending",$C2>=5000) both conditions on the same row AND($D2<>"Paid",$E2<TODAY()) unpaid and past due, re-evaluated daily MOD(ROW(),2)=0 every other row, for banding any TRUE/FALSE formula works; keep the column references absolute

Step 4 — Cover rows that will be added later

A rule applied to A2:E6 stops at row 6, so invoices appended next week stay uncoloured. Either apply the rule to a generous range from the start, or — better — convert the data to an Excel table and apply the rule to a range that the next append will fall inside.

Python
wb = load_workbook("invoices.xlsx")
ws = wb["Invoices"]
ws.conditional_formatting.add("A2:E5000", FormulaRule(
    formula=['$D2="Overdue"'], fill=fill("FFC7CE"), font=Font(color="9C0006")))

ws.append(["INV-206", "Proseware", 530, "Overdue", "2026-09-01"])   # row 7, inside the rule
wb.save("invoices_future.xlsx")

A range reaching row 5000 costs nothing measurable: Excel evaluates conditional formats only for cells it draws. Blank rows are unaffected because $D2="Overdue" is false for an empty cell. Appending in the right place is covered in Find the Last Row with Data in openpyxl.

Step 5 — Do the same with xlsxwriter

When the report is generated from scratch, xlsxwriter writes the same rule with conditional_format and a formula criteria. The formula convention is identical: written for the top-left cell, column absolute, row relative.

Python
import xlsxwriter

book = xlsxwriter.Workbook("invoices_xlsxwriter.xlsx")
sheet = book.add_worksheet("Invoices")
sheet.write_row(0, 0, ["Invoice", "Customer", "Amount", "Status", "Due"])
for index, row in enumerate(rows, start=1):
    sheet.write_row(index, 0, row)

overdue_format = book.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
sheet.conditional_format(1, 0, len(rows), 4, {
    "type": "formula",
    "criteria": '=$D2="Overdue"',
    "format": overdue_format,
})
book.close()

xlsxwriter's criteria string includes the leading =; openpyxl's formula list does not. Mixing them up produces a rule that Excel reports as invalid. More xlsxwriter rule types are in Apply Conditional Formatting with xlsxwriter.

Step 6 — Check the rules and predict what will be coloured

Conditional formatting cannot be seen without opening Excel, which makes it easy to ship a rule with a typo in a status word. Two checks catch most mistakes before anyone else does: read the rules back from the saved file, and evaluate the same conditions in pandas to see how many rows each one should colour.

Python
import pandas as pd

saved = load_workbook("invoices_status.xlsx")["Invoices"]
for cf_range in saved.conditional_formatting:
    for rule in cf_range.rules:
        print(cf_range.sqref, rule.type, rule.formula, "stop" if rule.stopIfTrue else "")

data = pd.read_excel("invoices_status.xlsx", sheet_name="Invoices")
expected = {
    "Overdue": data["Status"].str.lower().eq("overdue").sum(),
    "Pending": data["Status"].str.lower().eq("pending").sum(),
    "Paid": data["Status"].str.lower().eq("paid").sum(),
}
unmatched = data.loc[~data["Status"].str.lower().isin(["overdue", "pending", "paid"]), "Status"]
print(expected, "no rule for:", unmatched.unique().tolist())

Lower-casing both sides mirrors Excel's case-insensitive comparison, so the counts match what a reader will see. The unmatched list is the valuable part: a status such as Part-paid that no rule covers shows up immediately, instead of being noticed weeks later as the one row that never turns any colour. When statuses come from people typing, pair the rules with a dropdown as in Add Dropdown Data Validation to Excel with openpyxl, so the list of values the rules expect and the list people can choose from are the same list.

Common pitfalls

SymptomCauseFix
Only one column is colouredRule range covers only the Status columnApply the rule to the full row range, e.g. A2:E500
Colours land on the wrong rowsFormula written for row 1 but range starts at row 2Write the formula for the range's first row
Patchy colouring across columnsColumn reference not absolute (D2)Use $D2
Every row copies the first row's colourRow made absolute ($D$2)Remove the dollar sign before the row
Excel says the file has a problem with a ruleLeading = in an openpyxl formula, or smart quotesNo = in openpyxl; use straight quotes
New rows are not colouredRule range ends at the last current rowExtend the range generously

Performance and scale notes

A formula rule is stored once for its whole range, so file size does not grow with the number of rows it covers, and writing it is instant. Excel evaluates the formula for visible cells as it draws them, which is cheap for simple comparisons. Very large ranges with volatile functions such as TODAY() or INDIRECT in the rule can slow scrolling on big sheets; keep rule formulas simple, and prefer a helper column computed in Python when the condition is complex.

Conclusion

Highlight whole rows with a FormulaRule applied across the full width of the table and written for its first row, using a column-absolute, row-relative reference such as $D2. Add one rule per colour, use stopIfTrue where conditions overlap, combine conditions with AND and OR, and extend the range beyond the current data so appended rows are covered. The same rule works in xlsxwriter with a leading = in the criteria. Because the rule is evaluated by Excel, the colours keep following the data after people edit, sort and add rows.

Frequently asked questions

Why does my rule colour only the Status cell? The rule's range decides which cells can be coloured. Apply it to every column of the row, such as A2:E500, while the formula still tests column D.

How do I highlight rows that contain a word anywhere in a column? Use a formula such as ISNUMBER(SEARCH("urgent", $B2)), which is true when the text appears anywhere in that cell, ignoring case.

Can I highlight rows based on a value in another sheet? Yes. Modern Excel accepts references to other sheets in conditional formatting formulas, or you can define a named range on the other sheet and refer to the name.

Will sorting the data break the colours? No. The rule is evaluated per row from the current values, so after sorting each row is coloured according to its own Status cell.

More on conditional formatting

More on conditional formatting →