How to Trace Precedents and Dependents using Excel
Excel 2010 formulas may contain precedents and may serve as dependents to other formulas. Precedents are cells or ranges that affect the active cell’s value. Dependents are cells or ranges affected by the active cell. Use the Trace Precedents and Trace Dependents buttons in the Formula Auditing group of the Formulas tab to locate precedents or dependents for a cell that contains a formula.
A cell often serves as both a precedent and a dependent. The simple worksheet in the following figure includes values and calculations. Cell B8 contains the formula =SUM(B2:B7). Cell B17 contains the formula =SUM(B11:16). Cell E9 contains the formula =B8-B17. Note the following:
Cells B2:B7 are precedents of B8, but at the same time, cell B8 is dependent on all the cells in B2:B7.
Cells B11:B16 are precedents of B17, but at the same time cell B17 is dependent on all the cells in B11:B16.
Cells B8 and B17 are precedents of E9, but at the same time cell E9 is dependent on cells B8 and B17.
Cell E9 is not a precedent for any other cell.Understanding precedents and dependents of Excel formulas.
The next figure shows the same worksheet with precedent and dependent lines displayed. The methods for displaying these lines are shown in the Ribbon. Precedent and dependent lines are always inserted from or to the active cell. From the active cell:
To see what other cells are referenced in the active cell’s formula, click the Trace Precedents button.
To see which other cells contain a reference to the active cell, click the Trace Dependents button.
If you keep clicking the button, Excel will continue to go back (for precedents) or forward (for dependents) one more reference. For instance, the first time you click Trace Precedents, Excel shows you the direct precedents, those cells that are referenced by name in the formula. Click the button again and Excel reveals the precedents of those precedents. Keep clicking, and Excel keeps showing you how the cells are connected until you hit a range that contains values instead of formulas.
The Remove Arrows drop-down menu has three choices:
Remove Precedent Arrows
Remove Dependent ArrowsPrecedent and dependent arrows displayed in the worksheet.
Cells B8 and B17 have arrows that originate in the cells above. This shows the flow of precedents into the given cells. The arrow head rests in the cell that has the formula that contains the references to the precedents. On the other hand, cells B8 and B17 themselves then have lines coming from them and ending as arrow heads in cell E9. Therefore, B8 and B17 serve as precedents to cell E9. Or, said another way, cell E9 is dependent on cells B8 and B17.
Tips: Double-clicking a tracer arrow activates the cell on one end of the line. Double-clicking again activates the cell on the other end.