Microsoft Office Tutorials and References

In Depth Information

**Understanding How Excel Handles Formula Errors**

#VALUE!

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.

#DIV/0!

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

message.

Tip

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

column.

Using the IF Function

To learn more about the IF function, see

Chapter 2, “Working with Formulas,” for

help.