Find #REF!, #DIV/0! and Other Formula Errors in a Workbook

A workbook with a dozen broken formulas still opens, still has numbers in most cells, and reads into pandas without a complaint. That last part is the problem: pandas' Excel reader converts every error value — #DIV/0!, #N/A, #REF! — into NaN, so a lookup that failed for half the rows looks exactly like data that was never filled in. A report job that reads, aggregates and emails such a file will send wrong totals with a straight face. This page, part of Working with Excel Formulas in Python, shows how to find every error in a workbook with openpyxl, including the ones that have not been calculated yet.

Prerequisites

pip install openpyxl pandas
# LibreOffice, for recalculating files written by Python (Step 3)

The examples use report.xlsx: a Sales sheet with a price formula that divides by a zero quantity and a lookup against a Targets sheet that is missing one region, a text-plus-number formula in H2, and a Summary sheet with one formula whose reference has been lost. The setup recalculated it with LibreOffice, so it contains cached results just like a file saved by Excel.

Step 1 — See pandas hide the errors

import pandas as pd

sales = pd.read_excel("report.xlsx", sheet_name="Sales")
print(sales[["region", "price", "vs target"]])
print(sales["price"].isna().sum(), "missing prices")

The South row shows NaN for both the price and the target comparison. The DataFrame gives no hint that one of those is a division by zero and the other a failed lookup, and a sales["price"].mean() would silently skip the row. pandas does this on purpose — its reader maps error cells to NaN — so no reading option brings the error codes back. You need openpyxl.

Step 2 — Scan cached values for error cells

When a workbook is saved by a spreadsheet application, each formula cell stores its last result alongside the formula. Open the file with data_only=True and error results appear as strings like '#DIV/0!', with the cell's data_type set to 'e'. Open it a second time without data_only to get the formulas that produced them:

from openpyxl import load_workbook

def find_errors(path):
    values = load_workbook(path, data_only=True)
    formulas = load_workbook(path)
    found = []
    for ws in values.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if cell.data_type == "e":
                    found.append({"sheet": ws.title, "cell": cell.coordinate, "error": cell.value,
                                  "formula": formulas[ws.title][cell.coordinate].value})
    return pd.DataFrame(found, columns=["sheet", "cell", "error", "formula"])

errors = find_errors("report.xlsx")
print(errors.to_string(index=False))

Each row names the sheet, the cell, the error and the formula behind it, which is usually enough to see the cause at a glance: =C3/B3 with a zero in B3, a VLOOKUP for a region the Targets sheet does not have, text added to a number. Checking data_type rather than matching strings means a cell that genuinely contains the text "#N/A" — typed by a person as a placeholder — is not reported as an error.

Step 3 — Recalculate files that have no cached values

Workbooks written by openpyxl or pandas contain formulas but no results — nothing has calculated them yet. Opened with data_only=True, every formula cell reads as None, and the scan finds nothing:

print(load_workbook("report_raw.xlsx", data_only=True)["Sales"]["D3"].value)
# None — never calculated

Recalculate before scanning. On a server without Excel, LibreOffice in headless mode opens the file, calculates it and saves a copy with cached results:

import subprocess
from pathlib import Path

def recalculated(path, outdir="recalc"):
    subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", outdir, str(path)],
                   check=True, capture_output=True, timeout=120)
    return Path(outdir) / Path(path).name

print(find_errors(recalculated("report_raw.xlsx"))[["sheet", "cell", "error"]].to_string(index=False))

Spreadsheet applications do not all report every failure with the same code — LibreOffice, for instance, exports some errors that Excel would show as #NUM! as #VALUE! — so treat the code as a hint and the formula as the evidence. Recalculate Excel Formulas Without Excel in Python compares the ways to get cached values.

Step 4 — Find #REF! in formula text

One class of error is visible without any calculation. When a sheet, row or column that a formula refers to is deleted, Excel rewrites the reference inside the formula as #REF! — =SUM(Old!B2:B9) becomes =SUM(#REF!). The broken reference is then part of the formula text in the saved file, so a plain text search finds it:

def find_broken_refs(path):
    wb = load_workbook(path)
    hits = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("=") and "#REF!" in cell.value.upper():
                    hits.append(f"{ws.title}!{cell.coordinate}: {cell.value}")
    return hits

print(find_broken_refs("report_raw.xlsx"))

This check is cheap and works on any file, recalculated or not, which makes it a good first gate for templates and workbooks received from other people. The case-insensitive comparison matters: some applications rewrite the token in lower case when they save. References to other workbooks are a related source of silent breakage; Find External Links and Broken References in a Workbook with Python covers them.

Step 5 — Summarise and fail the job

For a report job, the useful output is a short summary in the log and a hard stop when anything is wrong:

def assert_no_errors(path):
    errors = find_errors(path)
    broken = find_broken_refs(path)
    if errors.empty and not broken:
        return
    summary = errors.groupby(["sheet", "error"]).size().rename("cells").reset_index()
    message = [f"{len(errors)} error cell(s) and {len(broken)} broken reference(s) in {path}"]
    message += [f"  {r.sheet}: {r.cells} x {r.error}" for r in summary.itertuples()]
    message += [f"  {b}" for b in broken[:10]]
    raise ValueError("\n".join(message))

try:
    assert_no_errors("report.xlsx")
except ValueError as exc:
    print(exc)

Counting by sheet and error type keeps the message readable when a single bad lookup has failed in five hundred rows. Put the check at the end of the job, after the workbook is written and recalculated and before it is emailed or published; Validate an Excel Report Before Sending It shows it alongside the other pre-send checks.

Decide which errors are allowed

Not every error is a bug. A price per unit is undefined when no units were sold, and a report may legitimately show that. The honest way to allow it is in the formula, where readers see the decision: =IF(B3=0,"",C3/B3) or =IFERROR(C3/B3,""). Avoid wrapping everything in IFERROR to make the scan pass — it converts a missing lookup key, a broken reference or text in a number column into a blank, which is precisely the silent failure this page set out to catch. Allow specific, expected cases; let everything else fail. Fix #NAME? in Formulas Written from Python covers the most common cause of #NAME? in generated workbooks.

Common pitfalls

• Scanning a workbook that was never calculated. Every formula reads as None; recalculate first.
• Matching the string "#N/A". Use data_type == "e" so typed placeholders are not reported.
• Trusting the DataFrame. NaN after read_excel may be an error, not a gap.
• Hiding errors with blanket IFERROR. It makes scans pass and reports wrong.

Performance notes

The scan loads the workbook twice and visits every cell, which takes a few seconds for sheets with hundreds of thousands of cells. For large files, use read_only=True for both loads and iterate the same sheets in step, or restrict the scan to the sheets and columns that contain formulas. LibreOffice recalculation is usually the slowest part — several seconds per file — so run it once and scan the result, rather than recalculating repeatedly.

Conclusion

pandas turns error cells into NaN, so finding broken formulas needs openpyxl: load with data_only=True, keep cells whose data_type is 'e', and pair each with its formula from a second load. Recalculate Python-written files first so there are results to scan, search formula text for #REF! to catch deleted references without calculating, and fail the job with a per-sheet summary. Allow only the errors you have decided are legitimate, in the formula itself.

Frequently asked questions

Why does pandas show NaN where Excel shows pandas' openpyxl reader converts every error cell to NaN deliberately. That is convenient for analysis but makes a failed formula indistinguishable from an empty cell. Scan with openpyxl when you need to know about errors.

Why does openpyxl return None instead of the error? The file has no cached results. Workbooks written by openpyxl or pandas contain formulas but no calculated values until a spreadsheet application recalculates and saves them. Recalculate first, for example with LibreOffice in headless mode.

Can I find Often, yes. When a referenced sheet, row or column is deleted, Excel rewrites the reference in the formula text as #REF!, so searching formula strings finds those cells in any saved file, calculated or not.

Should I wrap formulas in IFERROR to get rid of errors? Only where an error is an expected case, such as dividing by a zero count. Wrapping everything hides real problems like missing lookup keys and broken references. Fix the cause and keep IFERROR for the cases you have decided to allow.

Related

• Up one level: Working with Excel Formulas in Python — writing and reading formulas.
• Read Formula Results with openpyxl data_only — how cached values work.
• Audit Every Formula in a Workbook with Python — an inventory of every formula, erroring or not.
• Highlight Invalid Cells in Excel with Python — marking problems for readers.