Excel Dependency Tree

Excel Dependency Tree

Excel doesn't show full Dependency Tree. You can explore it cell-by-cell using Formula Auditing commands. Show Precedents and Show Dependents commands point you to list of precedent\dependent cells in the spreadsheet.

Dependency Auditor adds its own set of formula auditing commands to work with Dependency Tree and research the calculations. It provides you set of very helpful graphical and navigation tools to go through dependency list in all directions - Precedents and Depdendents.

All commands are on the Dependency Auditor ribbon tab in the Excel 2007-2019 and in the Tools | Dependency Auditing menu in Excel 2000-2003. Also commands are accessible from the Right Click Menu in Excel worksheet window.

Some functions in the Excel Dependency Tree may produce dependencies at the moment of calculation. For example, OFFSET and INDIRECT functions construct reference to another cell depending on their arguments and return this reference as a result of the function call. This type of dependency is not shown by Excels formula auditing commands, however Dependency Auditor calculates result of the OFFSET and INDIRECT functions and add it to the list of dependent\precedents. This is highly important, because if you are using OFFSET function extensively, there is no clear dependency of output cells from the input cells in your model. Dependency Auditor easily finds and shows this dependency.

What can I do with Dependency Tree using this tool?

You can browse Dependency Tree in the both directions:

• Show Precedents command displays list of Precedents for the selected formula
• Show Dependents command displays list of Dependents

In the Dependents window you need to expand tree node to go to the next level of dependencies. You can go drill-down through all dependency tree in your workbook. And same for Precedents window.

Compare Dependency Trees

Use Pin and Tile Windows commands in the Dependency Auditor window to keep 2 or more dependency trees on the screen. Navigate through both trees, analyze cell values, formulas, export them or mark with color. This is only a part of the Dependency Auditor features.

Evalute Formula (Dependency Auditor)

This one of the most used commands. Dependency Auditor divides every formula into sub-expressions and displays result of the each one. You can browse through Dependent Cells and all expressions in all formulas. So, if something is wrong in your spreadsheet - just run this command and analyze every expression. You'll quickly find an answer how Excel calculates every number.

See Also More information on the feature in the Help Library: Evaluate Formula Command.

Show Inputs

This command analyzes Dependency Tree and gives you list of constant cells, which affects to the specific formula. If you need to find exact list of input cells for your formula - use Show Inputs command.

Trace Errors

In single click you can check your model for error values. Dependency Auditor shows all of them in one list and allows to trace each formula through it's Dependency Tree, to find source of the error. You should not spend time to analyze every argument on the next level in the Dependency Tree. Give this work to Dependency Auditor and get the results immediately. This command analyzes Dependency Tree and gives you list of constant cells, which affects to the specific formula. If you need to find exact list of input cells for your formula - use Show Inputs command.

See Also Help Library contains several topcis relative to the error tracing:

• Trace Error
• Trace Error command
• Show Errors

Save Dependency Tree on the worksheet or on the text file

How to share Dependency Tree you are working with to your co-workers? Or print the results? Or send specific calculation chain by e-mail?

You can save sub tree or path between nodes in the any of the Dependency Auditor windows in the following format:

• On the new worksheet
• In the text file

You can format both output files in the way you need and save, print or send report by e-mail.

Format cells

Dependency Auditor has a right-click menu command which formats selected branch (sub-tree) or path. This is quick and robust way to mark specific cells on the worksheet. You can back to them later using Filter by Color

Dependency Auditor has all you need to work with Excel Dependency Trees.

Armed with this tool you will be able to solve various problems in your spreadsheets.

Important If your Windows is 64-bit, you should still check bitness of your Excel.

If your Excel is 32-bit but Windows is 64-bit, you should install Dependency Auditor 32-bit!

Use xlCompare to compare Excel Worksheets and Visual Basic Forms and Macros

xlCompare is the most powerful and accurate Excel File comparison tool on the market. Built on the own Spreadsheet Engine, it quickly compares and merges Excel workbooks. Also, it has similar to Dependency Auditor functionality:

• Evaluate Formulas - divide formula expression into parts (including Array Formulas)
• Display indirect dependencies
• Browse Dependency Tree
• Highlight Error Values in workbook

As a spreadsheet developer you need a tool to compare different versions of your workbook, merge and resolve conflicts. xlCompare has Command Line mode and could be integrated with version control systems like SVN.