Tools and Utilities for Excel | The Formula Trace Tool
The Formula Trace Tool is a free utility designed to make the process of auditing an Excel spreadsheet, workbook, or series of linked workbooks much easier. The tool allows the auditor to click on precedent or dependent cell references in a listbox as opposed to clicking on audit arrows or performing a manual search. This functionality allows the auditor to move through formulas much more quickly and efficiently.
Overview
To use the tool, select a single cell containing a formula. Go to the ribbon and select the Formulas tab:
Click on the Trace Formulas button on the far right side of the tab (the control with the red diamond).
The Formula Trace Tool should appear on your screen.
To move to a precedent cell, click on a cell reference in the window. Activate the "Trace Precendents" option. The active cell address is always preceded with an "o", for "origin". Precedent cells are preceded with a "p".
The screenshot below shows the Formula Trace Tool. The user has activated the tool after selecting cell C21. Each precedent cell address linked to cell C21 is shown in the listbox. The user can click on each cell address in the listbox to automatically go to that cell address.
To move to a dependent cell, click on a cell reference in the window. Activate the "Trace Dependents" option. The active cell address is always preceded with an "o", for "origin". Dependent cells are preceded with a "d".
The Formula Trace Tool senses if there are links to outside workbooks. If no links are found, only the sheet names and cell addresses in the active workbook will appear in the listbox. If links are found, the workbook name will also appear in the listbox. You must open a linked workbook to audit it.
Installation Instructions
The Formula Trace Tool is designed to work with Excel 2007. It will not work with any earlier versions of Excel or Excel for McIntosh. It has not been tested with Excel 2010.
To install the Formula Trace Tool you must download it from this site to your computer. To download the tool, click on the link below and follow the instructions.
Note: For all security warnings, hit "Run". You can download the tool to a directory that you choose or use the default directory.
Click on the green arrow to
download the Formula Trace Tool
Once the tool is downloaded to your computer you must load it into Excel as an Add-In. To do so:
- Open Excel 2007.
- Click on the Microsoft Office Button. It is located at the upper left side of the Excel interface.
- Select Excel Options -> Add-Ins -> Browse -> and find the location of the Formula Trace Tool (the file name is FTT201003.xlam). If the tool is loaded into the default directory, the directory location will look something like C:\Program Files\Formula Trace Tool\.
- Click on the FTT201003.xlam file to load it into your Add-Ins. The Formula Trace Tool should now appear in the "Add-Ins available:" list box in the Add-Ins dialog box.
- Put a check into the checkbox for the Formula Trace Tool.
The Formula Trace Tool is now installed as an Excel Add-In and will be available within the Formulas group each time you start Excel.
Tracing Precedent Cells
Precedents are cells that are referred to by a formula in another cell. To trace precedent cells, put your curser into any cell that contains a formula. To activate the tool, go to the Formulas group and click on Formula Trace Tool icon (orange diamond). A reference to each immediate precedent cell will appear in the listbox. Precedent cells appear with a "p" (for Precedent) before the cell address in the listbox while the activecell is flagged with an "o" (for Origin). Click on a reference to move to that cell.
Tracing Dependent Cells
Dependents are cells that are referred to by a formula in another cell. To trace precedent cells, put your curser into any cell that contains a formula. To activate the tool, go to the Formulas group and click on Formula Trace Tool icon (orange diamond). A reference to each immediate dependent cell will appear in the listbox. Dependent cells appear with a "d" (for Dependent) before the cell address in the listbox while the activecell is flagged with an "o" (for Origin). Click on a reference to move to that cell.
Setting the Number of Iterations
The tool performs a looping process to move create the list of precedent or dependent cells. The number of times that the looping process needs to occur are called ""iterations"". The default amount of iterations is set to 25 and the maximum amount is 500. A small number of iterations is usually sufficient but a higher amount may be needed for more complex tasks.
To set the number of iterations, choose a setting between 25 and 500.
The Step Function
The "Step" button allows you to continue to move through a trail of precedent or dependent cells. It reactivates the tool on any cell reference that you have previously moved to.
The Clear Function
The "Clear" button eliminates the audit arrows from the active sheet.
Error Handling
The Formula Trace Tool is designed not to work under certain conditions. Those conditions are:
- The active sheet is protected. Sheet protection for the active sheet must be disabled to use the tool.
- The active sheet is grouped with other sheets. Ungroup the active sheet to use the tool.
- The tool will only work with a range selection. It will not work if multiple ranges are selected.
The Help Button
The Help form provides the full file path to the directory location where the Formula Trace Tool is installed.
Similar Tools and Credits
- The idea for the Formula Trace Tool is based on Arron Blood's Explode Add-in. To my knowledge, Explode was the first to utilize a listbox approach to display cell references. I created the Formula Trace Tool because I wanted a less complex version of Explode.
- JKP-Ads offers the RefTreeAnalyzer for a small fee. I recommend this tool if you need to record an audit trail and/or need a more detailed display of cell references.
