Audit Every Formula in a Workbook with Python

Spreadsheets that have been in use for a few years accumulate formula debt: a total that multiplies by a hard-coded 1.2 "just for this quarter", one row in a column of identical formulas that points at the wrong cell, a number typed over a formula to make the figure match, a link to a colleague's workbook on a drive that no longer exists. Excel shows some of these one cell at a time. A Python audit shows all of them at once, for every sheet, in a table you can sort, filter and keep. This page, part of Working with Excel Formulas in Python, builds that audit step by step.

Prerequisites

pip install openpyxl pandas

The examples use orders.xlsx: an Orders sheet whose total column has the three problems in the diagram, a lookup column into a Rates sheet, and a Summary sheet with a cross-sheet total, a link to an external workbook and a tax formula with a hard-coded rate.

Step 1 — Build the inventory

Load the workbook normally — not with data_only=True, which would replace formulas with their cached results — and collect every cell whose data_type is 'f':

import pandas as pd
from openpyxl import load_workbook

def formula_inventory(path):
    wb = load_workbook(path)
    rows = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if cell.data_type == "f":
                    rows.append({"sheet": ws.title, "cell": cell.coordinate,
                                 "row": cell.row, "col": cell.column_letter, "formula": cell.value})
    return pd.DataFrame(rows)

inv = formula_inventory("orders.xlsx")
print(len(inv), "formulas")
print(inv.head(4).to_string(index=False))

That table alone answers the first audit questions — how many formulas, on which sheets — and it is the input to everything that follows.

Step 2 — Take each formula apart

openpyxl's formula tokenizer splits a formula into functions, operands and operators, which is far more reliable than regular expressions once formulas nest. From the tokens, pull out the functions used, the sheets referenced, any external workbook links, and numeric constants:

from openpyxl.formula.tokenizer import Tokenizer, Token

def describe(formula):
    funcs, sheets, external, numbers = set(), set(), False, []
    for tok in Tokenizer(formula).items:
        if tok.type == Token.FUNC and tok.subtype == Token.OPEN:
            funcs.add(tok.value.rstrip("("))
        elif tok.type == Token.OPERAND and tok.subtype == Token.RANGE and "!" in tok.value:
            ref_sheet = tok.value.rsplit("!", 1)[0].strip("'")
            if ref_sheet.startswith("["):
                external = True
            sheets.add(ref_sheet)
        elif tok.type == Token.OPERAND and tok.subtype == Token.NUMBER:
            numbers.append(float(tok.value))
    return pd.Series({"functions": ",".join(sorted(funcs)), "refs_sheets": ",".join(sorted(sheets)),
                      "external": external, "constants": numbers})

inv = inv.join(inv["formula"].apply(describe))
print(inv[["sheet", "cell", "functions", "refs_sheets", "external", "constants"]].to_string(index=False))

Now each formula has a profile. Sorting or filtering the table answers questions that are tedious in Excel: which sheets does Summary depend on, where is VLOOKUP used, which cells link outside the file.

Step 3 — Flag hard-coded numbers

Numbers inside formulas are sometimes harmless — the 2 in a VLOOKUP is a column index, and 0 and 1 appear in comparisons and switches — and sometimes a hidden assumption: a tax rate, a markup, a threshold. Filter out the harmless ones by value and by position, and review the rest:

LOOKUPS = {"VLOOKUP", "HLOOKUP", "INDEX", "MATCH", "OFFSET"}

def suspicious_constants(row):
    if row["functions"] and set(row["functions"].split(",")) & LOOKUPS:
        return [n for n in row["constants"] if n not in (0, 1) and not float(n).is_integer()]
    return [n for n in row["constants"] if n not in (0, 1)]

inv["hard_coded"] = inv.apply(suspicious_constants, axis=1)
print(inv.loc[inv["hard_coded"].str.len() > 0, ["sheet", "cell", "formula", "hard_coded"]].to_string(index=False))

The audit reports 1.2 in Orders!D4 and 0.2 in Summary!B3. The fix for both is the same: move the number into a labelled input cell — a "Tax rate" cell on an Assumptions sheet — and refer to it, so the assumption is visible, documented and changed in one place.

Step 4 — Find the inconsistent formula in a column

A column of per-row formulas should all follow one pattern. To compare them, translate each formula back to the position of one reference row with openpyxl's Translator; formulas that follow the pattern become identical text:

from openpyxl.formula.translate import Translator

def normalised(row, anchor_row=2):
    target = f"{row['col']}{anchor_row}"
    return Translator(row["formula"], origin=row["cell"]).translate_formula(target)

orders = inv[inv["sheet"] == "Orders"].copy()
orders["pattern"] = orders.apply(normalised, axis=1)
for col, group in orders.groupby("col"):
    usual = group["pattern"].mode()[0]
    odd = group[group["pattern"] != usual]
    print(f"column {col}: usual {usual}; odd ones: {odd['cell'].tolist()}")

Column D's usual pattern is =B2*C2. D4 normalises to =B2*C2*1.2 and D6 to =B2*C3 — the second one points one row down, the classic copy-paste slip that gives a plausible-looking wrong number. Column E is entirely consistent. This is the same idea as Excel's "inconsistent formula" warning, applied to every column at once.

Step 5 — Find values typed over formulas

The third problem in the Orders sheet is not in the inventory at all: D5 holds the number 3, typed over the formula that belonged there. Find cells that hold constants inside a column that is otherwise formulas:

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
for col, group in orders.groupby("col"):
    rows = range(group["row"].min(), group["row"].max() + 1)
    typed = [f"{col}{r}" for r in rows
             if ws[f"{col}{r}"].data_type != "f" and ws[f"{col}{r}"].value is not None]
    print(f"column {col}: constants among formulas: {typed}")

A typed-over formula is the most dangerous item on the list, because it is correct on the day it is typed and wrong every day after — the figure no longer follows its inputs. Convert Excel Formulas to Values with Python covers the deliberate version of this change, when values are what you want.

Step 6 — Write the audit into the workbook

An audit is most useful where the workbook's owner will see it. Write the inventory and findings to a new sheet — in a copy, not the original, so the audit never changes the file it describes:

inv["inconsistent"] = inv["cell"].isin(["D4", "D6"]) & (inv["sheet"] == "Orders")
report = inv[["sheet", "cell", "formula", "functions", "refs_sheets", "external", "hard_coded", "inconsistent"]].copy()
report["hard_coded"] = report["hard_coded"].apply(lambda xs: ", ".join(str(x) for x in xs))

with pd.ExcelWriter("orders_audit.xlsx") as writer:
    report.to_excel(writer, sheet_name="Formula audit", index=False)
print(pd.read_excel("orders_audit.xlsx").shape)

In a real job, compute the inconsistent flag from Step 4 rather than listing cells by hand; it is spelled out here to keep the example short. Rerun the audit after the owner's fixes and the problem rows disappear from the table — a simple way to show the work is done.

Common pitfalls

• Loading with data_only=True. You get results, not formulas, and the audit sees nothing.
• Regular expressions on formula text. They break on nested functions and quoted sheet names; use the tokenizer.
• Treating every constant as a problem. Column indexes, zeros and ones are usually fine; review the rest.
• Auditing only formula cells. Typed values inside formula columns are invisible to an inventory of formulas.
• Array and shared formulas. Cells that hold part of an array formula can report a formula object rather than a string; convert with str() or read the formula's text before tokenising.

Performance notes

Tokenising is the slowest step, at tens of thousands of formulas per second, which covers most business workbooks in a few seconds. For very large models, tokenise each distinct normalised pattern once — a column of 50,000 identical formulas has one pattern — and join the results back to the cells.

Conclusion

An inventory of every formula, built from cells whose data_type is 'f', becomes a full audit once each formula is tokenised: functions used, sheets and external files depended on, and numbers hard-coded inside. Normalising formulas to one anchor row with Translator exposes the odd one out in a column, and checking formula columns for constants finds values typed over formulas. Write the findings to their own sheet in a copy, fix at the source, and rerun until the table is empty.

Frequently asked questions

How do I list every formula in a workbook? Load it with openpyxl without data_only, visit every cell on every sheet, and keep those whose data_type is "f" — their value is the formula text. Collect sheet, cell and formula into a DataFrame for analysis.

How can I tell if one formula in a column differs from the rest? Translate every formula to the position of the first row with openpyxl's Translator. Formulas that follow the same pattern become identical text; the ones that do not stand out when you compare them with the most common version.

What counts as a hard-coded number? Any numeric constant inside a formula, such as the 1.2 in =B4C41.2. Some are fine — a column index in VLOOKUP, a zero in a comparison — but rates, factors and thresholds belong in labelled input cells where they can be seen and changed.

Can this find external workbook links? Yes. References to other files appear in the formula text with a bracketed index such as 1Budget!B4. The tokenizer reports them as range operands, so the audit can flag any reference containing a bracket.

Related

• Up one level: Working with Excel Formulas in Python — writing, reading and recalculating formulas.
• Find #REF!, #DIV/0! and Other Formula Errors in a Workbook — formulas that already fail.
• Find External Links and Broken References in a Workbook with Python — the external links in depth.
• Copy a Formula Down a Column in openpyxl — writing consistent columns in the first place.