Excel Dependency Tree

How to work with Dependency Trees using Dependency Auditor

Excel Dependency Tree

Excel Dependency Tree is an internal structure which is used to build calculation chains and informs Excel which cells should be recalculated when something is changed in the spreadsheet.
Recalculation may be triggered in such cases:
  • User entered new data into cell
  • Excel commands are used which instructs Excel to recalculate all workbook or only a specific range
  • When workbook is saved, if Recalculate Before Save option is turned on.
Volatile functions force recalculation in case of any change in the worksheet:
  • New row or column is inserted
  • Worksheet is renamed
  • Autofilter actions are performed
  • ...

Dependency Auditor for Excel 32-bit

File Name: xda.msi

This setup is for Excel 32-bit

Download Dependency Auditor 32-bit

Dependency Auditor for Excel 64-bit

File Name: xda64.msi

Download this file ONLY if you have Excel 64-bit installed!

Download Dependency Auditor 64-bit

Dependency Tree is not shown by Excel, you can one use Formula Auditing commands to work with it. 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.

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

What is the difference between Dependency Auditor and Excels commands?

Dependency Auditor shows you dependency tree in a way completely different from Excel and another tracing tools.
It shows you list of Precedents\Dependents in the hierarchical tree form. This is native representation of the Excel Dependency Tree:

Excel Dependency Tree

You can expand levels in this tree and go through all dependents\precedents in your workbook starting from the specific cell. On the image below you can see how Dependency Auditor represents Excel Dependency Tree.

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:

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.

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.

Dependency Auditor - evaluate formula

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:

Dependency Auditor - trace error

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: 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 - context menu

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.

If you need a feature which is not currently present in the software - let us know and we'll add it in the next version of the product for FREE.

Dependency Auditor for Excel 32-bit

File Name: xda.msi

This setup is for Excel 32-bit

Download Dependency Auditor 32-bit

Dependency Auditor for Excel 64-bit

File Name: xda64.msi

Download this file ONLY if you have Excel 64-bit installed!

Download Dependency Auditor 64-bit

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!