Microsoft Office Tutorials and References
In Depth Information
Understanding How Excel Handles Formula Errors
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
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.
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).
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.
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.