Excel formula audit checklist

Checking $ absolute reference locks (F4) and tracing precedents before rolling a financial model forward to the next quarter.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

Build a 3-Statement Financial Model [Free Course]
Wall Street Prep
Open on YouTube (opens in a new tab)
Testing Claude in Excel: Building a Three-Statement Financial Model
Corporate Finance Institute
Open on YouTube (opens in a new tab)
A formula audit is not about proving a workbook is perfect. It is about finding, in the time you actually have, the mistakes most likely to change a decision.
Studies of spreadsheet error rates consistently find that a large majority of non-trivial models contain at least one error. The interesting part is which errors matter: a rounding difference in the third decimal is not what gets a model withdrawn. A formula that stops one row short of the bottom of the data is.
This checklist is ordered by how much damage each category usually does.
1. Find the consistency breaks
In a correct model, formulas copy cleanly down a column or across a row. The dangerous cell is the one in the middle that is different — somebody patched a number in March 2024 and nobody noticed.
F5 → Special → Formulas selects every formula cell at once, which shows you
instantly where formulas stop and typed values begin. A column that should be all
formula and shows a gap is your first finding.
`Ctrl+`` (backtick) toggles Show Formulas for the whole sheet. Scanning a column in this mode makes an odd one out visible in a way that scanning results never does.
The green triangles Excel puts in cell corners flag “inconsistent formula in region” among other things. They are switched on by default but people switch them off because of the false positives on text-stored numbers. For an audit, turn them back on: File → Options → Formulas → Error checking rules.
For a column inside an Excel Table, this class of error is largely prevented rather than detected — Tables fill formulas down automatically and flag the exception. That is one of the stronger arguments in Excel Table vs normal range.
2. Trace the numbers that matter
For the three or four outputs anyone will actually quote, follow the chain back.
Formulas → Trace Precedents draws arrows to the cells feeding the selected one; press it repeatedly to go another level back. A solid arrow means same sheet; a dashed arrow with a small worksheet icon means another sheet or another workbook — double-click that to jump.
Trace Dependents goes the other way, and is the one to use before you change an assumption. It answers “what else moves if I touch this?”
F5 → Special → Precedents → All levels selects the whole upstream set at
once, which is faster when you want to know the extent of what feeds a number
rather than the path.
Follow each chain until you reach either an input cell or source data. If a chain terminates in a number typed into the middle of a calculation, that is finding number two.
3. Search for hardcoded values
Hardcoded numbers are not always wrong — 12 in a monthly-to-annual conversion
is fine, 0.5 in a midpoint is fine. They are wrong when they are assumptions
hiding inside arithmetic, because nobody can find them to change them.
Use Find (Ctrl+F) with Look in: Formulas, and search for:
*1.and*0.— catches*1.2uplifts and*0.85discounts/12,/365,/52— period conversions, sometimes wrong for the convention in use+0,-0— plugs, which deserve an explanation
The rule of thumb: if the number would be different next year, it belongs in a named input cell, not in a formula. The reasoning and the naming convention are in when to use named ranges.
4. Review errors — and the errors that have been hidden
F5 → Special → Formulas → Errors selects every visible error. Work through
them by type, because each one means something specific:
| Error | Usual cause |
|---|---|
#N/A |
A lookup found nothing. Sometimes correct, often a key mismatch |
#REF! |
A referenced cell was deleted. Always a real bug |
#VALUE! |
Text where a number was expected |
#DIV/0! |
A denominator is zero or blank |
#NAME? |
A typo in a function name, or a deleted defined name |
#SPILL! |
A dynamic array is blocked by something in its way |
Then do the harder half: find the errors that have been suppressed. Search for
IFERROR across the workbook. Each one is a decision that an error should become
something else, and that decision is sometimes right and sometimes a bandage over
a broken lookup.
=IFERROR(VLOOKUP(...),0) on a cost line means every failed lookup silently
contributes zero to the total. The total still foots. It is just too small. This
is, in practice, one of the most common serious errors in otherwise careful
models — prefer IFNA where you only want to catch “not found”, so genuine
#VALUE! and #REF! problems stay visible.
Also check for blanks inside ranges that should be complete:
=COUNTBLANK(tblSales[Amount])
and for numbers stored as text, which sum as zero:
=COUNT(tblSales[Amount])
=COUNTA(tblSales[Amount])
If those two disagree, some rows are text.
5. Check for circular references and calculation state
Formulas → Error Checking → Circular References lists any. Excel shows
Circular Reference in the status bar when one exists, but only for the first
one, and only until the file is reopened.
If iterative calculation is switched on (File → Options → Formulas → Enable iterative calculation), circular references stop announcing themselves at all and quietly converge — or quietly don’t. Interest-on-average-balance models legitimately use this. If you did not turn it on deliberately, turn it off and see what breaks.
While you are in that dialog: Calculation Options must read Automatic. The
setting is saved in the file, so a workbook left in Manual shows whoever opens it
a set of stale numbers with no warning whatsoever. Press F9, then
Ctrl+Alt+Shift+F9 for a full rebuild of the dependency tree, before trusting
anything on screen.
6. Look at the volatile functions
OFFSET, INDIRECT, TODAY, NOW, RAND, and CELL recalculate on every
change anywhere in the workbook. A model that feels slow usually has a few
hundred of them.
INDIRECT has a second problem: it is invisible to Trace Precedents, so the
audit trail stops there. A model that routes its key references through
INDIRECT cannot really be audited at all. Where you find one, ask whether
INDEX or a Table reference would do the same job — it usually will.
TODAY() in a report is its own trap: saved reports silently re-age every time
they are opened. Put the reporting date in one cell and reference that.
7. Add control totals that can actually fail
A check that cannot fail is decoration. Each of these can:
=ROUND(SUM(tblData[Amount]),2)=ROUND(SUM(Report!D5:O40),2)
=COUNTA(tblData[ID])=ROWS(tblData)
=ABS(TotalAssets-(TotalLiabilities+Equity))<0.01
Three properties make a control total worth having. It compares two numbers
computed by different routes — a check that recalculates the same thing the
same way proves nothing. It uses ROUND or a tolerance, so floating-point noise
does not produce permanent false alarms. And it rolls up into one visible status
cell at the top of the report, because a check nobody looks at is not a check.
The tie-out that catches the most real errors is the first one: report total against source total. It fails whenever a category exists in the data but has no row in the report, which is how money becomes invisible.
8. The ten-minute version
If you have no time at all, do these four:
F5 → Special → Formulason every calculation sheet — look for the gaps.Ctrl+F3→ Name Manager → sort by Value → look for#REF!.- Check Calculation Options is Automatic, then press
F9. - Confirm the report total ties to the source total.
That catches the broken-reference, stale-calculation, and missing-row failures, which between them account for most of the errors that actually reach a reader.
The rest of the pre-send pass — filters, hidden sheets, external links, metadata — is in the workbook review checklist before sending.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Make formulas easier to inspect, explain, and fix when the workbook grows. For this article, the goal is to practice: A working checklist for reviewing a model before it goes out — consistency breaks, hardcoded values, the errors that are hidden rather than fixed, circular references, volatile functions, and control totals that actually catch things.
Practice workbook setup
Use a workbook with a few formulas, assumptions, and dependent outputs.
Practice workflow
- Name only the ranges that represent important business concepts.
- Use formula auditing arrows to trace inputs and outputs.
- Check formulas row by row for inconsistent references.
- Add notes for assumptions that affect multiple calculations.
Quality checks
- Names are descriptive and not too generic.
- Formula dependencies are visible and explainable.
- No formula relies on a hidden hard-coded value.
Common mistakes
- Creating too many names until the workbook is harder to understand.
- Leaving old names that point to deleted or moved ranges.
- Auditing only the final formula and not the intermediate logic.
Next actions
- Open Name Manager and remove unused or unclear names.
- Create a short assumptions table for model-critical inputs.
Formula focus: test IFERROR(), SUMIFS(), COUNTIF(), ISNUMBER(), ROUND() with normal rows, blank inputs, and at least one edge case.





