The situation arrives in every working life: a workbook you didn’t build, a bottom-line number that looks wrong, and someone asking you to confirm it. Staring at cells hoping for insight is not a method. Excel ships a proper toolkit for exactly this — Formulas → Formula Auditing — and almost nobody opens it. This lesson is that toolbox, in the order a real audit uses it.

First: see all the machinery at once

Ctrl + ` (the backtick, top-left of the keyboard) flips the whole sheet to formula view — every cell showing its workings instead of its result. Thirty seconds of scrolling answers the structural questions: where do formulas live, where are the hard-typed numbers hiding among them, does column D do the same thing all the way down?

That last question has a dedicated detector. Go To Special → Row differences (F5 → Special) flags cells that differ from their row’s pattern — which is how you find the one cell in five hundred where someone once typed 4200 over the formula and “fixed” a report forever. Inside a Table, calculated columns make this class of rot impossible; in the wild workbooks you audit, it’s endemic.

Then: follow the wires

Select the suspicious cell and Trace Precedents draws blue arrows from every cell that feeds it. Click again: the feeders’ feeders. The number’s whole ancestry, on screen — and the surprises are immediate: an arrow from a forgotten sheet, a range that stops a row short, an arrow from a cell containing a number where a rate should have had a name.

Trace Dependents points the other way — who uses this cell? — and is the tool for the question that precedes every edit of a strange workbook: “if I change this, what breaks?” (A dashed arrow to a little grid icon means “something on another sheet” — double-click the arrow to jump there.)

Total £84,120? =SUM(Q1) =SUM(Q2) =B4*rate 4200 ← typed! three arrows in: a hard-typed number where a formula should be — found, not guessed
Trace Precedents draws the number's ancestry. The audit isn't cleverness — it's following wires until one of them surprises you.

Then: watch it think

For the formula itself, three levels of magnification. F9 — select any fragment inside the formula bar and press F9: that piece evaluates in place (Esc, not Enter, or the evaluation is pasted in permanently — the auditor’s classic own goal). Evaluate Formula does the same thing formally — stepping through the calculation one operation at a time, the debugger nobody knows Excel has, and the fastest way to see which part of a nested condition went sideways. And the Watch Window pins any cell’s live value on screen while you edit elsewhere — change an input on sheet one, watch the bottom line on sheet nine react (or damningly, not).

For errors specifically, Error Checking → Trace Error jumps straight to the origin of a cascade: fix the one #VALUE! at the source and forty downstream errors evaporate.

The audit, as a routine

Ten minutes, in order, before trusting any inherited number:

  1. Ctrl+ — where’s the machinery? Any numbers where formulas should be?
  2. Row differences on the money columns — any broken patterns?
  3. Trace Precedents on the bottom line — does the ancestry make sense? Any feeders that are typed constants?
  4. F9 the suspicious fragments; Evaluate the genuinely weird one.
  5. Check the ranges: do the SUMs reach the last row?

And the closing move every audit deserves: leave the workbook more auditable than you found it — a name on the magic number you unearthed, a comment on the fragile bit, a LET where the nesting defeated you. Auditing is how you earn trust in other people’s sheets; building sheets that don’t need this toolkit is how they’ll trust yours.

Blue arrows, a backtick, and F9. You never have to stare at a spreadsheet hoping again.