Excel: audit a large workbook with eight tools

Key insights

• Workbook Statistics summarizes the active sheet and the full workbook; Navigation provides a view of sheets and ranges.
• Focus Cell highlights the active row and column, while Freeze Panes keeps selected rows and columns visible as you scroll.
• Trace Precedents follows inputs to a formula; Trace Dependents shows cells affected by a value.
• Watch Window tracks selected cells while you edit values elsewhere in the workbook.
• Go To Special can select row or column differences to help locate inconsistent formulas or hard-coded values.
• Copilot can be prompted to explain a workbook, but availability depends on the Microsoft 365 plan and organization settings.

Excel’s workbook review and formula-auditing tools can help you map a large workbook, follow its calculations and find inconsistent formulas without starting by rebuilding it. They are useful for analysts, finance teams and anyone who has inherited a workbook and needs to understand what its numbers depend on. Leila Gharani [MVP] demonstrates these tools using a multi-sheet financial workbook.

What it does

The tools address different parts of a workbook audit: understanding the workbook’s layout, navigating large sheets, tracing calculation logic, monitoring important results and checking for formula exceptions. Workbook Statistics provides a quick summary of the current sheet and workbook, while Navigation brings sheets and ranges into a single view. The Navigation pane is an additional tool in the walkthrough, alongside the eight main tools.

For worksheet navigation, Focus Cell visually marks the active row and column. Freeze Panes keeps rows and columns in view while you scroll. For calculations, Trace Precedents identifies cells feeding a selected formula, and Trace Dependents identifies cells that rely on a selected cell.

Watch Window keeps chosen cells and their values visible while you work elsewhere. Go To Special can select differences within a row or column, helping surface a hard-coded number or a formula that differs from its neighbors. Copilot offers another way to explore the workbook by asking it to explain how source data flows into management reporting.

Who it is for and what you need

The navigation, formula-auditing and review techniques are relevant to Excel users inspecting an existing workbook. The demonstration uses a workbook with multiple sheets, calculations and financial reporting, but the source does not specify a required Excel edition or license for most of the tools.

The description identifies Focus Cell and the Navigation pane as available in Microsoft 365. It says Copilot availability depends on the Microsoft 365 plan and organization settings; the transcript describes the Copilot example as requiring a Copilot license. It does not identify a specific license name, rollout stage, region or additional prerequisites.

The transcript also cautions that the Ctrl+[ and Ctrl+] shortcuts may not work as expected on some European keyboard layouts. The menu commands remain an option when a shortcut is awkward or unavailable.

How it works

1. Map the workbook. On the Review tab, select Workbook Statistics to see information about the current sheet and the entire workbook. To inspect sheets and ranges in one view, use View > Navigation, then select an item to jump to it.
2. Make large sheets easier to read. Use View > Focus Cell to highlight the active row and column; the demonstration changes its color. To keep the first column and top row visible, select cell B2, then choose View > Freeze Panes > Freeze Panes. Excel freezes the rows above and columns to the left of the selected cell. Choose Unfreeze Panes to reverse that setting.
3. Follow a calculation upstream or downstream. Select a formula cell and choose Formulas > Trace Precedents to show its inputs. Repeat the command to follow additional formula steps. A dashed arrow can point to a reference on another sheet; double-clicking it opens a list of ranges involved. Choose Remove Arrows when finished. To see what relies on a cell, select it and choose Formulas > Trace Dependents.
4. Monitor important results while editing. Open Watch Window, choose Add Watch, and select one or more cells. The window displays the selected cells and their values as you make changes elsewhere. Naming cells in the Name Box can make entries easier to identify. Select an entry and choose Delete Watch to remove it.
5. Check for formula exceptions. Select the data range, then use Home > Find & Select > Go To Special. Choose Column differences for a vertical range or Row differences for a horizontal one. The demonstration colors the selected cells, enables filtering, and filters by color to inspect the flagged items. It also shows a spilled formula as an alternative to individual formulas in a column: its spill cells cannot be edited individually, and overwriting one produces a #SPILL! error.
6. Ask Copilot to explain the workbook. Open Copilot and ask for a concise explanation of how source data leads to management reporting. The example uses the response to identify where master data and financial inputs sit and where project calculations and summaries appear.

Limitations and open questions

These tools help investigate a workbook; they do not establish that its logic or results are correct. Trace arrows reveal cell relationships, and Watch Window shows how selected values respond to an edit, but users still need to judge whether the formulas and resulting figures match the intended business rules.

Go To Special highlights differences, not necessarily errors. A hard-coded number or distinct formula may be intentional, so inspect each flagged cell before changing it. The demonstration corrects examples by filling a formula down, but does not provide a general repair rule for other workbooks.

The transcript notes that Copilot took about two minutes to return its workbook explanation in the example. It does not describe the specific Copilot license, required workbook storage location, supported Excel versions or data-handling details. Those points should be checked against the organization’s plan and settings rather than assumed from the demonstration.

For keyboard users, Ctrl+[ jumps to precedents and Ctrl+] to dependents in the example, but the publisher notes that keyboard layouts can affect these shortcuts. Use Trace Precedents or Trace Dependents from the Formulas tab if the shortcuts do not work.

Sources

• Leila Gharani’s Excel workbook auditing demonstration

Frequently asked questions

How do I find formulas that differ in an Excel column?

Select the range, open Home > Find & Select > Go To Special, and choose Column differences. For data arranged horizontally, choose Row differences. Inspect flagged cells before editing because a difference may be intentional.

How can I see which cells feed an Excel formula?

Select the formula cell and choose Formulas > Trace Precedents. Run the command again to follow further calculation steps; use Remove Arrows to clear the displayed arrows.

Does Excel Copilot require a specific license to explain a workbook?

The source says Copilot availability depends on the Microsoft 365 plan and organization settings and describes the example as requiring a Copilot license. It does not identify a specific license name.

How do I keep headers visible while scrolling in Excel?

Select the cell below and to the right of the rows and columns to keep visible, then choose View > Freeze Panes > Freeze Panes. In the demonstrated example, selecting B2 keeps the top row and first column visible.