Microsoft Office Tutorials and References

In Depth Information

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:

•
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 for-

mula.

•
Display the Go to Special dialog box
(choose Home
⇒
Editing
⇒
Find & Select
⇒
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.

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

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

•
Choose Formulas
⇒
Formula Auditing
⇒
Trace Precedents.
Excel draws arrows to indicate the cell's pre-

cedents. Click this button multiple times to see additional levels of precedents. Choose Formulas
⇒
Formula

Auditing
⇒
Remove Arrows to hide the arrows. Figure 22-10 shows a worksheet with precedent arrows

drawn to indicate the precedents for the formula in cell C13.

Figure 22-10:
This worksheet displays lines that indicate cell precedents for the formula in cell C13.

Identifying dependents

You can identify formula cells that use a particular cell in a number of ways: