Tip #65 - Excel Formula Auditing

100 Computer Tips in 100 Days

Tip #65 - Excel Formula Auditing
Tracing Dependents and Precedents

There are a couple of auditing tools available in Excel that are of great help when you are troubleshooting excel formulas, when you need to explain a spreadsheet to a co-worker or when you need to audit a spreadsheet for errors. The two primary tools are tracing dependents and tracing precedents. Both of these tools are designed to identify graphically, using a series of arrows, the relationship of the active cell with other cells in your workbook. When you trace dependents, all of the cells that are dependent on the contents of the active cell are identified. Conversely, when you trace precedents, all of the cells that feed into or precede the active cell are identified with arrows. When you trace precedents the active cell must contain a formula.

Formula Auditing from the active cell in D column.

To try this out, open one of your worksheets that has formulas. Click on a cell that is used in a formula. Click the Formula tab, Formula Auditing group, Trace Dependents (View menu, toolbars, Formula Auditing). You should see arrows pointing to the first level of dependency. Clicking on the Trace Dependents

option again will show the second level of dependency. Continue clicking to show additional dependent cells. If you were to print your worksheet with the dependency arrows showing, they will be visible. If you wanted to print your worksheet at this point it would be helpful to print the row/column headings (the row numbers and the column letters) to help you identify the location of the cells. To do this click on the Page Layout tab, Sheet Options group, Print Headings (File menu, Page Setup, Sheet tab).

Use the Remove Arrows option to turn off the display of the relationship arrows. If you are a veteran Excel user I guarantee you’ll use this tip a lot.

I've included a picture of some Formula Auditing Art! Yes, this is done by people who have a little too much time on their hands!

Formula Auditing Art