Microsoft Office Tutorials and References

In Depth Information

**Understanding How Excel Handles Formula Errors**

#NAME?

This error message is telling you that Excel doesn’t

recognize some kind of text you have used in a

formula. Typically, this means you have either

misspelled a range name or referred to a

nonexistent range name. Or perhaps you misspelled

a function name, forgot to put a text string in

quotations, or left out the colon in a range

address (A2:G10).

#REF!

The #REF! error is telling you that a cell

reference used in a formula is invalid. This typically

happens right after you paste new cells (using

the Insert Copied Cells command) over the top

of the cells referenced in the formula, pushing

those cells down or right. It may also happen if

you use the Delete Cells command, which has

the reverse effect.

#N/A

This means that the value you want to use in a

formula isn’t there. This does not mean you are

referring to a blank cell—instead, it means that

you’ve tried to look up data in a table, and the

value you are trying to find isn’t there. For

example, if you type =VLOOKUP(“Alice”,B7:E44,3)

and “Alice” is not in the first column of the table,

Excel displays the error #N/A. You might also

get the #N/A error if you accidentally leave out

a required argument for a function, or attempt

to use invalid data as an argument (such as

entering a range address when the function is

expecting a single cell address).

#NUM!

You see this error when you use a non-numeric

argument in a function that is looking for a

value. The #NUM error also appears when the

formula is a number too large or too small for

Excel to represent.

#NULL!

Basically, this error is telling you that you left

out the comma when specifying more than one

range in a function. For example, if you type

=SUM(A1:A6 C1:C6), you get the #NULL error.

Curiously enough, if the ranges intersect in

some way, as in =SUM(A1:C6 B3:B10) and you

use a space, Excel thinks that you want it to add

only the cells in both ranges (B3:B6), and so you

don’t get an error.

More About VLOOKUP

If you want to learn more about the

VLOOKUP function, see Chapter 2, “Working

with Formulas,” for help.