Excel Errors: How to be awesome at fixing them

I’ve seen a lot of questions on Quora that go a little like this:

“What does it mean when Excel gives you [insert error]?”

Becoming awesome at excel by learning cool tricks are important, but knowing what to do when things go wrong is just as important to becoming a true pro.

Imagine being dropped in the middle of Italy one day and not knowing a lick of italian or having any connections.

You could probably survive for a little while but how long until you’re annoyed by the locals that are pointing and laughing at you, not knowing what they’re saying?

 

Excel is its own language; functions, formulas, and even errors must be decoded and understood. When excel gives you an error, it’s like the program is pointing and laughing at you in that language.

The good news is that I’m here to help you understand its language 🙂

 

 

 

 

 

 

Ok, simmer down Yoda, we’re getting there…

By the end of this post, you should know how to decipher these valuable error messages and show everyone how awesome you are for fixing them. We’ll go over:

• How to understand the language of Excel errors (you won’t need an expensive Rosetta Stone box set)
• Fix those pesky things so you finally can go home in time for dinner.
• How Excel errors can work for you, not against you.

#VALUE!

Probably the most common of all excel error codes, it means that Excel was expecting a specific data type (ie. a number) but you entered something else (ie. text).

For example, if you try to add 1 + A, you’re going to get a #VALUE! error because that doesn’t make any sense.

I doubt any of my readers would actually try to add a letter and a number and expect a mathematical equation to take place, so let’s talk about a more realistic way this error can come up.

Sometimes we may have a set of data that contains numbers, but these numbers are stored as text because of the way the numbers are stored in the original database (ie. your CRM, accounting system, etc.).

You then try to include those numbers in a formula and get the error message.

Whenever you get this error, audit your formula and see which cells you’re referencing. Chances are, you’ll find a number disguised as text. You’ll need to convert those numbers before you can move forward.

But how do you convert numbers stored as text into actual numbers?

 

 

 

Notice that little green flag in the top left hand corner of the cell? This is Excel warning you that the number should be converted. Right click on the cell and select “Convert to numbers”.

 

 

 

 

 

The other way a #VALUE! error can occur is when your excel file is linked to an external source (ie. another excel file). Most of the time, if you don’t have the source file open and you attempt to change something in your active sheet, all your links will return a #VALUE! error because it can’t locate the source data.

 

 

 

 

 

 

Use the “Edit Links” to see if you have any cells formulas pointing to an external file. From this menu, you can break the link to those files (it will paste the current result as a value instead of a formula, or you can open the source file).

 

 

 

#DIV/0!

This error is pretty easy to diagnose, it comes up whenever you try to divide any number by 0. Of course, as we learned in grade school, this is impossible. Its Excel’s polite way of saying…

 

 

 

 

 

 

 

This is most common when you drag a formula down (or across) and the formula attempts to calculate cells that have yet been populated with data.

This one is simple to solve; audit your formula and check to see where it is you’re trying to divide by zero. If you need to make these errors go away you can wrap the formula inside of an IFERROR (discussed later, but feel free to skip on down there now, I’ll wait 🙂

#REF!

This reference error occurs most commonly when you have a formula or function that is referencing a cell (or cells) which no longer exist.

It happens when you delete a cell that was being referenced by a formula somewhere else. Good thing is Excel has some tools you can use to make sure you don’t accidentally delete a cell that is being referenced by another formula.

If you go to your Formulas tab in ribbon you’ll see a button that says “Trace Dependent”. Select this option and Excel will point out whatever is being affected by the cell you have highlighted.

 

#NAME?

This happens when you forget to put quotes around a string of text inside of your formula.

For example, Let’s say I am using an IF STATEMENT and I want the function to return the name of a city whenever the criteria is met. If I forget to enter the text inside of quotes, the formula will return an #NAME? Error.

 

 

 

Whenever you enter a string of text inside of a formula without using quotes, Excel automatically assumes that it is a named range and it begins searching for it. If it can’t find it, then the Excel returns an error.

Same as before, when this happens simply double check your formula and make sure that all text is written inside of quotes.

#N/A

Probably my favorite of all errors (is that too nerdy?), this one happens whenever you use a lookup function (ie. VLOOKUP, MATCH) and it doesn’t find what you’re looking for.

It’s particularly useful to know when these lookup functions don’t find a value because you can then build intelligence into your spreadsheet for handling these situations.

Using IFERROR and ISERROR you can instruct Excel to look in another sheet or table, enter a text value, execute another formula. The possibilities are endless, but we’ll get into that a bit later.

#NULL!

No biggie, this happens typically when you forget to enter a comma while entering a formula.

Turn those excel errors over to the dark side!

 

 

 

 

 

 

I’ve discussed some error handling techniques in previous posts, so if you haven’t yet go ahead and check ‘em out here:

IFERRORs

Excel’s IS Funtions

IFERROR

This function can take the result of any formula or function with an error and turn it into anything you want. You can get rid of all the errors in your sheet by telling the function to return a blank cell, some text, or another function.

=IFERROR(Value,Value if error)

You basically wrap your function inside of the IFERROR and tell excel what you want the result to be instead.

Let’s say you are using a VLOOKUP to pull customer leads into a database you’re building for a marketing and sales team. You are indexing a list of customer IDs and realize that not all the IDs you have are on the list you’re checking against:

 

 

 

 

 

 

 

Use an IFERROR to replace the error with either another set of instructions (ie. another function) or a string of text. Let me demonstrate:

 

 

 

 

 

 

 

ISERROR

Checks whether the result of another function or formula is an error. This is similar to the IFERROR function, except ISERROR can be used in conjunction with an IF Statement in order to execute a command when the result is not an error (as opposed to IFERROR which only gives you the option to execute a command if the reference is an error).

=ISERROR(25/0)

ISERROR is a bit more complicated to use in these scenarios, but no big deal for anyone who’s been reading my material 😉

ISERROR just returns TRUE or FALSE, so by itself it’s pretty useless. But when combined with an IF Statement, you can do some heavy damage.

First we’ll use ISERROR to determine if the result is….[drumroll]… an error! (bet you weren’t expecting that, huh?)

 

 

 

 

 

 

 

 

Then, we’ll wrap the ISERROR inside of an IF Statement so we can actually do something with the True/False result of the ISERROR:

 

 

 

 

 

 

 

 

Note: Notice how I put in some placeholders before I start tinkering with the formula. For me it’s easier to make changes this way and not forget a comma or parenthesis somewhere, causing a massive error.

Finally, we’ll add another VLOOKUP the formula in place of “TRUE”. Turns out there’s another database where we’ll find the missing names. Yeah, yeah, I know; it would be easier for me to just combine the two databases into one list so I can use one VLOOKUP. Stop ruining my example!

 

 

 

 

 

 

 

 

From here you can build upon your formulas based on your needs. But this should help get you started in the right direction.

You see, no Rosetta Stone necessary. You can now open your sheet and not panic at the sight of what seems like a million errors. You’ll know how to immediately diagnose the problem and figure out what changes you need to make.

 

If you’re looking for a jumpstart to your career, or if you want to get noticed more at work, one of the best ways to accomplish this is by improving your excel skills.

It may sound overwhelming or time consuming, but if you can just dedicate 30 minutes a day I can show you how it’s done.

You can start right now by downloading my free ebook Master Excel, Surpass your Coworkers, and Impress Your Boss.