Why RefTreeAnalyser?

Pages in this article

Choose a starting point for your workbook review. These examples show where RefTreeAnalyser can help; the visual tour shows the tools and their output.

Understand a workbook you have inherited

A result on a summary sheet may depend on calculations spread across many worksheets. Start with that formula and follow its precedents to see where the inputs come from. Then explore dependents to find calculations that use the result.

For example, follow a forecast total back to the worksheets containing its assumptions. Click a reference in the tree to select its cell or range in Excel.

RefTreeAnalyser reference tree showing the dependents of a selected cell

How to explore the reference tree.

Investigate an unexpected result

When a total looks wrong, inspect the referenced cells and their values rather than jumping between formulas without a record of your route. For a formula returning an Excel error, use Trace Errors to investigate its inputs. To find error results throughout a workbook, use Report Errors instead.

Use Visualize Precedents when an on-sheet view of the calculation is easier to follow.

Note that a technical error report cannot determine whether the workbook's business assumptions are correct.

Investigate an error or create an error inventory.

Check what else uses a cell

Before changing a range, check more than its formula dependents. Charts, PivotTables and controls may use it too. Data-validation and conditional-formatting formulas can also contain references that are easy to overlook.

For example, check whether a chart uses a range before replacing its contents. The Object References window helps you investigate these relationships. To focus on a specific cell, check the "Objects" check box in the trace dependents dialog.

Inspect object references.

Investigate circular calculations

In Excel for Windows, the circular-reference tool presents the chains it finds in a navigable tree. Follow the cells in a chain to decide whether the circular calculation is intentional or needs correcting.

This feature is not available on Mac. Read the circular-reference instructions.

Understand relationships between worksheets

Before reviewing individual formulas, get an overview of which worksheets supply data to others. The inter-sheet report can help you identify input, calculation and output sheets and choose where to investigate next.

See the worksheet-dependency report.

Make a large workbook easier to navigate

Create a linked table of contents for the workbook's sheets, tables, charts, PivotTables and other supported objects. This gives colleagues a starting point without requiring them to search through every tab.

The table of contents is a worksheet report, not a live view. Run the command again when you need to refresh it. Create and update a table of contents.

Keep a record of your investigation

Write the current reference analysis to a report worksheet so you can review the relationships outside the tracing window.

The Formula Report groups formulas that share the same copied pattern, helping you review repeated calculations without reading every cell individually. Read about the Formula Report.

Audit your workbook and check formula revisions

Use Audit Workbook to look for formula issues, data-validation and conditional-formatting rule issues, and missing or inaccessible linked files. Separate review warnings from confirmed defects: an unusual formula may be intentional.

Before revising formulas, save a snapshot. Afterward, compare formulas with that snapshot to review additions, changes and removals. This is a formula comparison, not a comparison of values, formatting or every workbook object. Inserted or deleted rows and columns can cause shifted formulas to appear as removed and added.

Interpret audit findings or save and compare formulas.

Investigate slow calculations

Use worksheet statistics to identify sheets worth investigating, then time a selected range to examine a particular calculation. Timing depends on Excel's state and other open workbooks; it is a measurement to investigate, not an automatic optimization.

Review worksheet statistics or time a selection.

Try it with your workbook

Download the demo, review its restrictions and compatibility, then follow the getting-started guide.

Download the free demo

Choosing a starting point

For a single formula, start with Find Precedents. For a workbook-wide inventory of error results, choose Report Errors. For broader rule checks, choose Audit Workbook. The manual explains the scope and limitations of each command.

Comments

Loading comments...