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
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.
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.
Search JabSto ::

Custom Search