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




Custom Search