Microsoft Office Tutorials and References

In Depth Information

**Tracing Cell Relationships**

Tracing Cell Relationships

In many worksheets, the cells might contain complex interrelationships. Trying to debug a

formula can be easier if you understand two key concepts: cell precedents and cell dependents:

h
Cell precedents:
Applicable only to cells that contain a formula, a formula cell’s

precedents are all the cells that contribute to the formula’s result. A
direct precedent
is a cell

that you use directly in the formula. An
indirect precedent
is a cell that isn’t used directly

in the formula, but is used by a cell that you refer to in the formula.

h
Cell dependents:
These formula cells depend on a particular cell. A cell’s dependents

consist of all formula cells that use the cell. Again, the formula cell can be a
direct

dependent
or an
indirect dependent
.

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 might want to see whether it has any

dependents.

Identifying precedents

You can identify cells used by a formula in the active cell in a number of ways:

h
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.

h
Display the Go To Special dialog box
(choose Home

Go To

Special). 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.

Editing

Find & Select

➜

➜

➜

h
Press Ctrl+[
to select all direct precedent cells on the active sheet.

h
Press Ctrl+Shift+[
to select all precedent cells (direct and indirect) on the active sheet.

h
Choose Formulas

Trace Precedents,
and Excel draws arrows to

indicate the cell’s precedents. Click this button multiple times to see additional levels of

precedents. Choose Formulas

Formula Auditing

➜

➜

Formula Auditing

Remove Arrows to hide the arrows.

➜

➜

Note that all of these techniques are limited to identifying precedent cells only on the sheet that

contains the formula. Figure 202-1 shows a worksheet with precedent arrows drawn to indicate

the precedents for the formula in cell C13.