How to Audit an Inherited Excel Workbook

The "I just inherited this thing and have no idea what's inside" problem

Your colleague leaves the company. The financial model that ran the budget. The talent review workbook. The customer aging report. The portfolio tracker. Suddenly it's yours. The model "ties out" but you don't know which cells are inputs, which are formulas, which are overrides, which are comments-explained, which are silently broken.

The audit is the unglamorous but critical first task. This guide is the systematic approach.

TL;DR — Key takeaways

• Auditing an inherited workbook is about understanding structure before trusting outputs.
• Six audit dimensions: formulas vs hardcoded cells, errors, comments, hidden sheets, blanks, overrides.
• Free VBA macros handle each dimension as a one-shot operation.
• Always work on a copy. Audit changes (highlights, error reports) are easier to discard than to maintain.
• The full audit pass for a 14-tab model takes about 30 minutes with macros versus 3 to 4 hours manually.

The 6 audit dimensions

Dimension	Question	Tool
Formulas vs values	Which cells calculate, which are typed?	Highlight All Formula Cells
Errors	Where is the model broken?	List Cells With Formula Errors
Comments	What did the author note?	Highlight Cells With Comments
Hidden sheets	What's not showing in the tab bar?	Unhide All Sheets
Blanks	Where is data missing?	Highlight Blank Cells
Overrides	Are formulas being subverted?	Combination of macros

Audit step 1: Map the structure

Before drilling into cells, understand the workbook shape.

Unhide all sheets

The first thing to do. Hidden tabs may contain critical context, working calculations, or staging data the visible tabs depend on.

Run Unhide All Sheets. Walk every tab and note:

• Tabs that look like inputs (assumptions, scenarios, parameters).
• Tabs that look like calculations (formulas referencing inputs).
• Tabs that look like outputs (charts, summary tables, board pack pages).
• Tabs that look like working scratch (test calcs, sandboxes, "old version 2").

Generate an index

Run List All Sheet Names to create a navigable index. Useful for the rest of the audit.

Audit step 2: Find the formulas

The "calculated vs typed" distinction is the single most important audit signal.

Run Highlight All Formula Cells on each tab. Every formula cell turns light yellow.

The white cells in the result are:

• Inputs (assumptions, scenarios, parameters) — these should be white.
• Headers and labels — these should be white.
• Suspicious overrides — these should NOT be white if they're in the middle of a calculation chain.

That third category is the audit gold. A cell that should be calculating something but is hardcoded is the silent landmine in an inherited model.

For a 14-tab quarterly model with about 4,200 cells of inputs and intermediate calcs, this audit pass takes about 12 minutes versus 60+ minutes of click-each-cell-and-look.

Deep dive: How to Find All Cells With Formulas in Excel.

Audit step 3: Find the errors

If the model has any #REF!, #VALUE!, #N/A, #DIV/0!, or other formula errors, you want a complete list.

Run List Cells With Formula Errors on each tab (or on the entire used range Ctrl+A twice). The macro generates a new sheet called Error Report with every error cell listed by address, error type, and underlying formula.

Walk the report. Fix each error. Re-run to confirm zero.

Common error causes:

• #REF!: a row or column was deleted that the formula referenced.
• #VALUE!: text in a cell where a number is expected.
• #N/A: VLOOKUP didn't find a match.
• #DIV/0!: division by zero or empty cell.
• #NAME?: misspelled function name or undefined named range.

Deep dive: How to Find Cells With Formula Errors in Excel.

Audit step 4: Read the comments

The previous author may have left detailed notes on assumption cells: which source the number came from, which exec signed off, which date the assumption was last refreshed.

Run Highlight Cells With Comments on each tab. Every commented cell turns light green.

Walk each green cell. Hover to read the comment. Decide:

• Is the comment still accurate?
• Does the assumption need updating?
• Should the comment be deleted (irrelevant) or kept (useful context)?

For sensitive comments that shouldn't survive an external share, after auditing, run Clear All Comments on the audit copy.

Deep dive: How to Find All Cells With Formulas in Excel.

Audit step 5: Find the blanks

Missing data can break formulas (DIV/0 from missing denominators) and silently corrupt aggregations (SUM of mostly blanks looks fine).

Run Highlight Blank Cells on the input tabs. Walk each blank.

Categorize:

• Intentional blanks (excused absences, not-applicable cells).
• Missing data (should have a value, doesn't).
• Calculated blanks (formula returns "" because of an IF).

Fill missing data; flag intentional blanks; verify calculated blanks are correctly conditional.

Audit step 6: Sniff for overrides

Overrides are the trickiest audit dimension. An override is when a previous owner replaced a formula with a hardcoded number to "make it tie out" temporarily, then forgot to undo.

Signs of overrides:

• A cell in the middle of a calculation chain that's hardcoded (white in the formula highlight).
• A cell whose value matches a target rather than a calculation result.
• Comments that say "manual override" or "temp fix".
• Numbers that are suspiciously round when surrounding numbers aren't.

There's no automatic override detector. You have to read the model and use judgment. The macros help you find candidates; the override decision is human.

Audit step 7: Decide what stays, what changes

After steps 1-6, you have:

• A list of formula errors (fixed).
• A list of comments (kept or cleared).
• A map of hidden tabs (kept hidden, unhidden, or deleted).
• A list of overrides found (corrected to formulas or documented as intentional).

Decisions to make:

• Refactor the suspicious cells: replace hardcoded overrides with formulas, document any that must stay as overrides.
• Delete unused tabs: with Delete All Hidden Sheets for the audit copy.
• Clean up comments: keep critical context; delete stale notes with Clear All Comments.
• Generate the navigation index: List All Sheet Names for the cleaned-up workbook.

Audit step 8: Hand-off prep

If you're handing the audited workbook to someone else (auditor, successor, partner), prep before share:

1. Save audit copy with _audit_copy suffix.
2. Freeze formulas on summary tabs with Convert Formulas to Values so the auditor can't accidentally break them.
3. Apply consistent formatting: Freeze Top Row on All Sheets, AutoFit All Columns on All Sheets.
4. Protect sheets: Protect All Sheets With Password if needed.
5. Generate index: List All Sheet Names.

A typical audit timeline

For a 14-tab quarterly financial model inherited from a departing analyst:

Step	Task	Time
1	Unhide all sheets, generate index	1 min
2	Highlight formulas across all tabs	5 min
3	Walk and investigate suspicious whites	30 min
4	List errors, fix each	15 min
5	Highlight comments, walk each	10 min
6	Highlight blanks, walk each	5 min
7	Refactor overrides (if any)	0-30 min
8	Hand-off prep	5 min
Total		70-100 min

Versus the same audit done manually: 4 to 6 hours, with higher risk of missing things.

What audits don't catch

A few things macro-driven audits don't surface:

• Logic errors in formulas that compute wrong but don't error. The formula has the wrong sign, the wrong divisor, the wrong reference. Logic errors require domain expertise to spot, not macros.
• Stale assumptions. A 2-year-old cost-of-capital assumption that's still being used. The number isn't an error; it's just outdated. Comments may flag this; if not, you have to know to ask.
• Missing tabs (the model used to have a Q4 tab that got deleted). Comparing to a prior backup catches these.

The macros find the structural issues. The judgment issues stay with the human auditor.

Frequently asked questions

Are these macros approved by IT?

Each macro is open-source. The .bas files are plain text. They make no network calls and run only on user invocation. Most IT teams will whitelist after reviewing the source. For details see How to Use Excel VBA Macros Without Knowing How to Code.

Can I share the highlighted workbook with the auditor?

The macro's color highlights are stored as cell formatting and persist when you save and share. So a yellow-highlighted formula cell in your audit copy stays yellow when the auditor opens it. Useful for "here's what I checked" handoffs. To revert the highlights before sharing, Ctrl+Z immediately after running each highlight macro.

What about cross-tab dependency analysis?

Excel's Formulas > Trace Precedents and Trace Dependents features draw arrows showing which cells feed into a selected cell and which cells use it. Combined with the formula-highlight macro, you get a complete picture of the calculation chain.

Should I always run the audit on a copy?

Yes. Audit operations are easier to discard than to undo across many tabs. Save with _audit_copy suffix before starting.

Are macros safe to run on sensitive data?

Yes. The macros run entirely offline. No data leaves your laptop. For sensitive data (PII, financial data subject to compliance rules), this is the right tool versus any cloud-based audit tool.

What to do next

For data cleanup specifically (often a prerequisite for audit), The Complete Guide to Cleaning Up Data in Excel. For productivity macros to use after the audit, The Complete Guide to Excel Productivity Macros. For finance-specific audit context, Excel for Finance Professionals: The Practical Guide.