Microsoft Office Tutorials and References
In Depth Information
in the cells. Having the formulas display is a
wonderful tool for proofing and troubleshooting
formula errors in your worksheet.
Automatic Error Notification
Often when you enter a formula with an
error, Excel notifies you of the error and
attempts to correct the error for you or offers
suggestions for correcting the error.
Choose Formulas>Formula Auditing>Show
Formulas. Excel displays the formulas in each cell
(see Figure 9-25) instead of the formula result.
Each cell reference in a formula is assigned a color
and a corresponding colored box surrounds the
referenced cell whenever you click a cell with a
formula. If you print the worksheet while the
formulas are displayed, the formulas will print, not the
#DIV/0!: This means that the formula is
trying to divide by either an empty cell or
one with a value of zero (see Figure 9-26).
Make sure all cells in the division have a
Displaying the actual formulas.
Divide by zero error.
#REF!: This may mean that the formula
includes an invalid cell reference.
A shortcut key to turn formula display on
and off is Ctrl+` (grave accent).
#VALUE!: This error means the formula
references an invalid cell address. For example,
the cell you reference contains text but the
formula expects to find a value. You might
also see this error if you delete a value in a
cell that was used in a formula. Locate and
correct the invalid cell reference.
Formula Error Messages
There are a number of error messages that may
appear when you type a formula. Some are typing
mistakes and some may be a result of a cell value.
#NAME?: This error occurs when Excel
doesn’t recognize text in a formula, often
because of a range name or function name
Other errors may appear in the formula result cell.
Following are a few of the more common error