Goal
You’ll practice auditing existing spreadsheets for common real-world mistakes, applying the diagnostic thinking from across this entire course to spot problems in a workbook you didn’t build yourself.
Learn
Auditing an existing spreadsheet is a genuinely different skill from building a new one — it requires actively checking for specific known problem patterns, not just glancing at whether the numbers look roughly reasonable. A structured review checklist, pulling together this whole course:
- Hardcoded numbers (Part 1.3): Do formulas reference actual cells, or contain raw numbers that won’t update if the underlying data changes?
- Missing absolute references (Part 2.1): Does any AutoFilled formula reference a cell that should have stayed fixed, but didn’t?
- Chart type mismatches (Part 2.4): Is a pie chart being used for data that isn’t genuinely parts of one whole?
- Stale PivotTables (Part 4.1): Does a PivotTable’s total seem inconsistent with what the raw data actually shows, suggesting a forgotten refresh?
- Mixed raw data and dashboard (Part 5.4): Is raw source data sitting directly on the same sheet as summary visuals, risking accidental edits?
This kind of audit reveals that a spreadsheet can look completely fine at a glance while still containing genuine calculation and structural problems — a hardcoded number buried inside one formula among hundreds looks identical to a correct cell reference until you actually click into that specific cell and check.
Decision Task
You’re auditing a spreadsheet and find a formula =A1*0.15 used to calculate tax, appearing in 40 different cells throughout the sheet. Before reading on: what specific problem does this represent, and what would the correctly built version have used instead?
Show Answer
This is the hardcoded-number problem from Part 1.3/5.1 — 0.15 is directly typed into 40 separate formulas rather than referencing one actual cell (or better, a named range like TaxRate). If the tax rate ever changes, all 40 formulas need individually finding and editing, rather than updating one single named range or cell that every formula correctly references instead.
Common Mistake
Auditing a spreadsheet purely by checking whether the currently displayed numbers look reasonable, rather than actually opening specific formulas to check for the structural problems this whole course has covered. A spreadsheet can display completely correct-looking numbers today while still containing hardcoded values, missing absolute references, or stale PivotTables that will cause real problems the next time the data changes.
Practice Questions
1. An audited spreadsheet has =B2*1.2 repeated in 30 cells, calculating a 20% markup. What specific problem does this represent, and what fix does this course recommend?
Show Answer
A hardcoded number problem (Part 1.3, 5.1) — 1.2 should instead be a named range like MarkupRate, referenced consistently, so a future markup change requires updating just one place instead of 30 separate formulas.
2. An audited chart shows 12 categories in a pie chart, each barely visible as a tiny sliver. What does Part 2.4 say about this specific situation?
Show Answer
Pie charts become genuinely hard to read with too many categories, even if technically valid; a column chart would very likely communicate this same data far more clearly.
3. An audited PivotTable’s total doesn’t match what manually adding up the visible raw data suggests it should. What’s the most likely explanation, from Part 4.1?
Show Answer
The PivotTable likely hasn’t been refreshed since new data was added to the underlying source, so it’s still showing an outdated total that doesn’t yet include the newer rows.
4. True or False: a spreadsheet displaying completely correct-looking numbers today is sufficient evidence that it has no underlying structural problems.
Show Answer
False — this is the core lesson here; structural problems (hardcoded values, missing absolute references, stale PivotTables) can exist invisibly until specific future conditions (a data change, a new row) expose them.
5. List the five audit checklist categories from this lesson, in your own words.
Show Answer
Hardcoded numbers instead of references, missing absolute references where needed, chart type mismatches, potentially stale PivotTables, and raw data mixed directly with dashboard visuals.
Try It Yourself
Using this lesson’s checklist, audit (in your head, or on an actual spreadsheet you have access to) a real spreadsheet you use regularly, and see if you can spot at least one likely issue from the checklist.
This is an open, ungraded reflection exercise — there is no single correct answer to reveal.
Quick Check
1. Why is auditing a spreadsheet a genuinely different skill from building a new one?
Show Answer
It requires actively checking for specific known problem patterns, not just glancing at whether displayed numbers look reasonable.
2. What hardcoded-number check does the audit checklist include?
Show Answer
Whether formulas reference actual cells (or named ranges) versus containing raw numbers that won’t update automatically.
3. What chart-related check does the audit checklist include?
Show Answer
Whether a chart type genuinely fits the data it’s displaying, like avoiding pie charts for non-whole-part data.
4. Can a spreadsheet look completely correct today while still having a genuine underlying problem?
Show Answer
Yes — this is the core point of this lesson; some problems only become visible under specific future conditions, like a data change or an added row.
5. What PivotTable-related check does the audit checklist include?
Show Answer
Whether a PivotTable’s total seems inconsistent with the actual current raw data, suggesting a forgotten refresh.