How to Audit an Excel Workbook You Didn't Build

First Impressions: The Quick Assessment

Before diving deep, gather basic information:

• File size: Under 5MB is manageable. 5-20MB suggests lots of data or objects. Over 20MB means complexity or bloat.
• Sheet count: Right-click any sheet tab → “Select All Sheets” to see the total. Or check the tab scrolling area.
• Calculation time: Press Ctrl+Alt+F9 (full recalculation) and time it. If it takes more than a few seconds, the workbook has computational complexity.
• External links: Data tab → Edit Links. Does this workbook pull from other files? Broken links are often the first problem.

Understanding the Structure

Scroll through the sheet tabs and look for patterns:

Color coding: Many people color-code tabs by purpose. Input sheets in green, calculations in blue, outputs in yellow. If colors exist, that's your first roadmap.

Naming conventions: Sheets named “Data_Sales”, “Data_Costs”, “Calc_Revenue”, “Output_Dashboard” reveal their purpose. Sheets named “Sheet1”, “Sheet2”, “Copy of Sheet1” suggest less organization.

Hidden sheets: Right-click any tab. If “Unhide” is available, there are hidden sheets. Unhide them all — they often contain important configuration or intermediate calculations.

Finding the Outputs

Every workbook exists to produce something: a report, a dashboard, a data extract. Find the outputs first, then work backwards.

Look for sheets with:

• Professional formatting (headers, borders, logos)
• Print areas defined (Page Layout → Print Area)
• Charts or dashboards
• Names like “Report”, “Summary”, “Dashboard”, “Output”

Once you identify the outputs, you know what this workbook is supposed to produce. Everything else exists to feed those outputs.

Tracing the Data Flow

From your output sheets, trace backwards using Excel's auditing tools:

Trace Precedents: Select a cell in your output, then Formulas → Trace Precedents. Blue arrows show which cells feed into this one. Double-click an arrow to jump to the source.

Evaluate Formula: Formulas → Evaluate Formula lets you step through a complex formula piece by piece, seeing how each part resolves.

Go To Special: Ctrl+G → Special → Formulas shows all cells with formulas. Select “Precedents” to highlight cells that feed into the current selection.

Work backwards from outputs to calculations to source data. Build a mental map of how information flows through the workbook.

Inventorying the Objects

Complex workbooks contain more than cells. You need to identify:

• Charts: What visualizations exist? What data do they reference?
• Pivot tables: Where does their source data come from? Are they set to refresh?
• Named ranges: Ctrl+F3 opens Name Manager. Review what names exist and what they reference.
• Data connections: Data tab → Connections. Does the workbook pull from external databases or files?
• Tables: Go to each sheet and check if data ranges are formatted as Tables (Ctrl+T). Tables have special behaviors.

Checking for VBA

Press Alt+F11 to open the VBA Editor. If there's code, you need to know what it does.

Check for modules in the Project Explorer. Look at ThisWorkbook for auto-run code (Workbook_Open events). Check sheet modules for event handlers that run when you change cells.

If there's significant VBA, understanding it is essential. The workbook might not function correctly without running macros, or macros might automate critical processes.

Document As You Go

As you explore, create the documentation that should have existed:

• A sheet map listing every sheet and its purpose
• The data flow from inputs to outputs
• Any manual steps required (copy/paste from external sources, running macros)
• Known issues or fragile areas

Future you — or whoever inherits this next — will be grateful.

Official Resources

• Trace cell relationships — formula auditing tools
• Manage workbook links — external reference handling