It
is often difficult to find the cause of an error in a
formula, especially if you were not the creator of the
worksheet. With Excel's auditing features you can
quickly trace a formula to determine the error in the
formula or the thought process of the creator of the
worksheet.
With auditing tracers, Excel provides a visual way
for you to find precedents (cells that are
"referred to" in the current formula),
dependents (cells that are dependent on the current
formula), and errors related to any cell on your
worksheet. The tracer command draws arrows from the
active cell to its precedents, its dependents, or to the
possible source of an error in the active cell.
First let's define terms so you understand what a
precedent and dependent is and then we will give you the
step-by-step instructions on how to use the tracer
arrows.
Defining terms
Precedents: Cells that are "referred to" in
the current formula. For example, if the active cell is
B5 and the formula in cell B5 is =B2+B4, then the
precedents for cell B5 would be B2 and B4.
Dependents: Cells that is dependent on the current
cell. For example, if the active cell is B2 and the
formula in cell B5 is =B2+B4, then B5 would be a
dependent for both B2 and B4.
Step-by-step Instructions
QuickSteps to Trace Precedents
- Make sure the Auditing toolbar is displayed: Click
on View, Toolbars, Auditing. If Auditing is not in
the list then click on Customize then click on
Auditing, click on ok.
- Select the cell that contains the formula you want
to trace
- Click on the Trace Precedents icon on the Auditing
Toolbar
The cell with the arrowhead identifies the cell
(formula) being traced. A dot in a cell identifies that
cell as being part of the formula, a box around cells
identifies a range of cells as part of the formula.
QuickSteps to Remove Precedent Arrows
- Make sure the Auditing toolbar is displayed
- Select the traced cell (the one with the arrowhead
in it)
- Click on Remove Precedent Arrows icon in the
Auditing toolbar
The appropriate arrow(s) are removed.
QuickSteps to Trace Dependents
- Make sure the Auditing toolbar is displayed
- Select the cell whose dependent cells you want to
trace
- Click on Trace Dependents icon in the Auditing
toolbar
The dot in a cell identifies the cell (formula) being
traced. An arrowhead in a cell identifies that cell as
being dependent on the traced cell, a box around cells
identifies the range of cells being dependent on the
traced cell.
QuickSteps to Remove Dependents Arrows
- Make sure the Auditing toolbar is displayed
- Select the traced cell (the one with the dot in
it)
- Click on Remove Dependents Arrows in the Auditing
toolbar
The appropriate arrow(s) are removed.
QuickSteps to Trace Errors Using the Auditing
Toolbar
- Make sure the Auditing toolbar is displayed
- Click on the cell that contains an error value
- Click on the Trace Error icon in the Auditing
toolbar
Blue or solid arrows display precedents of the first
formula with the error. Red or dotted arrows display the
information flow of formulas generating the errors.
QuickSteps to Removing all Tracer Arrows
- Make sure the Auditing toolbar is displayed
- Click on the Remove All Arrows icon in the
Auditing toolbar
All arrows are removed.
With the steps above you will be able to quickly find
the errors in you worksheet, whether it is a small or
large worksheet. You will also be able to understand and
concur worksheets given to you by another person.
Keywords: tracer arrows, tracing formulas,
formulas, excel, errors
