Tracing Formulas in Excel 2007

By Claire Jarrett

In many Excel worksheets, formulas are used in a very straightforward way. The actual formulas and the cells they reference can be seen. Finding errors is easy because you're working with a limited number of cells. In more complex calculations, however, a formula in one cell may depend upon the results of a formula in another cell. There are no limits on the number of references a formula can support, so a formula can reference cells that use any number of other cells, which may contain additional references! To help you spot troubles with more complicated formulas, Microsoft offers some built-in utilities and features that provide assistance in tracing formulas in Excel 2007.

To begin tracing a formula in Excel 2007, right click in the toolbar area of Excel 2007 and select Formula Auditing. Excel will open the Formula Auditing toolbar.

Select the cell whose formula you wish to examine more closely. You'll be given the opportunity to trace "precedent" or "dependent" cells. A precedent cell is a component of the formula. A dependent cell uses the results of the formula you're auditing.

If you wish to examine the inputs to your formula, click Trace Precedents. Excel will create a set of blue lines that connect the formula to all of its input cells. If one or more of the input cells contains a reference, you may trace that also by pressing the Trace Precedents button again. You may continue to do this until all references have been identified and examined.

You can also see the precedents of a formula by double clicking the cell that contains the formula. All of its inputs will be colour-coded on the worksheet and in the formula bar. To exit, you may choose Esc or Enter. This method will not work if the formula contains multiple levels of precedents.

Similarly, you can trace the dependents of a formula. Select the formula in question and press the Trace Dependents button. Excel will draw blue lines between the formula and all of its dependent cells. If the dependent cells have dependents, you may press the Trace Dependents button again to show the second level of dependencies. You may continue to do this until you've examined all dependent relationships.

To remove precedent and dependent lines, press the Remove Precedent Arrows or Remove Dependent Arrows button. This will remove the tracing arrows one level at a time. If you wish to remove all arrows at once, press the Remove All Arrows button instead.

Notes for editors: Claire Jarrett is the managing director of Computer Training Solutions with centres in London, Bristol, Solihull and Reading. Computer Training Solutions offer Excel training or call 0800 019 6882