Highlight Cells with Formula Errors Using Conditional Formatting

Make #DIV/0!, #N/A, #VALUE! and #REF! results stand out in a generated workbook with openpyxl conditional formatting: a rule that colours any error, a separate softer rule for expected #N/A lookups, a whole-row highlight driven by an error in one column, and a Python check that lists every error cell after recalculation so problems are found before the report is sent.

Conditional formatting Formulas in workbooks

A workbook full of formulas will eventually show an error: a division by a zero quantity gives #DIV/0!, a lookup for a code that is not in the table gives #N/A, text where a number should be gives #VALUE!, and a deleted column leaves #REF! behind. In a long sheet these are easy to miss, and a total that includes one becomes an error itself, so the problem spreads. Conditional formatting can make every error cell stand out the moment it appears — including errors that appear later, when someone edits the workbook — and a short Python check can list them before the file is sent. This page adds a rule that colours any error, a gentler rule for #N/A results that are expected, a rule that highlights a whole row when one of its formulas fails, and a scan that finds every error after recalculation. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

Bash

pip install openpyxl pillow

LibreOffice (soffice) recalculates the workbook so the formulas produce real errors, and pdftoppm renders it so the highlighting can be checked. The example is an order sheet with a price lookup and a margin calculation — the two places errors usually come from.

Step 1 — A sheet that produces errors

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"
prices = wb.create_sheet("Prices")
prices.append(["SKU", "Price"])
for sku, price in [("B-100", 0.45), ("N-200", 0.12), ("W-300", 0.05)]:
    prices.append([sku, price])

ws.append(["Order", "SKU", "Qty", "Revenue", "Cost", "Unit price", "Margin %"])
orders = [("SO-1", "B-100", 120, 54.0, 25.2), ("SO-2", "N-200", 0, 0, 0), ("SO-3", "X-999", 10, 30.0, 12.0),
          ("SO-4", "W-300", 300, "TBC", 6.0), ("SO-5", "B-100", 80, 36.0, 16.8)]
for r, (order, sku, qty, revenue, cost) in enumerate(orders, start=2):
    ws.append([order, sku, qty, revenue, cost,
               f"=VLOOKUP(B{r},Prices!$A$2:$B$4,2,FALSE)",
               f"=(D{r}-E{r})/D{r}"])
    ws[f"G{r}"].number_format = "0.0%"
ws["D8"], ws["F8"] = "=SUM(D2:D6)", "Total check"
ws["G8"] = "=SUM(G2:G6)"
for col, width in zip("ABCDEFG", (8, 8, 7, 10, 8, 11, 10)):
    ws.column_dimensions[col].width = width

Order SO-2 has zero revenue, so its margin divides by zero; SO-3 uses a SKU missing from the price list, so the lookup returns #N/A; SO-4 has "TBC" typed where its revenue should be. The sum in G8 then inherits an error from the margins above it — the way errors spread through a sheet. In a real report the data would come from pandas or a database, but the shapes are the same: zero denominators, codes missing from reference tables and text that slipped into numeric columns account for most formula errors in generated workbooks.

Step 2 — Colour errors, with #N/A treated separately

Python

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

ERROR_FILL = PatternFill("solid", fgColor="FFC7CE")
NA_FILL = PatternFill("solid", fgColor="FFF2CC")
area = "D2:G8"
ws.conditional_formatting.add(area, FormulaRule(formula=["ISNA(D2)"], fill=NA_FILL, font=Font(color="7F6000"), stopIfTrue=True))
ws.conditional_formatting.add(area, FormulaRule(formula=["ISERROR(D2)"], fill=ERROR_FILL, font=Font(color="9C0006", bold=True)))
wb.save("orders_errors.xlsx")
print([(r.formula, r.priority) for cf in ws.conditional_formatting for r in cf.rules])

Each rule is a formula written for the top-left cell of the range, D2; Excel adjusts the relative reference for every other cell, exactly as when copying a formula. ISERROR is true for every error type; ISNA only for #N/A. A missing price is often an expected situation — a new product not yet priced — so it gets a pale yellow warning rather than the red used for genuine faults, and stopIfTrue stops the red rule from being applied on top of it. The order in which rules are added sets their priority, as explained in Control Conditional Formatting Rule Order and Stop If True. openpyxl also offers a dedicated Rule(type="containsErrors"), which Excel lists as "Format only cells that contain errors"; the formula form shown here does the same and makes the #N/A distinction possible.

Step 3 — Highlight the whole row

A red margin cell is easy to see; knowing which order it belongs to is easier still if the whole row is marked. A rule over the full row width with an absolute column reference does that:

Python

row_rule = FormulaRule(formula=["OR(ISERROR($F2),ISERROR($G2))"], fill=PatternFill("solid", fgColor="FBE9EB"))
ws.conditional_formatting.add("A2:C6", row_rule)
wb.save("orders_errors.xlsx")

$F2 and $G2 lock the columns but leave the row relative, so each row checks its own unit price and margin. The rule is applied only to the descriptive columns A to C, which receive a light tint, while the error cells themselves keep the stronger colours from Step 2. Whole-row highlighting in general is covered in Highlight an Entire Row Based on a Cell Value with openpyxl.

Step 4 — Recalculate, check the colours, and list the errors

Python

import subprocess
from openpyxl import load_workbook
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "orders_errors.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/orders_errors.xlsx", data_only=True)["Orders"]
ERRORS = ("#DIV/0!", "#N/A", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#NULL!")
found = [(c.coordinate, c.value) for row in calc.iter_rows() for c in row if isinstance(c.value, str) and c.value in ERRORS]
for coord, err in found:
    print(f"{coord:<4} {err:<8} row {coord[1:]}: {calc[f'A{coord[1:]}'].value or 'total'}")

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "orders_errors.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "80", "-f", "1", "-l", "1", "orders_errors.pdf", "errors"], check=True)
colours = {c for n, c in Image.open("errors-1.png").convert("RGB").getcolors(maxcolors=10**6) if n > 50}
for name, hexval in [("red error fill", "FFC7CE"), ("yellow #N/A fill", "FFF2CC"), ("row tint", "FBE9EB")]:
    print(f"{name:<17} {'rendered' if tuple(int(hexval[i:i + 2], 16) for i in (0, 2, 4)) in colours else 'missing'}")

After recalculation, openpyxl's data_only=True view returns error results as their text, #DIV/0! and so on, which makes them easy to find with a scan. The list shows exactly where the problems are: the division by zero for SO-2, the missing price for SO-3 and the #VALUE! from SO-4's text revenue, plus the margin total in G8 that inherited an error. The revenue total in D8 does not show an error: SUM silently skips the text, so the total is simply too low — a reminder that some data problems produce no error at all and need a check of their own. All three fills appear in the rendered page. A job that builds reports can run this scan and refuse to send a workbook with unexpected errors, as described in Validate an Excel Report Before Sending It.

When errors are acceptable

Not every error in a finished report is a fault. A #N/A in a lookup column can be the honest answer — "no price agreed yet" — and replacing it with zero would be worse, because zero is a valid price. Decide, column by column, which errors are allowed: lookups against reference data that is known to be incomplete may show #N/A, while divisions, type errors and broken references never should. The scan in Step 4 can then compare what it finds against that list — allow #N/A in column F, fail on anything else — so the check is strict where it matters and quiet where an error carries information. Recording the decision in the workbook, on a notes sheet, tells readers what the yellow cells mean.

Common pitfalls

A rule written for the wrong anchor cell. The formula refers to the range's top-left cell.
Treating every #N/A as a fault. Give expected lookup misses their own, softer rule.
Hiding errors with white text. It hides the total that inherited them too; fix or flag them.
Checking an uncalculated file. Errors only exist after Excel or LibreOffice has calculated it.
Forgetting totals. Errors spread upwards; check summary cells as well.

Conclusion

Add a FormulaRule with ISERROR to colour every error cell, preceded by an ISNA rule with stopIfTrue when missing lookups deserve a gentler warning, and a row rule with locked columns to mark the records that contain them. Because the rules live in the workbook, errors that appear later are highlighted too. Before sending, recalculate and scan the data_only values for error strings, so every error is accounted for.

Frequently asked questions

How do I highlight error cells in Excel with Python? Add a conditional formatting rule with openpyxl's FormulaRule and the formula ISERROR on the top-left cell of the range, with a fill and font for the highlight.

How can I highlight #N/A differently from other errors? Add an ISNA rule before the ISERROR rule and set stopIfTrue on it, so #N/A cells get their own format and the general error rule is skipped.

How do I find all error cells in a workbook with Python? Recalculate the workbook, load it with openpyxl using data_only=True, and look for cells whose values are error strings such as #DIV/0! or #N/A.

Should I use IFERROR to hide errors instead? Only where an error has an agreed meaning; hiding them everywhere also hides genuine data problems.

Up one level: Applying Conditional Formatting with openpyxl — rules from Python.
Find Formula Errors in a Workbook with Python — finding errors without formatting.
IFERROR, IFNA, ISNA and ISNUMBER Equivalents in pandas — the same logic in pandas.
Highlight Blank Cells with openpyxl — another data-quality rule.

More on conditional formatting