Formula reference auditing tool
This is an example of a tool I developed for a US-based company.
The tool is also commercially available. Check out The Ultimate Excel Formula Auditing Tool: RefTreeAnalyser for more information and a free demo)
As you probably know, Excel has built-in formula auditing tools (see the accompanying toolbar below).
With these tools, one can ask Excel to show arrows on the worksheet which point to precedent cells or dependent cells of the currently active cell. The example below shows that this is very useful.
However, if one wants to navigate the reference tree, things become cumbersome. For example, the figures in the range D2:D4 may also be calculated, using ranges on other worksheets. For example, highlighting cell D1 and pressing the show precedents button then gets you the arrow (the one with the tiny table) that indicates off-sheet references are involved:
Double clicking an off-sheet reference arrow will show a dialog box with all precedents on other worksheets. Unfortunately, when there are references involved to other workbooks, the entire path of a reference cannot be read (the box cannot be resized or scrolled, unless one clicks a reference, clicks in the "reference box" and uses the arrow keys to scroll the reference).
The tool I wrote for my customer eases this process considerably. It will show all precedents/dependents (or both) in a single (resizable) dialog screen in a well structured tree:
When one clicks any entry in the tree, the accompanying range of cells is selected automatically and if the reference is present in the formula of the starting cell, this part of the formula is highlighted.
Request more information
If you're interested in this tool, get in touch and we'll discuss the possibilities.