Microsoft Office Tutorials and References
In Depth Information
Detecting and Correcting Errors in Formulas
Common Formula Error Messages
What Went Wrong
You tried to divide a number by a zero (0) or an empty cell.
You used a cell range name in the formula, but the name isn’t
defined. Sometimes this error occurs because you type the name
incorrectly. (Earlier in this chapter, “Naming cell ranges so that
you can use them in formulas” explains how to name cell ranges.)
The formula refers to an empty cell, so no data is available for
computing the formula. Sometimes people enter N/A in a cell as
a placeholder to signal the fact that data isn’t entered yet. Revise
the formula or enter a number or formula in the empty cells.
The formula refers to a cell range that Excel can’t understand.
Make sure that the range is entered correctly.
An argument you use in your formula is invalid.
The cell or range of cells that the formula refers to aren’t there.
The formula includes a function that was used incorrectly, takes
an invalid argument, or is misspelled. Make sure that the function
uses the right argument and is spelled correctly.
To find out more about a formula error and perhaps correct it, select the cell
with the green triangle and click the Error button. This small button appears
beside a cell with a formula error after you click the cell. The drop-down list
on the Error button offers opportunities for correcting formula errors and
finding out more about them.
Running the error checker
Another way to tackle formula errors is to run the error checker. When the
checker encounters what it thinks is an error, the Error Checking dialog box
tells you what the error is, as shown in Figure 3-10. To run the error checker,
go to the Formulas tab and click the Error Checking button (you may have to
click the Formula Auditing button first, depending on the size of your screen).
If you see clearly what the error is, click the Edit in Formula Bar button, repair
the error in the Formula bar, and click the Resume button in the dialog box
(you find this button at the top of the dialog box). If the error isn’t one that
really needs correcting, either click the Ignore Error button or click the Next
button to send the error checker in search of the next error in your worksheet.