Hi, this is Wayne again with a topic “Fixing Common Excel Errors – Part 1: DIV/0, N/A, & NAME?”.
In this tutorial, we’re gon na look at how to fix common Excel errors, and this is the first in a three-part series in part, one we’re going to look at the div 0 error, the n/a error and the name error, and to help with this, I have A workbook with three unrelated spreadsheets each one, is a good example of one of those errors. If you would like to follow along with me in the description below this video, I have a link that you can use to download this workbook and it makes it so much easier to follow along. But let’s get started first with the div 0 error you can see. My spreadsheet here is tracking my purchase history for some supplies and you can see I’m getting a div 0 error a couple of times here in the spreadsheet. Well, what this stands for is dividing by 0 dividing by 0, as you probably know, is not possible, and so because it’s impossible when I try to do it, I get this error message in Excel, so what’s happening here. Well, I have a formula set up. You can see it here where what’s happening, is it’s dividing the total cost of a particular item tape in this case by the quantity that I purchased, and it gives me the total cost per item, so this is being divided by this ok.
So let’s look at the next row here. I have nothing: zero, basically divided by zero. Well, that produces an error message, because it’s not possible to divide 0 by 0, it’s not possible to divide a hundred by 0 either, so that error message is accurate. It’S telling me the truth. There’S an error going on here. The thing is, it looks so bad. It looks so ugly in my spreadsheet. So how could I fix that and you can see there’s another example of it down here. Well, one way to fix it would be to not divide by 0. So if I go out and buy a box of markers, let’s say it’s $ 6 and I get one box now that I have something other than zero in this cell.
It updates and there’s no error message, but what, if I don’t want to buy a box of markers or don’t need to, let’s just zero, those out and now what I’ll do instead is I’ll change up my formula, so I’m gon na go back up here to Cell f2 click on the cell and I’m gon na delete. What’S there in the formula bar. I, of course, could do the same thing here inside the cell, but sometimes it’s nice to use this formula bar it’s just a little bit cleaner, sometimes so I’ll type equals and then I’ll start out with something called. If error, it’s kind of a weird function to use but notice what it says, returns value if error, if expression, is an error and the value of the expression itself, otherwise, so this basically helps guard against errors and produces something if there is an error.
Okay. So if error left parenthesis and now I’ll put in the total cost for the tape so d2, so I type in D 2, divided by e 2 and then I put in a comma and then after the comma, look what it’s expecting. It’S expecting the value. If error, so, if there is an error in cell f2 based on dividing 60 by 32, if there’s an error, what do I want it to print on the screen? Well, I could just put two quotes or a quotation mark a space and another quotation mark and then close parentheses. You really don’t need the space in between, though now I’ll just tap enter on the keyboard and look.
There wasn’t an error, and so it just produced the result of D 2, divided by e 2 and now I’ll drag that down all the way down to the bottom and release what changed. Well, the div 0 errors went away and why? Because of this new formula, it’s checking for an error, and if there is an error, I tell it to print nothing in the cell and I copied that down using the autofill handle. So that’s true of all of this column.
So that’s one way to handle it. Another way would be to instead of putting nothing in the cell, I could put a message to the person who’s. Looking at the spreadsheet, I could type in something like no purchases, so that would explain why it’s blank there have been no purchases, so I tap enter and notice that it updated the entire column, probably because I’m in a Abell it did it that way. So now, instead of the ugly error code, I get this no purchases message.
So that’s a little bit about the div zero error in Excel. Let’S move on to another spreadsheet and look at a second error that comes up from time to time, and this is the n/a error, and this comes up most often when you’re, using vlookup or H, lookup or any other function or tool in Excel, where it’s looking For a specific thing, so you can see here I have a movie inventory and I’m using vlookup to tell me the rating of various movies. So I could click here type in the Martian tap enter on the keyboard and it tells me pg-13. So that’s using vlookup. If you haven’t already watched my tutorial on vlookup you’re missing out, you need to watch that, but for the purposes of this tutorial, let’s just move on and watch what happens when I type The Lion King, I tap enter and I get the n/a error. So you can probably figure out the reason why I’m getting n/a, because it’s searching the spreadsheet it’s searching the data that I’ve provided for the Lion King and it’s not available.
It’S not finding it now watch what happens if I put the Lion King into this spreadsheet. So the Lion King and I’ll put in the 2019 version of it. So I’ve put that information in now. Let’S go back up and try it again, The Lion King I tap enter.
It still doesn’t work. I still get that in a error. Now, if you get an error like that and you’re pretty confident that the words that you’re looking for are in the range that you’re looking in, that could be for a couple of different reasons. In this case, it could be that I’ve accidentally put in too many spaces in a cell, so, for example, here The Lion King notice that there’s an extra space after the G. When I search for the Lion King without a space, it doesn’t work. If I add a space in here after the G, will it work, will it fix it? Yes, it does, but instead of fixing the search term here, there is a way to actually fix the data, and it could be that other movie titles also have too many spaces included.
So I want to repair that data to make sure that there are no extra spaces to do that. There may be a better way, but this is how I would do it. I would just right click here on column B and choose insert so that gives me a new column and then here in the column, I’ll click on cell b3 tap equals and then the word trim left parenthesis. I’M gon na trim the title Star Wars and then I should put a right parenthesis.
You don’t really have to, but it’s a good idea to get in that habit. I’Ll tap enter and it seems like nothing changed and that’s true. Nothing changed. But the reason why is because Star Wars didn’t have an extra space in it, but some of these other titles do so now that that’s done I’ll use the autofill handle click and drag down the page to copy that formula, and now I want to replace what’s In column, a with what’s in column B, but the tricky thing is column B is really made up of just formulas that depend on column a so I do have to be a little careful with this, but I’m just gon na call this title as well, and Then I’m gon na click and drag from title all the way down to the bottom of my data and then I will copy that either by right-clicking and choosing copy or control C to copy and then I’ll click here and right-click. But I don’t want to just paste if I paste there will be some errors, but if I go here to paste values, what it does, is it converts these formulas into actual values in this case titles of movies? So now I don’t need this column anymore. I can right click on B, choose delete and that should have cleaned up all of these titles.
It should have trimmed out extra spaces and look it worked so now, when I type in The Lion King, it tells me the rating. Let me show you another example of how you could have the correct data in the sheet, but it still gives you the n/a error. A few years ago a movie came out called 42, so I’ll just type in 42. I don’t recall exactly the year. It came out, but I’ll put in some data for it.
Okay, so now that that’s in Fite do a search for 42, it’s gon na work, it finds it. It says it’s pg-13, but from time to time that might not work for you and if it doesn’t, it probably has to do with the number format. So right now, this number in this cell is considered to be a number, but in some cases it might be considered to be a text.
42 is actually the title of the movie. It’S not really just a raw number, so if you’re ever doing a vlookup – and it gives you an n/a error and you’re dealing with numbers, you might consider making sure that the column or row is formatted correctly either as text in this case or as numbers just To make sure that these cells match the format of this cell now, similarly, to my Purchase History example, I can fix this. I can make it so that if I type in a movie – that’s not in my collection, I don’t get this ugly error message.
The way that I would fix that is, I would click on the cell and change the formula in front of vlookup. I’M gon na click in front of the V, I’m just gon na type, if error left parentheses and then after the vlookup information, I’ll put a comma and then in quotes. I’M just gon na put the message that I want to send to the user of this spreadsheet, not in inventory but in the quote, put in the right parenthesis and then I’ll tap enter on the keyboard.
Starman is not in inventory. So now, if I type in Star Trek, that’s also not in inventory, but if I type The BFG it is there and has a rating of PG. So once again this if error function, saves the day and makes our spreadsheets look not so ugly.
When these error messages come up, there’s one more error message that we’re going to tackle in this article – and that is the name error in another video. I show you how to name cells in Excel and I’ve done that in this spreadsheet. This is a health tracker for Jason Smith and noticed that the cell, that has his age in it cell b3, has been renamed.
It’S called age and I’ve also renamed b4. I called that height so now, when I want to calculate the BMI or body mass index for Jason, I can just click here, type equals and then I’ll grab his weight, so B, 7 multiplied by 703 divided by and I can just type in the word height And it should grab the data, that’s in the cell called height in this case b4. But what if I misspelled height? What? If I type in something like that, and then I continue on with my formula, which would look something like this and then I tap enter on the keyboard, look, I get a name error and what this is trying to tell me is Excel thinks I might have Used the wrong name, and I did instead of spelling height correctly when I was trying to reference the cell called height.
I spelled it incorrectly, and so there is a name problem and that’s why I get the name error message. So how do you fix that? You just need to be careful whenever you see that name error. It usually means that you’ve typed something incorrectly, you’ve misspelled it or maybe there isn’t a cell named this exact thing, so you need to check to make sure that you’ve used the correct words spelled correctly.
One way to do that with named cells is just go here to the upper left corner. This is the name box and if you click this arrow, it gives you a list of all of the names that you’ve used in this workbook or spreadsheet. So you can see height, it’s spelled that way. That’S how I need to spell it when I’m typing. Another example of this error could be when you’re trying to do a sum or really any other function. So let’s say I try to type sum, and I want to sum this: entire column left parenthesis B, 1 through B 59 and I tap enter and that works fine.
But what if I accidentally leave the M off of sum and I tap enter well that doesn’t make sense. Some without the M is just not gon na work and so Excel is telling me there’s a naming problem. You’Ll have the same problem.
If you try to do an average, but instead of typing out average, what if you just put AVG it’s gon na give you a name error, one more thing to watch for when you get that name error message, it’s possible that the reason! Why is because, in your formula, you’ve used quotes when you shouldn’t use quotes or you needed to use quotes. So, for example, in this case, I’ve added quotes around the word height when I tap enter. I get an error message.
It’S a little bit different error message, but still that is something also to watch out for so those are three common: Excel errors: div /, 0na and name in a future video I’ll, look at other error messages, including null num, ref and value. In the meantime, I hope you found this tutorial to be helpful. If you did please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology. For teachers and students and when you do subscribe, click the bell next to the subscribe button that way you’ll be notified whenever I post another video and watch for another video from me at least every Monday.
If you’d like to support my channel, become a supporter of mine through my patreon account and you’ll see a link to that in the description below .