Formatting Tools | Formula Dependency Navigator | Compare Worksheets Tool
The Spreadspeed Dependency Navigator gives you insight to your complex Excel models, letting you visualize and trace through the call stack of your formula references with little effort. It opens in a task pane on the side of the worksheet. You simply click the Load Dependencies button and all the formulas on the worksheet are analyzed. Spreadspeed will generate treeview listings of both the formula dependents and the formula precedents. As you navigate through the treeview, using either your mouse or keyboard arrow keys, each cell in the treeview is activated on the worksheet (including off-sheet cell references), allowing you to view the formula.
Spreadspeed improves upon the built-in Excel formula traces. You don't need to hunt around for cells with formula dependents and precedents - Spreadspeed locates them for you. When you select an address from the listing, Spreadspeed can automatically display a full trace. When you click another address in the listing, the previous arrows are automatically cleared.
The Precedents view of the Formula Dependency Navigator is shown in the image below. While the Dependents Navigator can be thought of as a top down view, the Precedents navigator provides a listing from the bottom up. One major advantage of the precedents view is that it will identify references to other worksheets within the same workbook (identified by blue text) and references to other workbooks (identified by red text).
In the example below, we see that the trace arrows for the formula precedents are slightly different than the ones for the formula dependents. When a formula references a range, the range is outlined in a blue box. When the formula references a range in another worksheet or workbook, the arrow points to a little worksheet icon.
As with the Dependents trace, the Precedents view is useful for identifying problems in your model. Below, we've run a trace on cell D4, and it's fairly easy to see that there is an anomaly between columns B and C, with a box outlining rows 7-8 in column B and rows 6-8 in column C. In this case, the formulas in cells B9 and C9 are slightly different. Excel will typically not flag the first or last column with an Inconsistent formula error, making the Precedents navigator another important tool for checking errors in your models.
The Dependents view of the Formula Dependency Navigator is show in the image below. It provides a top down look at the formula trace, and is useful in determining both the complexity of the spreadsheet as well as identifying any possible problems. You can jump to a cell using the "Find range" lookup, and you can toggle between a single level trace arrow depth and all levels.
The example below shows how the Dependents view provides an snapshot of the spreadsheet's formula topography, which helps locate potential problems. In this case, we can see a regular pattern in columns B and C that stops in column D, pointing to a problem in cell D10. In cell D10, we discover that the expected formula has been overwritten with a value. Along with the content map created by the Visual Audit tool, the Dependency Navigator is a critical tool for ensuring the integrity of your spreadsheets.
Formatting Tools | Formula Dependency Navigator | Compare Worksheets Tool