excel-formula-auditor

Excel Formula Auditor

This skill performs a structured spreadsheet-risk review of a financial model or schedule before it reaches a reviewer, manager, or partner. It applies the same defensive checks an experienced preparer runs over their own work: are the formulas consistent, are constants hidden inside calculations, do the links resolve, are there error chains, is anything hidden, do signs and rounding behave. It is built for auditors testing client-prepared spreadsheets, FP&A teams reviewing budget and forecast models, and any finance preparer who wants a clean self-review before sign-off. It operates only on the files you provide, in your session, and never reaches the network.

When to use this

• You are about to send a workpaper, lead schedule, or model to a reviewer and want to catch defects first.
• An auditor receives a client-prepared spreadsheet (PBC) and must assess its reliability before relying on it as audit evidence (ISA 500 relevance and reliability of information used as audit evidence; ISA 520 analytical procedures).
• You inherited a model from a colleague who has left and need to understand its integrity.
• A number changed unexpectedly and you suspect a broken link, a hardcoded override, or a copy-paste formula error.
• You are documenting controls over an end-user computing (EUC) spreadsheet for SOX or internal-controls purposes.

Inputs

Provide one of the following:

• An Excel workbook (.xlsx, .xlsm). The skill reads cell formulas, values, sheet visibility, row/column visibility, and external link references.
• A CSV export when only values are available (formula-level checks degrade gracefully to value-level pattern checks).
• A formula dump: a table with columns Sheet, Cell, Formula, Value if the user has extracted it.

A bundled sample, samples/model_extract.csv, is a fictional formula extract from a small revenue and margin schedule for "Aurora Components Ltd." Each row carries the sheet, cell, the formula text, and the displayed value, so the methodology below can be demonstrated end to end without a binary file.

If you receive an .xlsx, open it programmatically (e.g. with the openpyxl library in a sandboxed read of the file the user supplied) reading with data_only=False to see formulas and again with data_only=True to see cached values. Never modify the source file; write findings to a new file.

How to do it

Work sheet by sheet, then cell by cell. For each region (a contiguous calculated row or column), establish the expected pattern first, then test each cell against it. Assign a severity to every finding: High (wrong number likely reaches a decision), Medium (fragile or error-prone but currently correct), Low (hygiene / presentation).

1. Map the workbook. List every sheet with its visibility state (visible, hidden, very hidden). Flag any sheet that is hidden or very hidden - reviewers cannot see what they cannot see. Note hidden rows and hidden columns inside used ranges. Hidden content that feeds visible totals is a High finding; cosmetic hidden helper columns are Low.

2. Detect hardcoded constants embedded in formulas. Parse each formula and flag numeric literals other than the trivial set {0, 1, -1, 100} and obvious structural constants (e.g. 12 for months may be acceptable - judge by context). A formula like =B5*1.075 hides a tax or growth rate; =SUM(C2:C9)+4500 hides a manual plug. These break when assumptions change and are invisible to a reviewer scanning values. Severity High when the constant is a rate or a plug that affects a reported total; Medium otherwise. Suggested fix: move the constant to a labelled input cell and reference it.

3. Test formula consistency across rows and columns. Within a block that should be homogeneous (e.g. a row of monthly totals, a column of Revenue - COGS), normalise each formula to its relative R1C1 form and compare. Any cell whose R1C1 pattern differs from its neighbours is an inconsistency. The classic defect is one cell in a copied row that was overtyped with a value or a different formula. Severity High - this is the single most common cause of a model producing a wrong number. Suggested fix: state the dominant pattern and the deviating cell(s).

4. Trace error values and their chains. Identify every cell whose cached value is an Excel error: #REF! (deleted cell/range), #DIV/0! (division by empty or zero), #VALUE! (type mismatch), #N/A (failed lookup), #NAME? (unknown name), #NUM!, #NULL!. Then trace downstream: any formula referencing an error cell inherits it. Report the root error cell as High and list the dependent cells it poisons. Suggested fix: identify the deleted reference or guard the divisor (e.g. =IF(B2=0,0,A2/B2)), and note that masking with IFERROR without fixing the root cause hides the defect rather than resolving it.

5. Resolve links. Flag external links (formulas containing [ ] workbook references or full file paths such as 'C:\...\[Budget.xlsx]Sheet1'!A1). External links break on file move and silently serve stale cached values; in an audit context they undermine reliability. Flag broken internal links (#REF! inside a formula). Severity High for external links feeding reported figures, Medium for internal links pointing at the right cell but fragile. Suggested fix: bring the source data into the workbook or convert to a documented, controlled link.

6. Check signs. For known sign conventions, test that values carry the expected sign: costs, expenses, and contra items negative (or consistently positive if the model nets with subtraction); revenue positive; a subtotal Gross margin = Revenue + COGS only works if COGS is stored negative. Flag any line whose sign is inconsistent with sibling lines or that would invert a subtotal. A reversed sign on one cost line is a High finding because it usually overstates profit. Suggested fix: state the convention and the offending cell.

7. Check rounding consistency. Detect mixed rounding: some cells wrapped in ROUND(...,2), ROUNDDOWN, MROUND, or INT while siblings are not; or displayed decimals (number format) differing from stored precision so that a column of rounded-looking figures does not foot to its shown total. Flag where a total computed from unrounded inputs is presented next to rounded components (the classic "the column does not add up" reviewer query). Severity Medium. Suggested fix: round consistently at one defined layer (round on display, or round inputs once and compute on rounded values - not both).

8. Foot and cross-foot. Independently recompute each subtotal and total from its components and compare to the stored value, with a tolerance of half the smallest displayed unit. Recompute cross-casts (row totals vs column totals reconciling to a grand total). Any difference beyond rounding tolerance is High. This catches plugs, dropped rows in SUM ranges, and overtyped totals.

9. Note volatile and fragile constructs. Flag whole-column/row references in SUM/VLOOKUP that may pick up stray data, VLOOKUP with non-exact match where exact was intended, and volatile functions (OFFSET, INDIRECT, TODAY, NOW, RAND) that make results non-reproducible across reopens - relevant where an audit needs a fixed, re-performable figure. Severity Medium to Low.

Output

Produce a Markdown findings workpaper named formula-audit-findings.md, plus a parallel formula-audit-findings.csv for tracking. Both contain one row per finding.

Header section:

• Workbook name, number of sheets (visible / hidden / very hidden), date of review, preparer note that this is a self-review aid.
• Summary counts by severity (High / Medium / Low).

Findings table columns:

Column	Content
ID	Sequential finding number
Sheet	Sheet name
Cell / Range	e.g. Margin!D7 or Margin!D2:D9
Category	Hardcode / Inconsistency / Error / Link / Hidden / Sign / Rounding / Footing / Fragile
Severity	High / Medium / Low
Observation	What was found, with the actual formula or value quoted
Suggested fix	Specific, actionable remediation
Status	Open (preparer fills in once addressed)

End with a short narrative "Reviewer summary" of the two or three highest-risk items.

Quality checks

Before handing over, verify:

• Every finding cites an exact cell or range - no vague "somewhere in column D".
• Each quoted formula matches the source workbook character for character.
• High-severity findings genuinely affect a reported or decision-relevant number; do not inflate severity.
• You distinguished a real hardcode from an acceptable structural constant (do not flag =A1/12 as a hidden rate without judgement).
• You did not flag a deliberately different formula (e.g. an opening-balance cell that legitimately differs from the roll-forward column) as an inconsistency - explain why a deviation may be intentional.
• IFERROR masking is reported as a concern, not silently accepted.
• The source file was not modified; findings live in new files only.
• No external network reference was followed; links are reported, never fetched.

Common errors to avoid: treating cached values in a CSV as authoritative when formulas were not available (state the limitation); assuming a sign convention without confirming the model's stated convention; reporting a footing difference that is in fact within rounding tolerance.

Worked example

Using samples/model_extract.csv for Aurora Components Ltd:

• Margin!D7 contains =D5*0.21 while the column header documents a 19% rate held in Inputs!B2. Finding: Hardcode, High - the 21% literal contradicts the input cell. Fix: =D5*Inputs!$B$2.
• Revenue!E4 is =SUM(E2:E3) whereas B4:D4 are all =SUM(B2:B3) style; normalised to R1C1 the row is consistent except E4, which omits a row picked up elsewhere. Finding: Inconsistency / Footing, High. Fix: extend the range to match siblings.
• Margin!D9 shows #DIV/0! from =D8/D6 where D6 is blank, and Summary!B3 references Margin!D9, inheriting the error. Finding: Error chain, High, root Margin!D9, dependent Summary!B3. Fix: guard the divisor.
• Revenue!B6 is ='C:\Forecasts\[Q2Budget.xlsx]Sheet1'!B6. Finding: Link, High - external link serving a possibly stale cached value. Fix: import the figure into the workbook.
• The sheet Scratch is hidden and feeds Summary!B5. Finding: Hidden, High. Fix: unhide or remove the dependency.

The resulting workpaper lists these as IDs 1-5 with the columns above, summarised as five High findings concentrated on the margin calculation.

Disclaimer

Disclaimer: This skill is a drafting and analysis aid, not professional advice. It does not provide accounting, audit, tax, investment, or legal advice. All output must be reviewed and approved by a qualified professional before use or reliance.

---

Part of LedgerSkills - security-vetted Claude Code skills for finance teams. New skills scanned and tested weekly; subscribe on the site.