Weekly Computer Tip # 319
19 September 2010


Trace the relationships between formulas and cells
(Excel 2000-2007)

If you work with somebody else's workbooks or you haven't opened one of your own worksheets for a while it can be difficult to know what's going on.

Many many moons ago I told you how you can view (and print) all formulas (see tip_238.php). But you can even take it a step further and see at a glance where the data is coming from and going to, which can help you to find errors that may occur in your worksheets due to incorrect formulas.

Here's how: (in Excel 2003)

  1. Select the appropriate cell. (If you are tracing a precedent - cells that provide data to a formula - the cell must contain a formula.)
  2. Choose Tools, Formula Auditing, Trace Precedents or Tools, Formula Auditing, Trace Dependents (formulas that reference a particular cell).

Here's how: (in Excel 2007)

  1. Select the appropriate cell. (If you are tracing a precedent - cells that provide data to a formula - the cell must contain a formula.)
  2. Click on the Trace Precedents or Trace Dependents (formulas that reference a particular cell) button in the Formula Auditing group on the Formulas tab.

When you use any of the above commands or buttons, a tracing arrow is displayed, connecting the selected cell to each of its precedents or dependents, assuming, of course, that the cell has a precedent or dependent. When a precedent or dependent of a particular cell appears in another worksheet or in another workbook, an black dotted line with an arrow pointing from or to a worksheet icon will appear. To open the worksheet or workbook it refers to double-click the dashed arrow to display the Edit > GoTo dialog box. From there, simply select the particular reference and press OK and the corresponding workbook/worksheet will open.

To remove tracing arrows from a worksheet choose the Tools, Formula Auditing, Remove All Arrows command, or click on the Remove Precedent Arrows, Remove Dependent Arrows or Remove All Arrows button on the Formula Auditing toolbar (in Excel 2003) or click on the Remove Arrows button in the Formula Auditing group on the Formulas tab, or click on the arrow next to the Remove Arrows button and click on Remove Precedent Arrows or Remove Dependent Arrows.

By the way if you often inherit somebody else's workbooks and would like to better understand what's going on, why not sign up for our half day Excel Further Use course; see msexcfu.html for details.

.