Tracing precedents and dependent in an Excel table
Precedents: These are the ranges or cells that affect the value of the active cell.
Dependent: These are the ranges or cells that are affected by the active cell.
The Formula Auditing group located in the Formulas tab features the Trace Precedents and Trace Dependent options to locate the precedents or the dependent for a particular cell that houses the formula.
A cell can serve as both a precedent as well as a dependent, and in most cases it does. In the following figure you will see a simple worksheet that includes the calculations based on the values. Take a look at B8 cell which has the formula SUM(B2:B7). B17, on the other hand has another formula, SUM(B11:B16). And then there is the cell E9 which has the formula B8-B17.
Here are some points to note:
- Now you can see that B2:B7 are the precedents of the cell B8. However, you can also note that B8 is also the dependent of the B2:B7.
- B11:B16 are the precedents of the cell B17. However, you can also note that B17 is also the dependent of the B11:B16.
- B8 and B17 cells are the precedents of the cell E9. But, you can also see that E9 is also the dependent of the B8 and B17 cells.
- E9 cell is not the precedent to any other cell here.
Understanding precedents and dependent for the Excel formulas.
In the next figure you can see that the same worksheet displaying the lines for precedent and dependent. The Ribbon actually shows the display methods for these lines. These lines, as you can see, are always inserted either from the active cell or to it.
From the active cell:
- Click on the Trace Precedents button to find out the other cells that are being used in the formula for the active cell.
- In the same manner, if you want to see the cells that include the reference to the cells that are active you can click the Trace Dependent option.
With every click, Excel will either take you one reference back or forward for precedents and dependent respectively. For example, when you click on the Trace Precedents the very first time, Excel will point out the direct precedents (these are the cells that are cited by the names in the given formula. When you will click the button one more time you will be able to see the precedents of these precedents. If you will keep on clicking Excel will take you to the range that contains the actual values and not the formulas.
There are three choices with the drop-down Remove Arrows:
- Remove Arrows
- Remove Dependent Arrows
- Remove Precedent Arrows
Precedent and dependent arrows in a worksheet
You can see that B8 and B17 cells have arrows with the heads resting in them. These arrows originated in one of the cells above them. That depicts how the precedents have flown into the cells of interest. These cells have the formula with references to the precedents. You can also see that the arrows are also originating from B8 and B17 cells and ending at E9 cell. This means that these cells are the precedents to E9 cell. You can put it the other way and say that E9 is the dependent of these two cells.
Note: Alternately double-clicking on the arrow activates the cells on the either end of it.
