2.7 Errors, auditing and trusting a spreadsheet

What you'll be able to do

Find out why a number is wrong instead of typing over it. Read what each error value is telling you. Follow a formula's inputs across the sheet. Build the checking row that catches tomorrow's mistake before a reader does.

Before you start

Any workbook of yours with real formulas in it. The audit teaches more on your own work.

The work

1. Same either way: read errors as admissions, not failures. #DIV/0! means the formula divided by an empty or zero cell. #N/A means a lookup found nothing, for one of the three causes in the lookup topic. #VALUE! means text was fed into arithmetic. #REF! means the formula's target was deleted, so the reference itself is gone. #NAME? means a misspelled function, or text left without quotation marks. Each one names its cause, and the fix starts from that name.
2. Same either way: handle the misses you expect without silencing real faults. =IFNA(XLOOKUP(...), "not on file") catches only #N/A, the miss you planned for, and lets every other error stay loud. =IFERROR(...) swallows all of them, including the #REF! that means the sheet is damaged. Reach for IFNA first, and use IFERROR only when any failure honestly means the same fallback. Wrapping a wrong formula in IFERROR to make the red go away hides a fault instead of handling it.
3. No documented keyboard shortcut for this — use the menu: to see where a suspicious cell's inputs come from, select it and go to Formulas → Trace Precedents. Arrows draw the flow of data into the cell. Trace Dependents draws who reads from it, and running that before you edit anything tells you what you are about to disturb. Remove Arrows, on the same tab, clears the drawing.
4. No documented keyboard shortcut for this — use the menu: to watch a formula work, select it and go to Formulas → Evaluate Formula. Each press of Evaluate works out the next piece, underlined, and you see the wrong turn happen. For a quick look at one piece instead, select it in the formula bar and press F9 to work it out in place. Press Esc so the result is not saved into the formula.
5. Same either way: build the checking row. Wherever two routes must agree, make a cell that says so. Column totals against a grand total: =IF(SUM(H2:H13)=H15, "OK", "CHECK"). It costs one row, and it catches the kind of error that reaches readers: the wrong number that looks believable.
6. Same either way: audit the sheet visually in ten seconds. Press Ctrl+Grave accent to show every formula at once, and the hard-typed number hiding in a formula column stands out immediately. Press it again to go back.

Try it

In your own workbook: cause each error once on a scratch sheet and fix it from its name. Follow one important cell's precedents and dependents. Walk one formula through Evaluate Formula. Then add a checking cell where two totals must agree, and leave it in.

Check yourself