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).

Excel formula auditing toolbar

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.

Excel displaying formula precedents

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:

Excel displaying formula precedents with an off-sheet reference

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).

Excel Goto dialog not displaying the entire 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:

Screen-shot of reference utility

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.

Please enter your name (required):

Your e-mail address (mandatory; will be used to contact you):

Your question or remark: