Types of Excel Formula Error
If your Excel Formula or Function contains an error that Excel can detect, you may be presented with an
error message (eg. #VALUE!, #N/A), which provides you with information about your Excel formula error,
and therefore assists you in identifying and fixing the problem.
The table below provides a quick reference guide of what each of the different error messages means.
Further information and examples are provided further down the page.
Arises when you specify an intersection of two ranges that don't intersect
Your formula is attempting to divide by zero
One of the variables in your formula is of the wrong type
The formula contains an invalid cell reference
Excel doesn't recognise text in a formula
Excel has encountered an invalid number
Indicates that a value is not available to the formula
The Excel #NULL! Error
Excel produces the
#NULL! error when you attempt to intersect two ranges that don't intersect.
For example, the formula
=SUM(B1:B10 A5:D7) will return the sum of the values in
the range B5:B7 (the intersection of the ranges B1:B10 and A5:D7).
However, if you entered the formula
=SUM(B1:B10 C5:D7) you would get the
because the ranges B1:B10 and C5:D7 do not intersect.
This can be corrected by reviewing your formula, and either changing the variables to ensure you get
a valid intersection or using the
Excel Iferror function to identify a null range
and take alternative action. For example :
=IFERROR( SUM(B1:B10 C5:D7), 0 )
The Excel #DIV/0! Error
#DIV/0! is easy to remember, as it is produced when a formula attempts to divide
by zero. Clearly, a division by zero produces infinity, which cannot be represented by a spreadsheet
value, so Excel highlights this problem with the #DIV/0! error.
For example, if cell C1 contains the value 0, then the formula :
will return the #DIV/0! error.
This problem can be overcome by using the
Excel IF function to identify a possible
division by 0 and, in this case, produce an alternative result :
=IF(C1=0, "n/a", B1/C1)
The Excel #VALUE! Error
#VALUE! Excel formula error is generated when one of the variables in a formula
is of the wrong type. For example, the simple formula
=B1+C1 relies on cells B1 and C1
containing numerical values. Therefore, if either B1 or C1 contains a text value, this results in
The best way to approach this error is to check each individual part of your formula, to make
sure that the argument has the required type. If your function contains nested functions, it is
a good idea to copy each of the arguments into a separate cell, to check what they evaluate to.
If necessary, break down each term further, to find out its components, until you find the
source of the error.
The Excel #REF! Error
If your Excel cell shows the
#REF! formula error, this indicates an invalid cell reference.
There are 2 common situations that cause this Excel formula error to be generated :
The formula previously referenced a cell which has now been deleted
eg. Cell A1 contains the formula :
If you now delete column C of the spreadsheet, the formula reference to C1 is no longer valid,
and so you will get the #REF! error
The formula has been copied from a cell that references a range near to the edge of the spreadsheet.
When the formula is copied, the range changes, in line with the
Excel Relative Referencing Rules,
so that the resulting range would (hypothetically) be for cells outside of the spreadsheet range.
eg. The formula shown below, which is in cell A1, counts the number of cells in 'Sheet2', that contain the value 1 :
If you now copy the above formula into cell A2, Excel attempts to adjust the range Sheet2!1:1048576 to
- However, in Excel 2003, row number 1048577 does not exist (as there are only 1048576 rows in Excel 2007/2010).
Therefore, in this case, you will get the #REF! error.
In both of the above examples, if you click on the cell that contains the #REF! error, you will see that
the cell reference within the cell has been replaced with #REF!. Therefore, in order to fix this error,
you need to re-enter the correct cell references into your formula.
(Note that in the second example above, you could avoid the error by making the cell references
- ie. change the reference Sheet2!1:1048576 to Sheet2!$1:$1048576)
The Excel #NAME? Error
When Excel encounters text in a formula, it will try to interpret that text as a reference, a named range,
or a function name. If the text is not recognised as any of these, the
#NAME? error will be
For example, if you intended to type in the function
=SUM(B1:C2), but you accidentally type
=SM(B1:C2), Excel will fail to recognise the function name "SM" and so will generate
Therefore, the way to approach a
#NAME? Excel function error is to check your function
names, references and named ranges have the correct spellings, and check that any variables that are
meant to be text values are entered in double quotes. If your formula contains nested functions,
check the results of these individually, until you identify the source of the error.
The Excel #NUM! Error
#NUM! Excel formula error is generated when Excel encountered an invalid number in
a formula. For example, all square numbers are positive, so there is no such thing as a square root
of a negative number (unless we enter the world of imaginary numbers). Therefore, the Excel function
SQRT(-2) will generate the
#NUM! error, to indicate the invalid negative
number argument to the square root function.
Therefore, the way to fix a
#NUM! error is to check each of the numeric arguments in
your formula. As with the other argument types, if you have a nested formula, it is a good idea,
to break down the formula and evaluate each argument separately.
The Excel #N/A Error
#N/A Excel formula error is produced when a value is not available to your formula.
For example, the example below shows an attempt to use the
Vlookup function to find
the value "Cabbage" in column C of the spreadsheet, and return the associated cost from column D.
However, as "Cabbage" does not appear in column C, the VLOOKUP function is unable to find this value
and so returns the
Therefore, in order to get to the bottom of a
#N/A error, you need to look at the
values that the formula is accessing and identify why the required value is not available.
If you think the value you are trying to look up is in the list you are searching, visit the
Failure To Match Values
page for more help with this problem.