How to Audit Someone Else’s Excel Workbook
Sooner or later, a workbook lands in your lap that someone else built — the analyst who left, the consultant whose contract ended, the manager who “just made it work.” Before you change a single cell, you need to understand what you’re holding, because inherited workbooks hide landmines: hardcoded overrides, broken external links, and sheets nobody knew existed. This guide walks through a forensic workflow I use every time I inherit a file, in the order that catches problems fastest.
Step 0: Work on a Copy, Never the Original
Save a copy with a clear name like Budget_Model_AUDIT_2026-06-11.xlsx and do all your poking in that file. Two reasons: Ctrl + Z doesn’t undo everything (deleting a sheet, for instance, is permanent), and you want the untouched original as your reference baseline when you document differences later.
Also check the file extension before you open it. An .xlsm or .xlsb file contains (or can contain) macros — open it with macros disabled first (Excel’s security warning bar blocks them by default) and review the VBA editor with Alt + F11 before you ever click Enable Content.
Step 1: Map the Workbook Structure
Your first job is a census: how many sheets, what each one does, and what’s hiding.
Unhide everything
Right-click any sheet tab and choose Unhide to see hidden sheets — the dialog lists every normally-hidden sheet at once. We cover the details in how to unhide a sheet in Excel. But that dialog won’t show very hidden sheets, which can only be set and cleared in the VBA editor. Press Alt + F11, click each sheet in the Project Explorer, and check the Visible property in the Properties window (F4). Anything set to 2 - xlSheetVeryHidden was deliberately concealed — flag it.
Then scan each sheet for hidden rows and columns: select all with Ctrl + A, then unhide rows with Ctrl + Shift + 9 and columns via Format > Hide & Unhide. The full walkthrough is in how to unhide rows in Excel. Gaps in row numbers or column letters are your visual tell.
Build a sheet inventory
For each sheet, record three things in a notes file or a new “AUDIT” tab:
| Sheet | Role | Risk notes |
|---|---|---|
| Inputs | Assumptions, rate tables | Some inputs typed directly into formula cells? |
| Calc_2024 | Monthly model | References “Calc_2023 (old)” — stale? |
| Dashboard | Output charts | Pulls from a hidden sheet |
| Sheet17 | Unknown, very hidden | Contains pasted payroll data — escalate |
A well-built workbook flows one direction: inputs → calculations → outputs. If references zigzag (outputs feeding back into inputs), note it now — that’s where you’ll find circular references later.
Check protection and properties
Locked sheets tell you what the builder considered fragile. If you need to inspect protected areas and have authorization, unprotect the sheet on your audit copy (Review > Unprotect Sheet). Also glance at the document properties (File > Info) — the original author, company, and last-modified metadata help you figure out who to ask questions of.
Step 2: See Every Formula at Once
Press Ctrl + ` (the backtick, above Tab) to toggle formula view, which swaps every cell’s result for its formula — the fastest way to spot patterns and anomalies across a whole sheet. More on this in how to display formulas in Excel.
What you’re scanning for:
- Inconsistent formulas in a range. If column F is
=D2*E2for 200 rows but row 147 is=D147*E147+500, someone made a manual “adjustment.” Excel often flags these with a green corner triangle, but don’t rely on it. - Constants where formulas should be. A column of formulas with three typed-in numbers in the middle is the classic inherited-workbook bug.
- Monster formulas. Anything over two or three nested functions deserves its own line in your documentation.
For a side-by-side view without toggling, put =FORMULATEXT(A1) in a helper column — the FORMULATEXT function returns the formula as a string, which you can then search with functions like =ISNUMBER(SEARCH("+",FORMULATEXT(A1))) to mass-detect suspicious patterns.
Step 3: Trace Precedents and Dependents
This is the core forensic skill. On the Formulas tab, the Formula Auditing group gives you:
| Tool | Shortcut | What it shows |
|---|---|---|
| Trace Precedents | Alt, M, P | Arrows pointing to every cell this formula reads |
| Trace Dependents | Alt, M, D | Arrows pointing to every cell that reads this one |
| Remove Arrows | Alt, M, A, A | Clears the arrows |
| Evaluate Formula | Alt, M, V | Steps through a formula one calculation at a time |
Start with the headline numbers — the totals on the dashboard or summary sheet — and trace precedents backward. A dashed arrow ending at a small grid icon means the precedent lives on another sheet or another workbook; double-click the dashed arrow to get a Go To list of those off-sheet references.
Two faster keyboard alternatives worth knowing:
- Ctrl + [ jumps directly to a formula’s precedent cells (selects them, even across sheets). Ctrl + ] does the same for dependents.
- F5 > Special > Precedents/Dependents selects all of them at once so you can shade them with a fill color.
Use Evaluate Formula on the three to five formulas that matter most — it executes the formula step by step, underlining each piece before resolving it, which is how you catch a VLOOKUP quietly matching the wrong row or an IF whose condition never fires.
Step 4: Hunt Hardcoded Values and Magic Numbers
Hardcoded constants inside formulas — =B4*1.0725 instead of =B4*(1+TaxRate) — are the most common defect in inherited workbooks because they silently go stale.
Three detection passes:
- Go To Special. Press F5 > Special > Constants, check only Numbers, and click OK. Excel selects every typed-in number on the sheet. Fill them yellow. Now do F5 > Special > Formulas and fill those a different color. Any yellow cell sitting inside what should be a formula block is a manual override.
- Search formula text. Use Find and Replace (Ctrl + F), click Options, set Look in: Formulas, and search for things like
*1.or specific suspect numbers (last year’s tax rate, an old headcount). This finds constants embedded inside formulas, which Go To Special can’t. - Check named ranges. Open the Name Manager (Ctrl + F3) and review every defined name. Names that refer to constants (
=0.0725) or to#REF!errors are both common. If the workbook has none, that itself is a finding — well-built models use named ranges for every key assumption.
Document each hardcoded value with its location, the number, and your best guess at what it represents. You’ll need the original author (or their manager) to confirm.
Step 5: Find External Links and Other Hidden Dependencies
Go to Data > Queries & Connections > Edit Links (grayed out if there are none). This lists every workbook this file pulls from, with status. Links to files on someone’s departed laptop, a renamed network share, or a personal OneDrive are time bombs — values freeze at whatever they were the last successful refresh. The full procedure, including links buried in names, conditional formatting, and chart series, is in how to find external links in Excel.
Don’t stop at Edit Links. Also check:
- Power Query connections (Data > Queries & Connections) — queries can point at files, databases, or web endpoints.
- PivotTable sources — click inside any PivotTable, then PivotTable Analyze > Change Data Source to see what it actually reads. Stale pivot caches can show data that no longer exists anywhere in the file.
- Merged cells, which break sorting, filtering, and fills — find the merged cells with Ctrl + F > Format before you reorganize anything.
Step 6: Validate the Key Outputs
Pick the three numbers the business actually uses from this workbook and verify each independently: rebuild the calculation with a fresh formula in a scratch area, or tie it to a source system report. Add quick sanity checks like =SUM(detail range)-summary_cell — anything nonzero is a finding. Check for error values workbook-wide with Ctrl + F searching for # with Look in: Values, and note whether errors are being masked by IFERROR wrappers — a pattern that hides real breakage more often than it handles legitimate edge cases.
If a previous version of the file exists, compare the two files for differences — diffs between versions often reveal exactly where manual overrides crept in.
Step 7: Document and Hand Off
Your audit is worthless if it lives only in your head. Create an AUDIT_NOTES sheet (or separate doc) with:
- The sheet inventory from Step 1, including anything that was hidden or very hidden
- Every hardcoded value found, with location and suspected meaning
- All external links and their status
- The formulas you validated and how
- A risk-ranked list of open questions for the original author
Date it, initial it, and only then start making changes — one at a time, with the original file intact as your baseline.
FAQ
What’s the fastest way to tell if a workbook has hidden sheets?
Right-click any sheet tab — if Unhide is clickable, hidden sheets exist. For very hidden sheets, press Alt + F11 and scan the Project Explorer: any sheet listed there but missing from the tab bar is very hidden.
How do I find hardcoded numbers inside formulas, not just typed-in cells?
Go To Special only finds constant cells. For numbers embedded in formulas, use Ctrl + F with Look in: Formulas and search for suspect values, or build a helper column of =FORMULATEXT() strings and filter or search those.
Trace Precedents shows a dashed arrow to an icon — what is that?
The precedent is on a different sheet or in another workbook. Double-click the dashed arrow itself to open a Go To dialog listing every off-sheet reference so you can jump to each one.
Should I fix problems as I find them during the audit?
No. Log everything first, then fix in a second pass, one change at a time, re-checking key outputs after each. Fixing mid-audit makes it impossible to distinguish your changes from the original state.
The workbook is password protected — can I still audit it?
If you have legitimate access rights, get the password from the owner or IT. Sheet protection (as opposed to file encryption) only blocks edits, not viewing — you can still read formulas in the formula bar and run most tracing tools unless the sheet was protected with hidden formulas enabled.
