Advanced Excel · Interactive lesson 10 of 12
Audit, controls & error checking
Before a workbook ships to someone else, it should be able to prove itself right — a reconciliation total that matches, a trail from a wrong number back to its source, a flag that catches a typo before a reader does.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialError Checking and Controls in ExcelSkill check
Try these first:
- Trace which cells a formula depends on, and which formulas depend on a cell.
- Step through a formula's calculation with Evaluate Formula.
- Add a reconciliation check comparing a detail total to a summary total.
- Build an error-summary area that flags problems instead of hiding them.
All familiar? Skip ahead to A11 — Performance & Maintainability.
Concept teach
What you're learning
- Trace precedents / dependents
- Arrows from a formula to what it reads (precedents) or from a cell to what reads it (dependents) — the fastest way into a sheet you didn't build.
- Evaluate Formula
- Steps through a calculation one piece at a time — the difference between guessing why a number is wrong and watching exactly where it goes wrong.
- Reconciliation checks
- A visible
=IF(SUM(detail)=summary,"OK","Mismatch")cell catches a typo'd total before a reader ever notices the number looks off. - Hidden rows and filters
- A SUM over a filtered range still includes the hidden rows unless you use
SUBTOTAL— a classic source of a total that "shouldn't" be wrong but is. - Error summary area
- One visible cell (or small table) that counts every
#REF!,#N/A, and failed check on the sheet — so a reviewer checks one cell, not every formula. - Protecting final outputs
- Lock the cells nobody should retype over, leave inputs open, then Protect Sheet — the same idea as I11, now applied to a report about to leave your hands.
Where to find it in Excel
Formulas›Formula Auditing
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total | ||||
| 2 | Pens | 12 | 1.50 | 18.00 | ||||
| 3 | Paper | 3 | 6.00 | 18.00 | ||||
| 4 | Tape | 4 | 2.25 | 9.00 | ||||
| 5 | Folders | 10 | 0.80 | 8.00 | ||||
| 6 | Clips | 6 | 1.10 | 6.60 | ||||
| 7 | Ledger total | 59.60 | ||||||
| 8 | Difference | 0.00 | ||||||
| 9 | Check | OK |
Swipe sideways to see the whole window.
- 1Trace Precedents shows what feeds a suspicious number.
- 2Error Checking walks through every error on the sheet.
- 3Evaluate Formula steps through one formula.
- 4A check cell returns OK when the difference is zero; list all checks in one error-summary area.
- 5Use SUBTOTAL or AGGREGATE when rows may be hidden or filtered.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Four expense categories and a summary total that was typed by hand — and doesn't match. Write the reconciliation check, then use the audit tools to find the mismatch.
Independent extension
Audit a report before you send it
Take a report you're about to send someone and add: one reconciliation check comparing a detail total to a summary figure, one trace on the cell you trust least, and a small error-summary cell counting any #REF! or #N/A on the sheet. Fix what the checks find before it leaves your hands.
Spot a mistake or have an idea for this lesson? Send feedback