Highlight Invalid Cells in Excel with Python

A list of validation failures in a log file gets read by you. The same failures painted onto the cells that caused them get fixed by the person who typed them. This guide, part of Validating Excel Data with Python, turns a list of issues into a workbook that explains itself: filled cells, comments carrying the reason, a legend, and an issues sheet.

Prerequisites

pip install pandas openpyxl

The examples assume a list of issue dictionaries in the shape produced by the validation layer: {"row": 4, "column": "Quantity", "value": -7, "problem": "must be greater than zero"}.

Write the data, then mark it up

Formatting must come after the data, because pandas replaces the whole sheet when it writes:

import pandas as pd

df = pd.DataFrame([
    {"Order_ID": 2001, "Region": "North", "Quantity": 4, "Unit_Price": 19.99},
    {"Order_ID": 2002, "Region": "Souht", "Quantity": 2, "Unit_Price": 49.50},
    {"Order_ID": 2003, "Region": "West", "Quantity": -7, "Unit_Price": None},
])

issues = [
    {"row": 3, "column": "Region", "value": "Souht", "problem": "region is not on the approved list"},
    {"row": 4, "column": "Quantity", "value": -7, "problem": "quantity must be greater than zero"},
    {"row": 4, "column": "Unit_Price", "value": None, "problem": "unit price is missing"},
]

with pd.ExcelWriter("marked.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    pd.DataFrame(issues).to_excel(writer, sheet_name="Issues", index=False)

The row values are spreadsheet rows: row 1 is the header, so the first data row is 2. Getting this off by one is the single most common bug in this whole exercise, and it is invisible until someone points at a perfectly good cell and asks what is wrong with it.

Fill the offending cells

Map each issue's column name to a column letter, then apply a fill:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.utils import get_column_letter

BAD = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
BAD_FONT = Font(color="9C0006")

wb = load_workbook("marked.xlsx")
ws = wb["Orders"]

headers = {cell.value: cell.column for cell in ws[1]}

for issue in issues:
    col = headers.get(issue["column"])
    if col is None:
        continue                      # column not on this sheet — skip rather than crash
    cell = ws.cell(row=issue["row"], column=col)
    cell.fill = BAD
    cell.font = BAD_FONT

wb.save("marked.xlsx")
print(f"highlighted {len(issues)} cell(s)")

FFC7CE with 9C0006 text is the colour pair Excel itself uses for its "Bad" cell style, so the result looks native rather than like something a script did. Building headers from the sheet's own first row means the mapping is correct even when the column order differs from your DataFrame's.

Attach the reason to the cell

A red cell says something is wrong; a comment says what:

from openpyxl import load_workbook
from openpyxl.comments import Comment

wb = load_workbook("marked.xlsx")
ws = wb["Orders"]
headers = {cell.value: cell.column for cell in ws[1]}

by_cell = {}
for issue in issues:
    col = headers.get(issue["column"])
    if col:
        by_cell.setdefault((issue["row"], col), []).append(issue["problem"])

for (row, col), problems in by_cell.items():
    cell = ws.cell(row=row, column=col)
    text = "\n".join(f"• {p}" for p in problems)
    comment = Comment(text, "validation")
    comment.width, comment.height = 260, 26 + 18 * len(problems)
    cell.comment = comment

wb.save("marked.xlsx")

Grouping by coordinate first matters because one cell can break several rules at once, and assigning a second Comment to the same cell replaces the first. Sizing the comment box to the number of lines stops the text being clipped, which is otherwise the difference between a reader seeing the reason and seeing a truncated fragment.

Add a legend and an active sheet

Readers should not have to guess what the colour means, and the workbook should open on the sheet that explains itself:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font

wb = load_workbook("marked.xlsx")
ws = wb["Orders"]

legend_row = ws.max_row + 2
ws.cell(row=legend_row, column=1).fill = PatternFill("solid", start_color="FFC7CE")
ws.cell(row=legend_row, column=2, value="Failed validation — hover the cell for the reason")
ws.cell(row=legend_row, column=2).font = Font(italic=True, color="595959")

summary_row = legend_row + 1
ws.cell(row=summary_row, column=2,
        value=f"{len(issues)} issue(s) found in {len({i['row'] for i in issues})} row(s)")

ws.freeze_panes = "A2"
wb.active = wb.index(wb["Issues"])       # open on the issues list
wb.save("marked.xlsx")

Freezing the header keeps the column names visible while a reader scrolls to a highlighted row far down the sheet — a small thing that makes a 3,000-row file usable.

Clear last run's marks first

Highlights are ordinary formatting: they persist. A second run over a corrected file leaves yesterday's red cells in place unless you clear them:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

NO_FILL = PatternFill(fill_type=None)

def clear_highlights(ws, colours={"FFC7CE"}):
    cleared = 0
    for row in ws.iter_rows():
        for cell in row:
            fill = cell.fill
            if fill is not None and fill.fill_type == "solid":
                rgb = getattr(fill.start_color, "rgb", "") or ""
                if rgb[-6:].upper() in colours:
                    cell.fill = NO_FILL
                    cell.comment = None
                    cleared += 1
    return cleared

wb = load_workbook("marked.xlsx")
print("cleared", clear_highlights(wb["Orders"]), "old highlight(s)")
wb.save("marked.xlsx")

Matching on the last six characters of the colour handles both FFC7CE and the 00FFC7CE form openpyxl sometimes returns. Clearing only your colour leaves the reader's own formatting alone, which matters when the same file is passed back and forth.

Static fill or conditional formatting?

Conditional formatting has a real advantage — it updates the moment someone corrects the cell — but it can only express rules Excel can evaluate. A "region is not in the master list" rule that depends on a database lookup cannot live in the workbook, so the decision usually makes itself.

Colour by severity, not just by failure

One red for everything is fine on a short list. Once a file routinely carries dozens of issues, a second colour separates "this must be fixed" from "check this":

Both pairs are Excel's own built-in styles — "Bad" and "Neutral" — so the workbook keeps looking like a spreadsheet rather than a script's output. Carry the severity on the issue record itself and pick the fill from it, so the colour and the message can never disagree.

Common pitfalls and gotchas

Performance and scale notes

Fills and comments are per-cell objects, so marking tens of thousands of cells makes the file noticeably larger and slower to open — and a sheet where a third of the cells are red communicates nothing anyway. Cap the number of highlighted cells (the first 500, say), state the cap in the summary line, and let the issues sheet carry the full list. Clearing highlights walks every cell, so on a large sheet restrict the scan to the used range or to the columns you know your rules touch.

Conclusion

Highlighting is the step that turns validation from a log into a conversation. Write the data first, map issue columns to letters from the sheet's own header row, fill with Excel's native bad-cell colours, attach one comment per cell carrying every reason that applies, and add a legend so nothing needs explaining. Clear your own marks at the start of each run, cap the number of highlights on huge files, and keep the full list on an issues sheet.

Frequently asked questions

Should I highlight with a static fill or conditional formatting? Use a static fill when Python decided the cell is wrong — the reason lives in your code, not in a formula. Use conditional formatting when the rule can be expressed in Excel and should keep reacting as the reader edits.

Why did my highlight disappear after writing the DataFrame? pandas rewrote the sheet. Write the data first, then apply the formatting to the finished sheet.

How do I show why a cell is wrong? Attach a comment to the cell with the rule that failed, and keep the same text in an issues sheet so the reasons can be filtered and counted.

Do highlighted cells survive a round trip through Excel? Yes. A PatternFill is ordinary cell formatting, so it stays until someone clears it — which is why a run should clear its previous highlights before applying new ones.

Related

Up to the parent guide:

• Validating Excel Data with Python — where the issue records come from.

Related guides:

• Check Excel Data Types with pandas — producing the failures this page paints.
• Applying Conditional Formatting with openpyxl — rules that keep reacting as the reader edits.
• Styling Excel Cells with openpyxl — fills, fonts and borders in depth.
• Add Dropdown Data Validation to Excel with openpyxl — preventing the errors you are highlighting.