Microsoft Office Tutorials and References
In Depth Information
When you click the exclamation mark icon next to an error, you see a menu of
choices (as shown in Figure 17-7):
• Help on this error pops open Excel’s online help, with a (sometimes cryptic)
description of the problem and what could have caused it.
• Show Calculation Steps pops open the Evaluate Formula dialog box, where you
can work your way through a complex formula one step at a time.
• Ignore Error tells Excel to stop bothering you about this problem, in any
worksheet you create. You won’t see the green triangle for this error again (although
you’ll still see the error code in the cell).
• Edit in Formula Bar brings you to the formula bar, where you can change the
formula to fix a mistake.
• Error Checking Options opens up the Excel Options dialog box, and brings
you to the section where you can configure the settings Excel uses for
alerting you about errors. You can turn off background error checking , or change
the color of the tiny error triangles using the settings under the Error
Checking heading. (Background error checking is the feature that flags cells with tiny
green triangles when the cells contain a problem.) You can also tell Excel to start
paying attention to errors you previously told it ignore by clicking the Reset
Ignored Errors button. Underneath that button is a section named “Error
checking rules” that has a number of options that focus on specific types of errors. For
example, you can choose to have Excel ignore numbers stored as text, formulas
that ignore part of a range, and other situations that technically aren’t errors, but
usually indicate that you’ve done something you didn’t mean to. Excel always
reports genuine errors, like #VALUE! and #NAME? regardless of what choices
you make in this dialog box.
Note: Sometimes a problem isn’t an error, but simply the result of data that hasn’t yet been entered.
In this case, you can solve the problem by using a conditional error-trapping formula . This conditional
formula checks if the data’s present, and it performs the calculation only if it is. The next section shows
one way to use an error-trapping formula.
Table 17-2 lists the error codes that Excel uses.
Table 17-2. Excel’s error codes
You used the wrong type of data. Maybe your function expects a single value
and you submitted a whole range. Or, more commonly, you might have used
a function or created a simple arithmetic formula with a cell that contains text
instead of numbers.
Excel can’t find the name of the function you used. This error code usually
means you misspelled a function’s name, although it can indicate you used text
without quotation marks or left out the empty parentheses after the function
name. This error will also happen if you try to use a new Excel 2010 function in
older version of Excel.