Most Common Errors and How To Handle Them in Google Sheets

Most Common Errors and How To Handle Them in Google Sheets #N/A error #REF! error #NAME? error #DIV/0! Error #VALUE! Error #NUM! error #NULL! error

In this tutorial, we're going to talk about errors. This happens quite a lot for a variety of reasons and we have a variety of errors. We're going to go through the most common errors all the way down to the least common.

These are the errors we'll be talking about:

N/A error
REF! error
NAME? error
DIV/0! Error
VALUE! Error
NUM! error
NULL! error

I'm going to go through what you can do to solve those errors and then I'm going to give you one great tip: Use IFERROR().

If you ever get into a problem where you're coming up with errors and you don't want those errors, you can use IFERROR. I use it a lot. I'll show you how I do that and what I do just to hide those errors.

Now let's get to those errors!

N/A error

This happens a lot because I will be doing a lot of vlookups or index match to pull data from places and I'll absolutely just mess up which column I'm looking in or what is the key that I'm looking for.

So what happens is this vlookup, B1 is looking in column D and it's looking for the word error, but it's just not finding it.

“N/A” doesn't mean “not applicable.” It means that it just didn't find a value. When errors come up, you usually just can hover over them and they'll give you the translation of what it means.

Like in this particular case (see video clip below), it says did not find value error in vlookup. What we can do is go back here and see that B1 is error and it's looking in D.

So what I would recommend is just check your results. Check what should your results be.

If you're looking for a vlookup, walk through it piece by piece and just see what should it get.

And usually you'll find out how to fix this error, like B1. It’s looking for error here, in column D.

It's looking in the first column and it's looking for an exact match.

Let's look through that. Obviously, it's not going to be there. So we can look through error, error, error, and there's just nothing there.

We can see that there's no value and that's how we can fix this.

Formula parse error: #ERROR!

The next error I get a lot of is formula parse error.

The solution I recommend is to just check your punctuation.
Parse error means that Google Sheets could not find the formula that you have. The name of the formula is correct, but the actual formula has some issue.

And again, I’m just saying check your punctuation. Usually that's where your error actually lies. Maybe you are missing a comma.

In this case, I've tried to put quotations around this number, but somehow added another quotation and had a parenthesis here. Usually it's the punctuation.

Error type formula in Google Sheets

I will introduce something right here, when I was looking this up to make this video.

I discovered that Google Sheets has an error type formula that you can find out what the error is.

You can use this error type to find out what the error is if you just maybe want to catalog these or categorize them.

REF! error

This means “reference.” It'll say when you hover over it, “Reference does not exist.”

What I recommend is check your ranges. Whatever that ref is, that should have been some column or some cell, and it probably got moved. I’ll show you here. If I go “=A:A”, that is fine.

Nothing's going to happen ifI put the dollar signs here, right? Nothing happens.

But if I remove the dollar sign and I want to take this cell (E4), I’ll copy and paste it onto cell B4. See, it becomes ref. A reference error.

0:00

There's nothing before A, so it gives you a reference error. It just says we cannot, the reference does not exist. We can't find what you're looking for.

Just check your range. This happens a lot when you're moving large data sets around. You'll get a bunch of rough errors if you copied and pasted one cell off or behind.

NAME? error

Next one is #Name? error and the pretty simple explanation as you just got the name, the function name wrong.

Here, we tried to spell concatenate but didn't complete concatenate with an e at the end.

DIV/0! Error

This error is divide by zero and this will literally happen when you divide by zero.

Just check your math. Make sure your calculations are correct.
This happens quite often when I'm been doing business plans.

Let's say you want to do churn or lifetime value. Then you start putting in random numbers: “Hey, I'm going to get do sales of 200 and 300 and 500.” and then you divide it by the number of customers you have, but you haven't put in the number of customers you have. And so it thinks it’s zero. You'll get this error right away.

Again, this happens when you're doing projections. It happens a lot of times when you do rather aggressive projections towards the negative. If you want to look at diminishing returns or how does something, function or depreciate over time, you might get a divide by error here.

VALUE! Error

Last one is value. When you hover over it, it'll show you this error:

Just check your math value so you're not looking at the math function.

This actually does happen a lot.

Let’s put “hello” in D9. We want 747 multiplied by D9.

We have that same error so it knows. “Hey, this is, hello. This is a text. This is not a number. We're trying to multiply two numbers, but you only gave us one number and a hello.”

This is a pretty common error if you are selecting the wrong cell sometimes.

NUM! error

This is the number one error type and is the example in Google Sheets.

I don't think I've ever seen this, where you have a formula and where your numbers just shouldn't line up correctly.

It just means your numbers are wrong. One of these numbers is not like the other.

NULL! error

This actually is very useful in database parlance, where null is a real value, which means nothing. It doesn't mean that there's zero, it doesn't mean that we don't want to enter something.

It makes it very apparent that no data has been entered – nothing whatsoever has ever been entered.

What’s interesting is that it is a real function. I think of this as not applicable, but you can type in “NA” followed by a set of parenthesis.

Null is a real use when you actually want to say null.

If you type in this formula, it’ll say True:

=iserr(B10)

But if we do =NA(), it says False.

This function of =NA is not an error.

If it actually is an error, it'll say true.

Some tips in handling errors:

So that was the most common errors and how to handle them.

Honestly, the easiest way to handle them well beyond these name errors and value is for you to actually solve these. But sometimes you are going to get a “not applicable.” Sometimes, you want data to show up eventually.

If you have an error, just put equals IFERROR, wrap that in parentheses, and then what you want to do is add a comma there and then do nothing. So if you do nothing, nothing will show up.

The formula and function is still there, but the data involved is zero.

Some people will say,”Error.” They'll put a little message or they'll say, “You have an error” You can absolutely put a message to yourself.

Honestly, the best thing to do – at least for me – most of the time is I'm doing =IFERROR and then I'm putting the comma and then doing nothing because I'll have seen the errors that happen. I'll know and I'll expect those errors. But then when data shows up, I want the data to see be seen. So I use =IFERROR a lot of times.

It can get a little funky if you want to see which errors you're doing. That is one pitfall and one challenge you should look out for. Don't put =IFERROR if you actually want to know what the error is and you want to fix those. Especially like #NAME? and #DIV/0! and value and stuff.

Hopefully, this was helpful. Again, here's some solutions to those pesky, errors: Check your results.