Excel is a great tool but when things aren’t correct, Excel will let you know with an error or info message. But these messages aren’t too descriptive or user-friendly.
What’s the difference between a #VALUE! message and a #REF! or #NAME!, or an #N/A? Let me explain in simple terms and everything will soon become a bit clearer.
#N/A
The classic #N/A warning. You’ll often see this warning for example, when you create a VLOOKUP and the value you’re looking up simply doesn’t exist in the table you’re looking up.
N/A means “no value is available.” An#N/A indicates an empty cell.
#VALUE
This means your formula hasn’t been entered correctly. The good old #VALUE error is very general but the first thing to do is check that your formula makes sense.
It can mean that the cells you are referencing don’t contain the correct data format. For example, you’re using an arithmetic formula (such as =SUM(A1:A9) but you have some text in the range A1:A9. Or it could be that your formula is referencing to a cell that contains a #VALUE! or #N/A!
#REF!
You’ll see this #REF! warning when your formula can’t find a cell referenced in the formula. This is a classic Excel error and usually means that the cells referenced by formulas have been deleted, or even pasted over.
#DIV/0!
You may not see this one as regularly as some of the others above but it’s good to know what it means when it does crop up.
The #DIV/0! warning is presented when there is an attempt to divide by zero. For example, if you entered the formula =100/0 , then you’re trying to break the rules of maths and the universe, so you’ll get #DIV/0!
Here DIV means Divide, Excel isn’t trying to be rude here.
#NAME?
This #NAME? error is most often seen when an area of cells has been spelled incorrectly. For example, you may have named a range of cells A1:A9 with the name NetRev23 but in your formula, you’ve used the name NetRev233. In this example, you’d get the #NAME? error as Excel doesn’t know what the name NetRev233means.
Can not compute.
#NULL!
#NULL! warns that these two ranges of cells do not actually cross, or intersect.
For example, you’ve unintentionally typed a space in a formula, e.g. =SUM(A2:A100 B2:B100) instead of =SUM(A2:A100;B2:B100)
Again, scan and check your formula to pinpoint where the unnecessary space me be lurking.
Conclusion
You’ll always face error messages using Excel. We all have done and we’ll always continue to face them. Although the error messages don’t explain a lot it is handy to at least know what each error message means so that we can at least know where to start investigating the root cause.
Good luck with errors such as #VALUE!, #REF!, #NAME!, #N/A and the rest. Embrace them, understand them, and you’ll soon be working smarter than ever with Excel.
