94
Error Value
Excel Returns This Error When
Resolution(s)
#NULL!
You use an intersection operator
(which is a blank space between range
references) with two ranges that don't
actually intersect.
Either replace the intersection operator
with the intended operator or correct
the range references to two ranges that
intersect.
#DIV/0!
A formula or a function is trying to
divide some value by zero. This can
also be caused when a formula or
function is trying to divide by the value
in a blank cell.
Replace the value zero with the correct
numerical value, correct the cell
reference to a populated cell, or correct
the formula or function that is
returning the zero that Excel is trying
to divide by.
#VALUE!
When a formula or function is
referencing an incorrect data type. For
example, if a function is asking Excel
to multiply a numerical value by a text
label.
Correct the data entry causing the error
to the correct data type, or correct a
formula or function so that it returns
the correct data type.
#REF!
A formula or a function contains an
invalid reference. This typically occurs
when you delete a row or column
containing cells a formula or function
is referencing.
Restore the deleted cells or update the
references in the formula or function.
#NAME?
It does not recognize text that you
include in a formula or function.
Common causes of this are misspelling
a defined name, misspelling the name
of a function or a nested function,
excluding quotation marks around text
that requires them, and omitting the
range operator ( : ) in a range in a
function.
Correct the spelling of the defined
name or function, using the Paste
Name dialog box instead of typing
defined names, adding quotation marks
around text in function arguments, and
adding the range operator to range
references that are missing one.
#NUM!
An argument in a function that should
be a numeric value is some other data
type. This error can also be caused by a
calculation that returns a numeric value
that is too small or too large for Excel
to express.
Revise the function or the data feeding
the function to ensure that all
arguments are of the correct data type,
or revise the formula or function to
return a numeric value that Excel can
express.
#N/A
A function or a formula cannot access
a required value. Common causes of
this error include an invalid
lookup_value argument in Lookup
functions, trying to use Lookup
functions to search an unsorted dataset,
using array functions with different
sized arrays, and omitting required
arguments from functions.
Ensure that the lookup_value
argument in a Lookup function is valid,
sort the dataset you are searching with
a lookup function, ensure that all arrays
in array formulas are the same size, and
ensure that all functions include all
required argument.
Access the Checklist tile on your LogicalCHOICE course screen for reference
information and job aids on How to Search for Invalid Data and Formulas with Errors.
Microsoft® Office Excel® 2010: Part 3 (Second Edition) | 109
Lesson 5: Auditing Worksheets | Topic B
Do Not Duplicate or Distribute