Excel's #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A Error Warnings

Last updated on 2024-05-02.

Preface

There are warnings that Excel displays when it cannot evaluate a formula for one reason or another, illustrated here in Column C:

Excel error warnings

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():

Excel VALUE warning

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:

Excel VALUE function returns

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:

Very simple Excel formula

Until some vandal deletes Column A:

Excel formula REF warning

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:

Faulty Excel VLOOKUP formula warning

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) —

Thanks, in anticipation.