Excel’s #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A Errors
Understanding the significance of warnings sometimes posted by Excel formulas, how to adapt your formulas to accommodate them, and how to anticipate some potential hazards that may arise from them.
Last updated on 2020-05-26 by David Wallis.
There are warnings that Excel displays when it cannot evaluate a formula for one reason or another, illustrated here in Column C:
On the How to Deal With the Excel #DIV/0! Error page you’ll find explanation of the IFERROR function in the formula in Column D.
Adapting your formulas to respond to such warnings is important if you want to ensure that your spreadsheets are calculating as you require them to.
The #VALUE! Warning
The cause of this warning can be hard to track down, but these are common causes of the #VALUE! warning:
- Your formula is incorrectly typed
- Some of the cells your formula references do not contain the right sort of information. For instance, an arithmetic formula references a cell the contents of which is text
- Your formula is referencing a cell that itself contains a formula that is returning #VALUE! or #N/A!
- The choice between the use of the FIND and SEARCH functions. The formula =FIND("CD","abcdefg") returns !VALUE!; the formula =SEARCH("CD","abcdefg") returns 3. That’s because FIND makes case-sensitive inspections whereas SEARCH ignores case.
To illustrate the #VALUE! warning at work in aritmetic calculations, in this spreadsheet Cells B3, B4 and B5 are empty, and Cells B6 and B7 contain the formula =NA():
Hmmm! I’m comfortable with what Excel is telling me until I get down to Rows 6 and 7. What the logic is in returning the different warnings, I’m uncertain about. This example highlights why you need to test all likely outcomes of formulas, lest thay have unanticipated knock-on effects in your spreadsheets, possibly leading to incorrect results.
Here I’m running a test to understand how Excel might respond to some functions I want to use in a report summary:
The SUM, COUNT, AVG, MAX and MIN functions in the formulas in Cells C12 to C16 all reference Cells C2 to C10.
That the COUNT function should be happy to ignore the warnings is a puzzle to me, and as such puts me constantly on the lookout for anomalous behaviour of any of my formulas.
The #REF! Warning
When a formula cannot locate a cell you have addressed in your formula, that formula will return the #REF! warning. For example, consider this spreadsheet in which everything is hunky-dory:
Until some vandal deletes Column A:
Note that the warning appears within the formula itself as well as appearing in the cell as the result of the formula.
A flawed VLOOKUP formula can return a #REF!, as in this example:
The VLOOKUP doesn’t like to be asked to find the value in the fifth column of a lookup table that contains four columns, A2:D5, only.
The #DIV/0! Warning
The #DIV/0! warning results from any attempt to divide by zero and is the subejct of How to Deal With the Excel #DIV/0! Error page.
The #NAME? Warning
Here are some possible causes of the #NAME? warning:
- You’ve used a range name — a name applied to a collection of cells — in your formula, but you’ve misspelled that name
- In a formula that addresses a cell in another spreadsheet, you’ve misspelled the name of that sheet
- You’ve assigned a value to a name. You then refer to that name in a formula in order to use that value. For example, You’ve created the name TaxRate and assigned the value 20%. In your formula however, you’ve misspelled the name: =C14*TxaRate.
The #NULL! Warning
Possible causes of the #NULL! error warning are these:
- You’ve included a space character in your formula to indicate the intersection of two ranges of cells to each of which you have assigned a name, e.g. =March Results. #NULL! warns that these two ranges of cell do not actually cross
- You’ve unintentionally typed a space in a formula, e.g. =SUM(A2:A10 B2:B10) instead of =SUM(A2:A10;B2:B10) — an example of the space being interpreted by Excel as the intersection of two ranges
- Your formula is referring to a cell that itself is currently displaying a #NULL! warning.
The #N/A Warning
These are some causes of the #N/A warning:
- Your formula includes a VLOOKUP function that cannot find the value you are asking it to find in its lookup table —
- Your formula is referencing a cell in which someone has input a formula calling the NA function; e.g. the simplest of formulas, =NA(), probably to indicate that the cell should contain a value but that value is not available for input at the moment.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
If you’d like an invoice to account for your donation, let me know how much you’re donating —
Thanks, in anticipation.