Map Formula Dependencies in a Workbook with Python

Trace precedents and dependents across every sheet of a workbook without Excel: parse formulas with openpyxl's Tokenizer, resolve cross-sheet references and named ranges, build a dependency graph, list what feeds a result and what a changed input affects, find unused inputs and circular references, and write the map to a sheet.

Formulas in workbooks openpyxl

Excel's Trace Precedents arrows answer "what feeds this cell?" one cell at a time, one sheet at a time, by clicking. That is fine for checking a single number and hopeless for the questions that come up when a model is handed over or audited: which inputs does the headline figure actually depend on, which inputs are never used, what breaks if this assumption changes, is there a circular reference hiding on some sheet? Every formula in a workbook is stored as text, and openpyxl ships a tokenizer that splits that text into references, functions and operators. From there a dependency graph is a dictionary, and Python's standard library can sort it and find cycles. This page is part of Working with Excel Formulas in Python.

Prerequisites

Bash

pip install openpyxl

model.xlsx is a small three-sheet model: assumptions on Inputs (one of them, the tax rate, reached through the name TaxRate), calculations on Calc, and results on Summary. Two cells on Calc refer to each other, and one input is never used — the kind of thing an audit should find.

Step 1 — Pull the references out of each formula

Load the workbook normally (not data_only, which would give the cached values instead of the formulas). For each formula, Tokenizer(formula).items returns tokens; references are the OPERAND tokens with subtype RANGE:

Python

from openpyxl import load_workbook
from openpyxl.formula import Tokenizer

wb = load_workbook("model.xlsx")
formulas = {}
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            if cell.data_type == "f":
                formulas[(ws.title, cell.coordinate)] = cell.value

for key, f in list(formulas.items())[:4]:
    refs = [t.value for t in Tokenizer(f).items if t.type == "OPERAND" and t.subtype == "RANGE"]
    print(key, f, "->", refs)
print(len(formulas), "formulas")

The tokenizer handles the parts that trip up a regular expression: text in quotes that looks like a reference ("see A1"), sheet names with spaces in single quotes, and function names next to parentheses. But "RANGE" to the tokenizer means anything that is not a literal — a cell, a range, a defined name such as TaxRate, or a table reference like Sales[Amount] — so the next step sorts them out.

Step 2 — Resolve each token to sheet-qualified cells

A bare B2 in a formula on Calc means Calc!B2; Inputs!B3 names its sheet; TaxRate is a defined name pointing somewhere else; B2:B4 is three cells. Normalise everything to (sheet, cell) pairs:

Python

import re
from openpyxl.utils import range_boundaries, get_column_letter

REF = re.compile(r"^(?:(?P<sheet>'[^']+'|[^'!]+)!)?(?P<rng>\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?)$")

def cells_in(sheet, rng, limit=10_000):
    min_col, min_row, max_col, max_row = range_boundaries(rng.replace("$", ""))
    if (max_col - min_col + 1) * (max_row - min_row + 1) > limit:
        return [(sheet, rng)]                      # keep huge ranges as one node
    return [(sheet, f"{get_column_letter(c)}{r}")
            for r in range(min_row, max_row + 1) for c in range(min_col, max_col + 1)]

def resolve(token, home):
    m = REF.match(token)
    if m:
        sheet = (m.group("sheet") or home).strip("'")
        return cells_in(sheet, m.group("rng"))
    if token in wb.defined_names:
        out = []
        for sheet, ref in wb.defined_names[token].destinations:
            out += cells_in(sheet, ref)
        return out
    return [("?", token)]                          # table refs, external links, errors

print(resolve("B2", "Calc"), resolve("Inputs!B3", "Calc"), resolve("TaxRate", "Calc"))
print(resolve("Calc!B2:B4", "Summary"))

Ranges are expanded to their cells so that SUM(Calc!B2:B4) counts as depending on each of the three, with a limit so that a reference like A1:Z100000 stays one node instead of 2.6 million. Anything the resolver does not understand is kept with a ? sheet rather than dropped: table references, references into other workbooks ([1]Sheet1!A1) and #REF! all deserve a look during an audit. Whole-column references such as A:A also land there; add a rule for them if your models use them.

Step 3 — Build the graph and trace in both directions

Map each formula cell to the set of cells it reads — its precedents — and invert that for dependents:

Python

from collections import defaultdict

precedents = {}
for (sheet, coord), f in formulas.items():
    refs = [t.value for t in Tokenizer(f).items if t.type == "OPERAND" and t.subtype == "RANGE"]
    precedents[(sheet, coord)] = {c for r in refs for c in resolve(r, sheet)}

dependents = defaultdict(set)
for cell, pre in precedents.items():
    for p in pre:
        dependents[p].add(cell)

def walk(start, edges):
    seen, stack = set(), [start]
    while stack:
        for nxt in edges.get(stack.pop(), ()):
            if nxt not in seen:
                seen.add(nxt)
                stack.append(nxt)
    return seen

net = ("Summary", "B2")
feeds = walk(net, precedents)
print("inputs behind net profit:", sorted(c for c in feeds if c not in formulas))
print("affected by the growth rate:", sorted(walk(("Inputs", "B4"), dependents)))

walk follows edges until nothing new turns up, so it finds indirect links too: net profit depends on Calc!B4 and Calc!B6, which depend on revenue and the tax rate, which depend on units and price. Filtering the result to cells that are not formulas gives the true inputs of a result — the list to review when someone asks "what assumptions is this number based on?" Going the other way, a changed growth rate affects only the next-year figures, which tells you which parts of a report need re-checking after the change.

Step 4 — Unused inputs, calculation order and circular references

Three audit checks fall out of the graph. An input that nothing reads is either dead or a sign that a formula was meant to use it. A topological sort of the graph gives an order in which every cell comes after the cells it reads — and fails if there is a cycle, naming it:

Python

from graphlib import TopologicalSorter, CycleError

referenced = set().union(*precedents.values())
inputs = [(ws.title, c.coordinate) for ws in wb.worksheets for row in ws.iter_rows(min_col=2)
          for c in row if c.value is not None and c.data_type == "n"]
print("unused inputs:", [i for i in inputs if i not in referenced])

try:
    order = list(TopologicalSorter(precedents).static_order())
    print("calculation order:", order)
except CycleError as err:
    print("circular reference:", err.args[1])

graphlib has been in the standard library since Python 3.9. CycleError carries the cycle as its second argument — here Calc!B7 and Calc!B8, which refer to each other. Excel would only say "There are one or more circular references" and point at one of them; the graph names every cell in the loop. The unused input is the FX rate: somebody added it for a currency conversion that never got built. To get the order anyway, remove the cells of the cycle from the graph and sort the rest.

Step 5 — Write the map into the workbook

An audit is easier to share as a sheet than as console output. Write one row per formula with its direct precedents, its dependents and whether it sits in a cycle:

Python

def label(c):
    sheet, coord = c
    return f"{sheet}!{coord}"

cyc = set()
try:
    TopologicalSorter(precedents).prepare()
except CycleError as err:
    cyc = set(err.args[1])

report = wb.create_sheet("Dependencies")
report.append(["Cell", "Formula", "Reads", "Read by", "In cycle"])
for cell in sorted(formulas):
    report.append([label(cell), formulas[cell], ", ".join(map(label, sorted(precedents[cell]))),
                   ", ".join(map(label, sorted(dependents.get(cell, ())))), "yes" if cell in cyc else ""])
report.column_dimensions["B"].width = 26
report.column_dimensions["C"].width = 40
wb.save("model_audited.xlsx")
for row in report.iter_rows(min_row=1, max_row=4, values_only=True):
    print(row)

Common pitfalls

Loading with data_only=True. You get cached values, not formulas; load normally for this job.
Matching references with a regular expression on the whole formula. Text inside quotes and sheet names with ! in them produce false references. Tokenize first.
Forgetting names and tables. Defined names resolve through wb.defined_names; table references need the table's range from ws.tables.
Huge ranges. Expanding A1:XFD1048576 is not an option; cap the expansion and keep large ranges as single nodes.
Volatile and indirect references. INDIRECT and OFFSET build references at calculation time. The graph cannot see through them; list every formula that uses them as a separate audit finding.

Performance notes

Tokenizing is fast — tens of thousands of formulas per second — and the graph is a dictionary of sets. The cost is in expanded ranges: a model full of SUMIFS over 50,000-row columns multiplies the number of edges. Keep large ranges as single range nodes and treat "reads range X" as the dependency; for audit purposes that is usually the question being asked anyway.

Conclusion

Tokenize every formula with openpyxl, keep the RANGE operands, resolve them to sheet-qualified cells — bare references to the formula's own sheet, names through wb.defined_names, ranges expanded up to a limit — and store them as a precedents dictionary. Walk it forwards and backwards to trace inputs and impact, use graphlib.TopologicalSorter for the calculation order and to name circular references, compare constants with referenced cells to find unused inputs, and write the result to a sheet reviewers can filter.

Frequently asked questions

How do I find which cells a formula depends on in Python? Load the workbook with openpyxl, split the formula with openpyxl.formula.Tokenizer and keep OPERAND tokens with subtype RANGE. Resolve each to a sheet and cell — bare references belong to the formula's own sheet, names are looked up in wb.defined_names — and repeat recursively for indirect precedents.

Can Python find circular references in an Excel workbook? Yes. Build a dictionary from each formula cell to the cells it reads and pass it to graphlib.TopologicalSorter. If there is a loop, it raises CycleError, and the error's second argument lists the cells in the cycle.

How do I find inputs that no formula uses? Collect every cell referenced by any formula, then list the constant cells on your input sheets that are not in that set. They are either unused assumptions or a sign that a formula points at the wrong cell.

Does this work with INDIRECT and OFFSET? Not fully. Those functions build references while Excel calculates, so the formula text does not say which cells they read. Flag formulas that use them and review them by hand.

Up one level: Working with Excel Formulas in Python — writing, reading and checking formulas.
Audit Every Formula in a Workbook with Python — an inventory of formulas, functions and hard-coded numbers.
Find External Links and Broken References in a Workbook — the ? nodes in detail.
Run What-If Scenarios on an Excel Model with Python — changing the inputs this map identifies.

More on formulas in workbooks