Part II: Working with Formulas and Functions
Cell dependents These formula cells depend upon a particular cell. A cell’s dependents
consist of all formula cells that use the cell. Again, the formula cell can be a direct depen-
dent or an indirect dependent.For example, consider this simple formula entered into cell A4:=SUM(A1:A3)Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1,
A2, and A3 all have at least one dependent cell (cell A4).Identifying cell precedents for a formula cell often sheds light on why the formula isn’t
working correctly. Conversely, knowing which formula cells depend on a particular cell
is also helpful. For example, if you’re about to delete a formula, you may want to check
whether it has any dependents.Identifying precedents
You can identify cells used by a formula in the active cell in a number of ways:■ (^) Press F2. The cells that are used directly by the formula are outlined in color, and
the color corresponds to the cell reference in the formula. This technique is limited
to identifying cells on the same sheet as the formula.
■ Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special to display the Go to
Special dialog box. Select the Precedents option and then select either Direct Only
(for direct precedents only) or All Levels (for direct and indirect precedents). Click
OK and Excel selects the precedent cells for the formula. This technique is limited
to identifying cells on the same sheet as the formula.
■ (^) Press Ctrl+[. This selects all direct precedent cells on the active sheet.
■ Press Ctrl+Shift+{. This selects all precedent cells (direct and indirect) on the
active sheet.
■ Choose Formulas ➪ Formula Auditing ➪ Trace Precedents. Excel will draw arrows
to indicate the cell’s precedents. Click this button multiple times to see additional
levels of precedents.
■ (^) Choose Formulas ➪ Formula Auditing ➪ Remove Arrows to hide the arrows.
Identifying dependents
You can identify formula cells that use a particular cell in a number of ways:
■ (^) Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special to display the Go to
Special dialog box. Select the Dependents option and then select either Direct Only
(for direct dependents only) or All Levels (for direct and indirect dependents). Click
OK. Excel selects the cells that depend on the active cell. This technique is limited
to identifying cells on the active sheet only.
■ Press Ctrl+]. This selects all direct dependent cells on the active sheet.
■ (^) Press Ctrl+Shift+}. This selects all dependent cells (direct and indirect) on the
active sheet.