Most Common Errors and How To Handle Them in Google Sheets

Beginner

About this Tutorial

Today we're gonna talk about errors in Google Sheets and how you can fix them. #N/A, #ERROR, #REF!, #NAME?, #DIV/0

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCATENATE() VLOOKUP()

Video Transcript

<div> Hi, welcome. Uh, today we're gonna talk about errors. Uh, this happens quite a lot for a variety of reasons, and we have a variety of errors. Um, we're gonna go through the most common errors in the way that, um, in pretty much the most, most common that I find, uh, all the way down to the least common. And actually there's a few errors in Google Sheets. Um, sorry. There's two errors in Google Sheets that I literally don't know how we get that number . So, um, I have those down here or how we get that error .

And we also, I'm gonna go through what you can do to solve those errors and then I'm gonna give you one great tip, which is, I'll just give it to you now. Use if error . If you ever get into a problem where you're coming up with errors and you don't want those errors, I use if error a lot, and I'll show you how I do that and what I do just to hide those errors. So first off is na.

This happens a lot because I will be doing a lot of v lookups 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, um, key key that I'm looking for. And so what happens is, like this V look up, B one. As looking in column D and it's looking for the word error and it's just not finding it. So na actually means just like not, it doesn't mean not applicable. It means, uh, that it just didn't find no value. It didn't find a value.

And when errors come up, you usually just can hover over them and they'll give you. Literally, they'll give you like the translation of what it means. Like in this particular case, this says did not find value error in V lookup. So what we can go back here and see is that B one 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 were getting, if you're looking for a V lookup, go through the um, The, the work, like walk through it piece by piece and just see like what should it get?

And usually you'll find out how to fix this error . Like B one. It's looking for error here. Okay, so we're saying let's look for error . And it's looking in all of D one or D D to D, and it's looking in the first column and it's looking for an exec match . Okay. Or sorry, it's, it's sorted. It's not sorted. Okay, so let's, let's look through that, right? And obviously it's not gonna be there. So we can look through error , error , error , and there's just nothing there. So we can see, okay, there's no value and that's how we can fix this.

Um, then the next error I get a lot of is formula parse error . And this will come up as error with an explanation point. This solution I recommend just check your punctuation. This usually means formula . Parse error means that like literally Google Sheets could not parse. The, uh, formula that you have. So the name of the formula is correct, but the actual formula has some issue. And again, I'm, I'm just saying check your punctuation. Usually that's, uh, where your, your error actually lies is maybe you have missing a comma.

This one, in this case, I've tried to, um, put.  quotations around this number , but somehow added another quotation and had a parenthesis here. See, like usually it's punctuation. You might have a missing comma or something like that.

Uh, I will introduce right here is, uh, something, when I was looking up this to make this video, I, I discovered that Google Sheets has an error type, um, formula that you can find out what the error is, even though I don't know why, for some reason this error obviously is like human readable. I can see like, oh, I can go here, see what the error is. But if you can remember these numbers, which I did not realize, these errors had numbers like this is seven, this is eight. , you'll see number one is literally the one that I'm like, how do you even get that?

Um, , but seven and eight are the one that I see the most often. And you can use this error type to find out what the error is if you have, uh, if you want just maybe, um, catalog these or categorize them. And then I realized, uh, when looking through some, um, research for this video, that if you are in Excel and you have something like this, A one B one, where this should have like a comma or some plus or some math here, um, , you will not get error . You will get null.

And so Google Sheets has the null error , but like nobody in the first few results I could figure out, nobody there could figure out what the null does. But it seems like it's a holdover from when Excel . Excel has this. So if this happens in Excel , the error would not be error , it would be null error . Um, and so if you find this. It's happening. Just check your punctuation if you have the word error here. Okay, moving on, ref. So this means reference and it'll say when you hover over it, reference does not exist. What I recommend is check your ranges.

So whatever that ref is, that should have been some column or some cell, and it probably got moved. So I'll show you here. So if I go equals A to a, that is fine. But now. Nothing's gonna happen if I say, if I put the dollar signs here, right? Nothing happens. But if I remove the dollar signs and I want to take this, this cell and I copy and paste it onto here, see it becomes ref the reference error . . It's because this A should have, when it moved here, it tries to move it before. So let's see. Before A, and there's nothing before A.

If I go here, A to here it's D and then F, right? So it's moving. But if you move beyond what it actually what? What's before A? 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 ranges . This happens a lot when you're moving, uh, large data sets around. You'll get a bunch of rough errors if you. Copied and pasted one cell off or behind. This happens quite often to me, um, when I'm moving data around or data calculations around.

Next one is name, uh, pretty simple explanation as you just got the name, the function name wrong. Uh, here. We tried to spell concatenate but didn't complete concatenate with an e at the end. Um, if we just hit concatenate and we get rid of that error , okay. .

And the last two most common, um, common, uh, common errors I have is divide by zero or, um, it says Diiv zero, like tells you, you divide by zero, divide, divide by zero exclamation point, and this will literally happen when you divide by zero. Uh, just check your math, just make sure uh, your calculations are correct, that you want them. Uh, Okay, so this happens actually quite often when I'm been doing business plans and say you wanna do some like churn or lifetime value.

And like you have like, oh, if you start putting in, um, random numbers for like, Hey, I'm gonna, I'm gonna get do sales of 200 and 300 and 500, and then you like 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 at zero. Uh, you'll get this, this error all right away. Uh, again, this happens when, when you're sort of doing projections. Sometimes when you project, like, um, oh, it happens a lot of times when you do rather aggressive projections towards the negative.

Um, If you want to look at sort of diminishing returns or like how, how does something, um, function or depreciate over time? You might get a divide by error here. Last one is value. Uh, when you hover over it, it'll, it'll tell you function . Multiply parameter two expects number of values , but hello is a text. And cannot be coerced to a number . So just check your math value so you're not looking at the math function. The times I'm trying to take 7 47 and multiply it by, hello. I don't know why.

Um, this actually does happen a lot when you have like, here, I'll do it here if you have cell. So we can put hello there and we want 7 47 times. Let's do this and see 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, uh, hello. So this is a pretty common one if you are, uh, selecting the wrong, uh, the wrong cell sometimes. Um, and then the last two, which are funny enough, This is the number one error type, and this is number six.

Numb is, this is a, this is the example in Google Sheets. They give, I've, I don't think I've ever seen this, where you have a formula and where your numbers just shouldn't line up correctly and it'll, it'll tell you, again, hover over it, it'll tell you rate, attempted to compute the internal rate of return for a series of cash flow, but it was not able to, so it just means your numbers are wrong. So one of these numbers is not like the other. Last one is null , and I couldn't find anyone that had an explanation of when this actually occurred. Uh, this looks okay.

So this actually is very useful when you want to sit. So in database parlance like nu is a real value, which means nothing. It doesn't mean that there's zero, it doesn't mean that we've, we don't want to enter something. It's like, it, it makes it very apparent. That no data has been entered, nothing whatsoever has ever been, uh, entered. And so what's interesting is there is a, um, na or not, I think of this as not applicable, but you can do NA and then, uh, parenthesis and that is a real function , okay?

That is a real function . And nu is a real, is a real use when you actually want to say null. So if you go equals is error .  and we do this and it says true, right? But if we do equals an A, let's do that. It says false. This function of equals NA is not an error . So this is really funny that if you put the function na, it says false for, is it an error ? But we can also, let's look at this. If we do hashtag n a. That is still not an error . And so it's saying any error that is not, let's look here. Is it an error? It's a . It is false. Okay.

I kept getting rough errors there, but actually if, if it actually is a error , it'll say true. Let's see. This is a rough error . Let's go here. I need to delete this. And see, so this error is, error is not doing using na. So this NA will not be a error . But all the other errors will, will come out true if you use as error , even if you do equals hashtag pound sign. No exclamation point. This is an error . And again, in database terms, this really.

We haven't put a number in, um, typically when I'm dealing with like, um, adding data , a lot of data and I just wanna like say true false, true false. And I don't know, is it true or is it false? And I don't know yet. I'll actually use na. Um, but I think technically you should be saying like, if you don't know the answer yet, you should be using, no, I think that's how you could use. Um, error .

And so that was the most common errors and how to handle them. And honestly, the easiest way to handle them well beyond like actually these sort of, um, rough errors and, uh, name errors, value, you have to actually solve these. But sometimes you are gonna get a not applicable. Sometimes, uh, you want data to show up eventually. And so if you have an error , you don't want that to show. Just put equals if error , 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 um, formula and function is still there, but the data involved is zero, is nothing. Not zero. Some people will say, error . They'll put a little message or they'll, they'll say, uh, you have an error . right? You can absolutely put a message to yourself. Um, you can also say, if it's an error , what error is it? So you say error type. And then, but honestly, the best thing to do, at least for me, most of the time I'm doing if error 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. Uh, so I use F error . So this I use a lot of times. Um, it can get a little funky if you want to see which errors you're doing. So that is one pitfall and one challenge you should look out for. So don't put if error , if you actually want to know what the error is and you want to fix those. Especially like name and diviv, zero and value and stuff. So, uh, hope, hopefully this was helpful. Again, here's some solutions to those pesky, pesky errors. Check your results. If you have an na, check your punctuation.

If you have an error , check your ranges . If you have a reference error , check the function name. If you're, if you have a name error , check your math. If you have a divide by zero and check. The math values . If you have a value error and if you don't wanna see errors, then just use if error. Nothing. Have a great one.</div>