Hi, this is Wayne again with a topic “Fixing Common Excel Errors – Part 2: NULL, NUM & #####”.
This video is fixing common, Excel problems, part two, it’s the second in a three-part series on some strategies to try in order to fix common, Excel errors, and I have a spreadsheet here to go along with this video. I hope that you’ll download it by looking in the description below the video and this workbook should look pretty familiar. If you watched part 1, you can see that it has still the div 0 error message. If you want to learn how to fix the div 0 error message, please watch part 1 here in part 2.
We’Re gon na look at some strategies for fixing the null error, the num error and the hashtag error. Let’S start, first with null the main reason why you will see the null error is, if you use a space in your formula when you actually should use a comma or some other symbol. So let’s do an example.
Let’S say I would like to add up the total cost of the first 8 items in this list and the last 5 or 6 items, so I want to leave out some of the things here in the middle like shipping envelopes. I don’t want to include those so to do this, I’m going to go here off to the right. Let’S just say g4, it doesn’t have to be there though, and I’ll type equals.
So this cell equals the sum of what well I’ll put in a left parenthesis and then I’m gon na click and drag. I could type the cell references here, but I’ll just click and drag to get the first, eight or so items in this list. Ok, so I want to add that, but I also want it to add: let’s say Erasers on down.
So let’s say here in the formula I put a space and then I go down to erasers and I click and drag and get the remaining items. So back up here on my formula, you can see what it looks like I’ll tap, Enter or return on the keyboard, but it gives me a null error now. The reason for that is because of this space here I should not have put a space there. Whenever you put a space in that situation or similar situations, what it does is it tries to identify an inner section of two ranges.
It’S looking for where two ranges intersect. Well, guess what this range that I selected does not intersect with this range. There is no intersection and that’s why it produces an error message. No, there is no intersection between those two ranges.
So, instead of a space, what I really meant to do and should have done, was put a comma now, if I tap enter on the keyboard, it’s adding up this range and it’s adding up to that range and putting them together, and it gives me 17:57. So if you do see this null error, double check, your formula make sure you’re using the proper symbols and don’t have extra spaces many times that will solve the error. Next, let’s look at the num error and for this one I’m gon na switch to the health tracker spreadsheet in my workbook and as you can see, this health tracker is a way to track the health of Jason Smith he’s trying to lose weight and you can See here his weight and the change from the previous week and that’s being tracked with a formula you can see the formula there, it’s pretty easy, pretty simple. Who knows why? But let’s just say that I’ve got to know the square root of how many pounds Jason lost from week to week.
Well, I could do that. I could type in equals s. Qrt notice, it says, returns the square root of a number and what’s the number it’s this right here, so I click there and then I’ll put in the left parenthesis and I need to now put in the number so I’ll click here on the number zero. In this case, but it’s actually clicking not on the number but on the cell reference c7 and then I’ll tap enter on the keyboard and there you’ve got the results. Square root of zero is zero. Now I’m going to use the autofill handle this little green square.
In the lower right corner of the cell, I’ll just click on that and hold the click and then drag down the page and it copied that formula down and it worked beautifully, except for a couple of num errors. Now, why am I getting these num errors? Basically, you’ll see num error when you have a formula that try to produce an invalid number. So if you tell Excel to do an impossible calculation, it’s not going to be able to do it it’s impossible, so it may give you this num error.
So why is this impossible? Well, I told Excel to give me the square root of negative 4 and if you know how square root works, there’s just no possible way to get the square root of -4 because it’s a negative number same thing with negative 3. It’S just not possible to get the square root of it. So, anytime, you see this num error, double check your formula double check your data and make sure that you’re not asking the impossible of Excel a way to fix.
That would be to simply change negative numbers to 0, but in this case that would not reflect reality, and so instead, you may want to do an if error formula. Hopefully you can see how I’m doing this so equals. If error left parenthesis and then the formula that I was using before and then I’ll put in a comma and then quotation marks and quotation marks, so basically that will print on the screen a blank space, nothing and then I can put the right parenthesis and tap Enter on the keyboard, so now, if I copy that formula down you’ll notice what it does, if there is a num error, it wipes it out and pretends like there is not an error. If there are no errors, then it performs the formula, just as I had created it up here with the square root.
Now there are other cases when you’ll see the num error, it’s technically possible for the num error to appear when a number is too big for Excel or too small for Excel. This rarely happens. I bet you’ll never have this happen, but the biggest number that Excel can display is a number one with 308 zeros after it, and if you try to go higher than that, it’s going to produce a num error so to demonstrate this, I’m going to type equals In this cell, so this cell equals let’s say 2000 raised to the power of and the way you show. That is with this little carrot symbol.
For me, it’s shift six on the keyboard and then I’ll just type in let’s say seven thousand six hundred and seventy six. So I’m gon na raise two thousand ^ 7676. Let’S try that I tap enter on the keyboard. That gives me an error message, because my formula produced a number so big, it was bigger than the number one with three hundred and eight zeros after it and so Excel just cannot display it.
Now. That’S also true of negative numbers. If I try to type in or calculate a number that is smaller than minus one, with three hundred and eight zeros behind that, that’s also going to produce a num error, because the number is too small for Excel to display. There are some other reasons why you might see the num error, they’re, pretty rare, but watch out for those two examples that I’ve shared. The third error message that we’re gon na look at in this article is pretty simple, pretty easy and very common, and I’m going to use this third spreadsheet. The movie inventory spreadsheet. So here I have a list of movies and the cost that was paid to buy these movies in DVD format. So I’m gon na go down here to the bottom underneath them and I would like to get a grand total so I’ll just go up here and use this autosum button here on the Home tab home ribbon in the editing group. I can just click that, and it will auto sum everything in that column. I tap enter on the keyboard, but look I get this terrible ugly, looking hashtag error now. The reason for this is very simple and the fix is very simple. What this symbol means is simply that Excel cannot fit the contents of this cell in the space provided and you’ll notice.
If I go up here to the column letter D, I’m gon na go to the line between D and E, I can click and drag and I can make that even more narrow. If I want – and if I do that notice that the text that was there before turns into hashtags, so that shows that it’s really just about space, it’s about being able to display the contents of the cell. The solution for this is super easy. All you have to do is click and drag to prove more space for that column, and that way the contents of all of these cells show up and can fit in the space provided there is an even easier way to do this.
All you would have to do is double click on the line between D and E, in this case, so just double click and that automatically resizes the column to the perfect width so that everything can be displayed. So we’ve looked at three common error messages null num and the hashtag error, and I’ve shown you some of the best techniques for resolving those errors watch for part 3 in this series, where I’ll cover the ref error and the value error. If you found this tutorial to be helpful, please like follow and subscribe and when you do subscribe, click the bell next to the subscribe button. If you do you’ll be notified whenever I post another video, 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 .