For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Excel formula audit checklist

AUD.F4 FORMULAS > TRACE PRECEDENTS
Excel formula audit checklist — Excel example preview

Checking $ absolute reference locks (F4) and tracing precedents before rolling a financial model forward to the next quarter.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Testing Claude in Excel: Building a Three-Statement Financial Model

Corporate Finance Institute

Open on YouTube (opens in a new tab)
Corporate Finance Institute

Loading preview…

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.2 uplifts and *0.85 discounts
  • /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:

  1. F5 → Special → Formulas on every calculation sheet — look for the gaps.
  2. Ctrl+F3 → Name Manager → sort by Value → look for #REF!.
  3. Check Calculation Options is Automatic, then press F9.
  4. 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.

=PRACTICE

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

  1. Name only the ranges that represent important business concepts.
  2. Use formula auditing arrows to trace inputs and outputs.
  3. Check formulas row by row for inconsistent references.
  4. 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.