Expert Excel · Interactive lesson 9 of 12
Controls, testing & audit trails
A10 caught one mismatch. A real QA layer proves the whole pipeline: every source row accounted for, every exception visible, every change logged — so trust in the output doesn't depend on trusting the builder.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialTesting Excel Models and Keeping Audit TrailsSkill check
Try these first:
- Write a record-count check that proves nothing was dropped on import.
- Write a reconciliation check comparing a detail total to a summary figure.
- Build an exception report that surfaces missing or invalid values.
- Explain what a change log should capture that a version number alone doesn't.
All familiar? Skip ahead to E10 — Performance & Scalability.
Concept teach
What you're learning
- Control totals & record counts
- A count of source rows compared against a count of loaded rows — the simplest possible proof that nothing silently vanished on import.
- Source-to-output reconciliation
- A visible check comparing a report total back to the raw source total it's supposed to represent — the same idea as A10, run against the whole pipeline instead of one sheet.
- Exception reports
- A filtered list of exactly what's wrong — missing IDs, values outside an expected range — so a reviewer checks one short list instead of scanning everything.
- Formula consistency tests
- Confirm the same formula pattern was actually copied down an entire column — one manually edited cell in the middle of 500 is one of the most common silent errors in a workbook.
- Protected ranges
- Lock the cells nobody should retype over — formulas, headers, controls — and leave only true inputs open, then protect the sheet.
- Change logs
- A running table of what changed, when, and who changed it — a version number alone tells you something changed, not what or why.
- Review checklists
- A short, specific list run before every release — reconciliation passed, exceptions cleared, protection on — turns "I think it's fine" into something checkable.
Where to find it in Excel
QA sheet›Review › Protect
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Check | Expected | Actual | Status | |||
| 2 | Row count: source = model | 4,812 | 4,812 | PASS | |||
| 3 | Revenue ties to export | 1,204,550 | 1,204,550 | PASS | |||
| 4 | Products without category | 0 | 3 | FAIL | |||
| 5 | Failed checks | 1 | |||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Swipe sideways to see the whole window.
- 1A QA sheet proves no rows were lost and totals tie back to the source.
- 2Each check returns PASS or FAIL; conditional formatting makes failures stand out.
- 3One failed-check count for the whole model.
- 4Allow Edit Ranges keeps input ranges open while the sheet is protected.
- 5The ChangeLog sheet records each release: date, change, who, checks passed.
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
Eight imported orders — one is missing its Product ID. Write the record count, the reconciliation check, and the exception report.
Independent extension
Add a real QA sheet
Add a QA sheet that proves imported sales tie to your dashboard: a record count matching source to loaded rows, a reconciliation check on the total, and an exception report flagging any missing Product ID or other required field. Fix what it finds before the report ships.
Spot a mistake or have an idea for this lesson? Send feedback