Microsoft Office Tutorials and References
In Depth Information
Understanding How Excel Handles Formula Errors
When this error message appears, Excel is telling
you that you are trying to use the wrong type of
data for your formula. For example, the formula
might use a function that requires a text
argument, and you’ve pointed it to a cell that contains
a number. Or the function might be looking for
a single cell argument, and you entered a range.
In any case, to eliminate the error, edit the
formula so that it uses the type of data the function
is looking for.
Setting the Error Options
You determine which errors Excel checks for
by selecting them from the Excel Options
dialog box, as described in the section “Telling
Excel Which Errors to Flag.”
Understanding Formula
Error Messages
Excel has a list of specific formula errors that
it will flag if you make them, by displaying
a message in the result cell. All of these error
messages start with a pound sign #, as you will
soon see.
When you see this error message, the formula is
attempting to divide some number by zero. For
example, in the formula =A5/B5, if B5 is zero
or empty, the result will be the #DIV/0! error.
Typically, this error occurs not because you
actually wanted to divide by zero, but because
the cell you are referring to is currently empty.
You can just live with the error flashing you in
the face until you enter values in the worksheet,
or you can tell Excel to ignore this particular
error by changing your options (as explained in
the section “Telling Excel Which Errors to Flag”).
But if you share this workbook with someone
else, and Excel is not set up to ignore this error,
the error will display in the result cell—perhaps
causing confusion for your colleague. One way
to ignore this error is to use the IF function, like
this: =IF(B5=0,“”,A5/B5).
If this error appears in a cell, Excel is not telling
you that a formula contains an error. Instead, it’s
simply letting you know that this particular cell
contains data that’s wider than the cell. In most
cases, all you have to do is widen the column to
correct the problem and eliminate the error
To quickly widen a column to just the
right width, move the cursor to the right
edge of the column. Double-click this
right edge, and the column automatically
expands to fit the widest entry in that
Using the IF Function
To learn more about the IF function, see
Chapter 2, “Working with Formulas,” for
Search JabSto ::

Custom Search