Formula Auditor

---
name: formula-auditor
description: Use when auditing a spreadsheet for formula issues — hardcoded values, broken refs, inconsistent formulas, named-range cleanup, external links. Triggers on "audit this workbook", "find formula issues", "model audit", "review this spreadsheet".
---

# Formula Auditor

Audit a workbook the way a senior FP&A lead would in a due-diligence call. The goal is a punch list, ranked by severity, with cell references the user can click through. Most "the numbers are off" issues come from 5 root causes — find them.

## Required inputs

1. **The workbook** — .xlsx file
2. **The user's confidence level** — "I built this" (light audit) vs "I inherited this" (full audit)
3. **Critical outputs** — which cells / tabs are the answer the workbook is delivering? Audit harder around those.

If the workbook has external links to files you don't have, flag immediately — you can't audit what you can't see, and external links are the #1 hidden source of broken numbers.

## Approach

**openpyxl** to read formulas as strings (not values) — load with `data_only=False`. Walk every cell, classify the contents (formula vs value vs blank), and run checks.

For complex models, **the formulas package** (or **xlcalculator**) can parse Excel formulas into ASTs — useful for finding hardcoded numbers within formulas (`=Revenue*0.4` vs `=Revenue*Tax_Rate`).

For Google Sheets: **gspread** + the values-and-formulas batch get. Same logic.

Output the audit AS a workbook (Audit.xlsx) with one row per finding. The user can sort and filter — audit reports as text are unusable for >50 findings.

## Audit workbook structure

1. **Summary** — counts by severity (Critical / High / Medium / Low), counts by category, top 10 findings
2. **Findings** — one row per issue. Columns: ID, Severity, Category, Tab, Cell, Formula, Issue, Suggested Fix
3. **Hardcoded Values** — cells where a formula contains a literal number (other than 0, 1, -1, 100). Filterable.
4. **Broken Refs** — cells with #REF!, #NAME?, #N/A, #DIV/0!, #VALUE!, #NULL!. With likely cause.
5. **Inconsistent Formulas** — rows where formulas should be uniform across columns but aren't (e.g., row 14 has the same formula in B14:M14 except L14 is hardcoded).
6. **Named Ranges** — list of named ranges, what they reference, and whether they're used. Flag dangling.
7. **External Links** — workbooks referenced from outside this file. List source file, count of references.
8. **Structural Issues** — merged cells, hidden rows/columns, sheet protection, circular references.

## What to check

### Hardcoded values in formulas (Critical / High)
- Formula contains a literal number > 1 (excluding 0, 1, -1, 100, common date constants).
- Worst offenders: tax rates, growth rates, FX rates, headcount counts.
- Suggested fix: move to an Assumptions tab as a named range.

### Broken or fragile references (Critical)
- Any `#`-prefixed error.
- References to deleted ranges or sheets (`#REF!`).
- Lookups returning `#N/A` consistently — usually a key-format mismatch (text vs number, trailing whitespace).
- Suggested fix: identify the dependency chain and repair from source.

### Inconsistent row formulas (High)
- A row of formulas that look the same but one cell differs. Usually means someone overwrote with a hardcoded value.
- Detection: parse formulas, compare structures across the row, flag differences.
- Suggested fix: re-extend the canonical formula.

### Circular references (High)
- Common with interest-on-cash, tax-on-tax. Sometimes intentional (with iterative calc enabled), sometimes a bug.
- Flag and ask the user to confirm intent.

### External links (Medium / High)
- Formulas referencing other .xlsx files. The model breaks if that file moves.
- Suggested fix: paste-values the dependency or import the dependency as a tab.

### Named-range cleanup (Medium)
- Dangling names (point to deleted ranges).
- Names with workbook-wide scope when they should be sheet-scoped (causes name collisions on copy).
- Names that aren't used anywhere.

### Merged cells (Medium)
- Break sorting, filtering, named ranges, and SUMIFS. Often used for visual layout — replaceable with "Center Across Selection."
- Flag and recommend removal.

### Hidden rows / columns / sheets (Low / Medium)
- Hidden tabs sometimes contain "scratch" calculations that affect outputs.
- List them — let the user decide if intentional.

### Sheet protection without password (Low)
- Often security theater — easily bypassed. Note for awareness.

### Formula complexity (Low)
- Single-cell formulas > 200 chars are hard to debug.
- Suggest decomposing into helper columns.

## Severity rules

- **Critical**: outputs are wrong RIGHT NOW. Broken refs, circular refs without iterative calc, hardcoded numbers in critical-path formulas.
- **High**: outputs will become wrong soon. Inconsistent row formulas (next quarter's column won't compute the same), external links to files that may move.
- **Medium**: hygiene issues. Merged cells, unused named ranges, hidden tabs.
- **Low**: stylistic. Long formulas, sheet protection notes.

## Common pitfalls in your audit

- **Flagging every literal number as a hardcoded value.** `=A1+1` is fine. `=A1*1.4` is not. Have a sensible threshold.
- **Missing context.** A hardcoded `0.21` for VAT in a UK model is a real concern; same number as a multiplier in a unit-conversion formula is fine. Flag, don't conclude.
- **Auditing volatile functions** (`NOW`, `TODAY`, `OFFSET`, `INDIRECT`) as errors. They're slow but not wrong. Note for performance, not correctness.
- **Producing a 400-row punch list.** Triage. Top 10 critical items go in the Summary; the rest in Findings.
- **Not checking external links by default.** They're the most common silent failure.

## Output

Generate the audit .xlsx. Print a summary: total findings, severity breakdown, the top 5 findings with cell references. End with a recommended action: typically "fix Critical and High first; tackle Medium during the next planning cycle." If you find > 50 issues in a small workbook, recommend a rebuild — past a point, audit and patch isn't the right play.