If Excel detects that your Excel Formula or Function contains an error, it will return an error message (e.g. #VALUE!, #N/A).
The error message that you are presented with, provides information about the type and cause of the Excel formula error. It can therefore assist you with identifying and fixing the problem.
The table below provides a quick reference guide of what each of the different error messages means. Further details and examples of each error type are provided further down the page.
|#NULL!||-||Arises when you refer to an intersection of two ranges that do not intersect.|
|#DIV/0!||-||Occurs when a formula attempts to divide by zero.|
|#VALUE!||-||Occurs if one of the variables in your formula is of the wrong type (e.g. text value when a numeric value is expected).|
|#REF!||-||Arises when a formula contains an invalid cell reference.|
|#NAME?||-||Occurs if Excel does not recognise a formula name or does not recognise text within a formula.|
|#NUM!||-||Occurs when Excel encounters an invalid number.|
|#N/A||-||Indicates that a value is not available to a formula.|
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
#NULL!error, 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:
#DIV/0! is produced when a formula attempts to divide by zero. Clearly, a division by zero is equal to infinity, which cannot be represented by a spreadsheet value, so Excel returns the
For example, if cell C1 contains the value 0, then the formula:
will return the
This problem can be overcome by using the Excel IF function to identify a division by 0 and, in this case, produce an alternative result. For example:
#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 numeric values. Therefore, if either B1 or C1 contains a text value, this results in the
If an 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 has been copied from a cell that references a range near to the edge of the spreadsheet. When the formula is copied to a new cell, the range changes, in line with the Excel Relative Referencing Rules, so that the resulting range would (hypothetically) reference cells outside of the spreadsheet range.
If you now copy the above formula into cell A2, Excel attempts to adjust the range to Sheet2!2:1048577. - However, row number 1048577 does not exist (as there are only 1048576 rows in current versions of Excel). Therefore, you will get the
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 formula has been replaced with
#REF!. Therefore, in order to fix this error, you need to re-enter the correct cell references into your formula.
When Excel encounters text in a formula, it will try to interpret the 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 generated.
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 the
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.
#NUM! Excel formula error is generated when Excel encounters 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 (excluding 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.
#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.