Computer Tips From A Computer Guy

How to trace formula dependents in Excel

Posted: 02 Oct 2009 03:01 AM PDT

Tracing Excel formula errors is tedious, if not guided by the built-in formula auditing tools. The formula bar itself is a good starting place to debug formula errors but when the formula gets longer and more complicated, a visual guide to trace the dependents is a great help.

You can use the Trace Precedents button on the Formula Auditing toolbar to trace all the generations of cells that contribute to the formula in the selected cell (something like tracing all the ancestors in your family tree). Many times, finding the original source of the formula leads you right to the source of all the error values in your spreadsheet.

To activate the tool, click Formulas on the ribbon and then Trace Precedents under the Formula Auditing panel:

In the Fibonacci sequence below, you would see the blue arrows showing the precedents of the result of the formula:

Another example is shown below where the B1 cell is a result of summing A1 and A2:

Now to see the dependents of a value, click a cell that you want to audit then click the Trace Dependents value. If the cell is used by other cells in formula, the arrows will point the to cells that depend on the cell.

To clear the arrows, click the Remove Arrows button and you should see the arrows disappear.

Use the Formula Auditing tools to trace dependents and precedents of your Excel formulas. They are very handy when the cell formulas get very complicated. As of Office 2007, the arrows don't get automatically refreshed when you change the formula (they get cleared out) so you will need to click the trace buttons again to generate the arrows.