Error Values in Excel - Full Explanation

Add to Favorites
Author:

Here, I'll teach you what the errors in Excel mean.  There are many errors that you can get and each one means something.

(make sure to download the accompanying spreadsheet so you can follow along and learn how to fix these errors)

Quick Links to Each Error Section:

######

#DIV/0

#N/A!

#NAME?

#NULL!

#NUM!

#REF!

#VALUE!

######

When your column isn't wide enough to display cell contents.

Solution for the ###### Error

Make the column wider.  You can also double-click the edge of the column to auto-fit it to the widest cell in the column.

#DIV/0

When a formula is trying to divide by zero or an empty cell, which is impossible.

This happens when you input an equation that divides by zero, when you divide by a cell that contains zero, or when you divide by an empty cell.

Here, we divided cell C7 by cell B7.

Solution for the #DIV/0! Error

Double-click the cell to find out where the division is occurring and change it so that it is not being divided by zero.

In this case I will create a new formula and make sure it is not being divided by zero.

#N/A!

A formula or function cannot find the data it is looking for.  This is common in lookup functions.

In cell A40 we have the error.  Cell A42 shows you what was entered into cell A40.

Solution to the #N/A! Error

Allow the formula or function to return some data.

In the case above, there isn't even a lookup table from which the Vlookup function can return something.  Let's add that now:

Now that we have that, we need to enter something for the lookup value, which goes in cell A39.  We still get the #N/A! error because the lookup value references an empty cell.

Now it works once we enter a correct value.

In this case though, using the Vlookup function, if we enter a value that is not in the list, we still get an error:

This is why you should use error checking with the Vlookup function so it returns a more user-friendly error when it can't find data.

#NAME?

When you misspell the name of a function, put text into a function without using quotation marks, reference a range or cell that does not exist, or reference a named range that does not exist.

I've included 3 examples above with the text that was entered into each cell listed below it.

These three examples illustrate common reasons for this error.  The example of misspelling a named range wasn't included but is self-explanatory (you misspelled a named range or that named range does not exist).

Solution for the #NAME? Error

Here is the corrected version, with the correct formulas highlighted:

Line 14 shows the correct output and line 15 shows what was entered in the cells above.

For A14, we needed to spell the function SUM correctly.

For B14, we needed to put quotation marks around the text hi and bye.

For C14, we needed to reference a cell that actually exists; the column ZZZ does not exist and is not even a cell reference.

If we had a named range in there that was causing an error, we would need to spell it correctly or remove it from the formula if that named range didn't exist in the workbook or worksheet.

#NULL!

This means that you put a space in place of a comma in a function.

(technically, it means that you specified an intersection of two cell ranges that don't intersect.)

Here is an example:

When we tried to SUM, or add, cell B18 to cell B19, we forgot to put a comma between the two function arguments, in this case cell references.

Solution for the #NULL! Error

Simple.  Add the comma in the correct place.

The highlighted cells show the corrected version.

#NUM!

Either the number in the function/formula is invalid or the result is too large or too small (think too many decimal places).

This can also happen when using functions like IRR and RATE that must iterate to find a result.

Solution for the #NUM! Error

Basically, just make the number smaller.  That's probably not the answer you want to hear but that's it. 

If you can't make it smaller, make it text by putting an apostrophe in front of the number or formatting the cell as Text.

If it is a problem with a function, you will need to do 1 of 2 things.

Use correct starting values for the function.  This may involve learning how to use the function correctly.

Increase the number of iterations allowed in Excel (this may slow your computer down while calculations are being made).  To do this, go to the File menu (Office button for Excel 2007) > Options > Formulas tab > check next to where it says Enable iterative calculation and increase the number of Maximum Iterations as desired.

#REF!

You have an invalid cell reference.  This happens when you have referenced a cell in a formula or function and then deleted that cell's row or column.

Below, everything looks OK:

Cell A29 shows you what is in cell A28.

Now, we delete row 30 and this is the result:

If we look at cell A28 now it is like this:

The reference to cell B30 is gone because we deleted that row.

Solution for the #REF! Error

There is no easy fix for this error.  Basically, you have to go into your formula and fix the problem by hand by referencing another cell or range of cells until the formula works again.

If you just recently caused this error, you can hit Ctrl + Z to undo the action and fix it that way.

#VALUE!

This means you are using the wrong data types in your functions or formulas.

A34 shows what is in cell A33 and B34 shows what is in cell B33.

Here, we are using text when there should only be numbers.  You can't divide 5 by text and you can't sum the words "hi" and "bye" because they are not numbers.

Solution to the #VALUE! Error

You have to go into each function and formula where this error exists and fix it by hand.  This error means that you entered something incorrectly or that you are referencing a cell that contains the wrong type of data.

Here, I simply replaced the text with numbers to get everything to work:

The highlighted cell shows the corrected versions.

Final Notes

These are the errors that you can see in your Excel worksheet and how to fix them.  But, this doesn't mean that it will be easy to fix all of the problems.  Unfortunately, even when you know why the problem is occurring, it can still take hours to fix a huge spreadsheet that has a lot of problems.

Make sure to download the accompanying spreadsheet so you can see these errors in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course