Auditing Formulas
In this unit, you
will learn how to:
- Trace
formula precedents, dependents and errors
- Correct
errors in formulas
Trace Formula Precedents, Dependents and Errors
On the Formulas, Formula
Auditing group there are buttons that can help you trace errors and show
the cells that are referenced in a formula.
The Trace Dependents
button will display arrows to the other cells that depend on a given cell’s
data.
The Remove Arrows
button will remove both precedence and dependence arrows from your Excel
screen.
The Show Formulas
button will toggle between showing results and formulas in the worksheet. You
can also use the Ctrl + ‘pipe’ key
shortcut keys to do the same thing.
Correcting Errors in Formulas
The Error Checking option will check the entire worksheet
for formula errors. If any are found, you will be alerted with an error
checking dialogue (like the one below) that pertains to the specific error in
question.
You can also click the down arrow next to the Error Checking
button to see additional options.
The first option, Error
Checking, will perform the same action as if you clicked the Error Checking
button directly. The Trace Error
option will display arrows to the cells referenced in an incorrect formula. The
Circular References menu will
display cells that contain circular references, if there are any in the
worksheet.
The Evaluate Formula
dialog will help you analyse, interpret and correct formulas.
In this example cell B9 contains a formula linked to a cell
in the worksheet called Practice.
Clicking Step In
navigates to the Practice worksheet and Step
Out returns back the worksheet containing the link.
Evaluate displays
the value of the current cell rather than its formula.
0 Comments