Dependency Navigator (Auditor)

The Dependency Navigator displays all the formula precedents and dependents in the active worksheet. The navigator opens in a task pane (side panel). It organizes the formulas by their call trace, and has separate tree views for the precedents and dependents. Where possible, the navigator will list the name of any Named Ranges used in formulas.

Fields

Dependents and Precedents tabs: The Dependency Navigator has separate tabs for the dependents and precedents views, with the operations for each handled separately.

Dependents Tree: Clicking on an item in the Dependents tree view will select the range and show the dependent arrows for the formula. Dependent ranges are displayed in a tree to show the trace hierarchy.

Precedents Tree: Operates in the same fashion as the Dependents tree view.

Expand All / Collapse All: Expand or collapse all the nodes in the active dependent or precedent list.

Find range: Selecting an item from the list will jump to it's first location in the listing

Trace arrow depth: If "All levels" is selected, the entire trace arrow route is shown. If "One level" is selected, the precedent or dependent arrows are show only for the selected range.

Hide Trace Arrows: Clears all the trace arrows on the displayed worksheet.

Load Time and Dependency Navigator Refresh

The average benchmarked times to load Dependents or Precedents is 30 seconds for every 1000 formulas. For example, a sheet with 10,000 formulas would load in 30 seconds, and a sheet with 100,000 formulas would load in 300 seconds (5 minutes). These are just benchmarks on our in-house systems, and your own load times may vary considerably based on the formula complexity and your system's hardware.

Due to the load times, the Dependency Navigator will not automatically refresh when you switch sheets or workbooks, or edit the formulas on the active sheet. You must click the Load Dependents / Load Precedents button(s) again.

Color Codes

Items that reference another sheet are colored blue.

Items that reference another workbook are colored red.

Spreadspeed uses the built-in Excel Precedents and Dependents references, which have some limitations. In some cases, a cell with a formula that references an external range will return the precedent range and sometimes it will not. In the cases where is does not, Spreadspeed will list the formula itself in the Precedents listing.

Redundant Listings

Because a cell can have more than one dependent, it can be listed multiple times in the Precedents view. Likewise, because a cell can have more than one precedent, it can be listed multiple times in the Dependents view.

Item Selection

Selecting an item in either the precedents or dependents listings will select that range on the worksheet and show the trace arrows. Selecting an item listing an external formula (instead of a range address) will cause the parent cell containing the formula to be selected instead of the precedent range used in the formula.