Tracing Precedents and Dependents in Excel 2007 Formulas
Excel 2007 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 B9 contains the formula =SUM(B3:B8). Cell B21 contains the formula =SUM(B15:B20). Cell E25 contains the formula =B9-B21. Note the following:
Cells B3:B8 are precedents of B9, but at the same time cell B9 is dependent on all the cells in B3:B8.
Cells B15:B20 are precedents of B21, but at the same time cell B21 is dependent on all the cells in B15:B20.
Cells B9 and B21 are precedents of E25, but at the same time cell E25 is dependent on cells B9 and B21.
Cell E25 is not dependent on 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 Show 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 is a value instead of a formula.
The Remove Arrows drop-down menu has three choices:
Remove Precedent Arrows
Remove Dependent ArrowsPrecedent and dependent arrows displayed in the worksheet.
Cells B9 and B21 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 B9 and B21 themselves then have lines coming from them and ending as arrow heads in cell E25. Therefore, B9 and B21 serve as precedents to cell E25. Or, said another way, cell E25 is dependent on cells B9 and B21.
Double-clicking a tracer arrow activates the cell on one end of the line. Double-clicking again activates the cell on the other end.