Microsoft Office Tutorials and References

In Depth Information

**Troubleshooting Errors**

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

formula results.

#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

non-zero value.

Figure 9-25

Displaying the actual formulas.

Figure 9-26

Divide by zero error.

Keyboard Shortcut

#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.

Understanding Common

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

misspelling.

Other errors may appear in the formula result cell.

Following are a few of the more common error

messages.

180