Microsoft Office Tutorials and References

In Depth Information

**Excel’s Auditing Tools**

Tracing cell relationships

To understand how to trace cell relationships, you need to familiarize yourself with the following

two concepts:

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 is not used

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

h
Cell dependents:
These are formula cells that 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.

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 each have a dependent cell (cell A4), and they’re all direct dependents.

Identifying cell precedents for a formula cell often sheds light on why the formula is not

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:

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. This technique is limited to

identifying cells on the same sheet as 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. This technique is limited to identifying cells on the

same sheet as 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.
Excel draws 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. Figure 21-10

shows a worksheet with precedent arrows drawn to indicate the precedents for the

formula in cell C13.

Formula Auditing

➜

➜