MVP #80: The Errors You Encounter In Excel Are Telling You Something Important.

If you've used Excel consistently at work for a few months, you will be familiar with some errors Excel displays when it can't get you the answer you want. Oftentimes, we don't give some thoughts to these errors but the truth is they are not random or meaningless, they are trying to tell you something important.

Today, I will be sharing with you how to interpret those errors and uncover the gem in them. How to read the message they are trying to pass across to you.

There are eight error types in Excel:

1. #VALUE!
2. #DIV/0!
3. #N/A
4. ########
5. #NAME?
6. #REF!
7. #NUM!
8. #NULL!

#VALUE! Error

You get #VALUE! error when you do a calculation in Excel that doesn't make sense. Like Michael + 2. What is Michael + 2? Is Michael a number? What kind of answer are you expecting? Can you try it on your CASIO calculator? 

To be blunt, #VALUE! is Excel's way of saying someone is stupid. Someone has typed in a calculation that makes absolutely no sense. 

#DIV/0! Error

As the name implies, division by zero, #DIV/0! is the error you get when you do any formula that divides a number by zero.

Now, I have a trick question for you: what error do you think Excel will give you when you divide Michael by zero? #VALUE! or #DIV/0!?

#N/A Error

This is the error you get when you do a lookup function (VLOOKUP, LOOKUP, MATCH etc.) and Excel can't find what you are looking for.

####### Error

You get this error for two reasons. The first and more common one is when there is not enough space in your Excel cell to display a numeric (number) value. Excel doesn't want you to take 1,000,000 as 1,000 because space was only enough to show 1,000. So it puts in ####### in the entire cell and force you to expand/widen the cell to see the entire content.

The second and less common one is when you do a calculation on date and the resulting date answer is too large or too small a value to be shown as a valid date in Excel. Here's what I mean. In Excel you can do 26-Aug-16 + 1 and you will get 27-Aug-16 (the next date).

But when you try 26-Aug-16 + 999999999 you get ######## and no amount of expanding the cell will make the error go away. You are trying to calculate a post-apocalyptic date.

#NAME? Error

This is the error you get when you type a formula name that doesn't exist in Excel. Maybe you wanted to type SUM but mistakenly typed SUN. Well, Excel doesn't know about the solar system so t will tell you that it doesn't recognize that formula name.

#REF! Error

This is a very popular and troublesome error. It happens when you have a formula that picks value from a different sheet or different file and somehow the file or sheet becomes inaccessible (or deleted). When Excel tries to recalculate the formula, it gives you that #REF! error because it can't access one of the reference file/sheet.

Another funny case that causes it is when you drag a formula that depends on a cell above it too way up that it messes with Excel's cell reference system.

#NUM! Error

This is the error you get when you do a calculation that is too large or too small for Excel to handle. An example is 999 raised to the power of 99999

#NULL! Error

This is a very uncommon error. We are all familiar with the multiplication, addition, subtraction, division etc. operations. There is one not very commonly known -- the intersect operator. And it is simply a space. It gets you the intersect value of two different ranges.

So what happens when you select ranges that do not intersect? You get #NULL! error.

And those are the errors in Excel and their interpretation. Now you know what they are telling you anytime you come across them.