Most Common Excel Errors #️⃣ and How To Fix Them - Avoid Broken Formulas

0:00Today, I'm going to take you through seven common Excel errors. I'm also going to add a

0:04bonus one for you here that you're going to come across at some point and how you can fix them.

0:09So, one fix is just to get rid of them, but yeah, don't do that. So, you don't want broken formulas;

0:15you want consistent ones, and if they don't work, you want to understand why,

0:20and at least fix it in a way that doesn't return an error. Let's take a look.

0:28The first error has the easiest fix. When you see values like this, it means double-click to

0:35expand the column. Your numbers are too big to be reflected in this short space, that's why you

0:42see the hash sign. Excel doesn't want to cut them off because then you might end up reporting wrong

0:47numbers. Notice for text, it doesn't do that. It does cut off the text, but it doesn't want to take

0:52that risk on your number, so just double click and expand the width of your columns. Number two,

0:58"#VALUE!". The "#VALUE!" error generally happens when you use the wrong data type.

1:04So here, if I had a simple formula that deducted the previous year sales from the actual sales.

1:10Right now, am I going to get an error when I send this formula down? I'm just going to double click,

1:15send it down, no errors. These empty cells are treated as zeros with this formula. But if I

1:23had text here, so if I typed in "missing", I'm going to end up with a "#VALUE!" error. I can

1:28only deduct numbers from one another, I cannot have text. Now there are some inbuilt functions

1:34that can handle or ignore text. So, if I didn't want to see that "#VALUE!" error, I can just

1:41put this inside the SUM function and send this down and I end up with no errors because the SUM

1:49function is able to ignore text. Number three, "#SPILL!". The "#SPILL!" error is a new error

1:57in Office 365 and Office 2021. This happens when you use a function that spills to multiple cells.

2:04So, for example, let's say I'm using the UNIQUE function here, I'm going to get a unique list

2:08of these values, close bracket, press Enter, and I end up with a "#SPILL!" error. Why? Because

2:15my results want to spill, and this is blocking it. We don't want "Subscribe" to block it, this emoji looks

2:22even funnier in the formula bar. When I remove this, my formula spills and the error is gone.

2:30Next one is the "#NAME?" error. Can you guess what the problem is with this function?

2:36There is no function that's called ADD in Excel. The correct function name is called SUM. So,

2:42if you type the function name wrong and Excel doesn't recognize it, it's going to give you

2:48the "#NAME?" error. To correct this, type in the right name. Next one is the "#REF!" error.

2:56So, let's say I'm just deducting these two values from one another. Let's just send this down.

3:02Now, at some point, I end up deleting this column. What I end up with is the "#REF!" error.

3:09The column that this formula was based on has now been deleted. This can also happen if you copy and

3:16paste your formula somewhere else. If I press Ctrl + Z to go back, and this time not delete anything,

3:22instead copy this column and paste it here, I also end up with the "#REF!" error. This is

3:29using relative referencing. The Sales column, which is two columns to the left of here,

3:34doesn't exist on this grid anymore. I end up with an error. Number six, "#DIV/0!" error.

3:42The "#DIV/0!" error happens when you divide a number with a zero. So, for example here, let's

3:47say we want to calculate the percentage change to previous year. I'm going to go with Actual Sales

3:52divided by Previous Year minus one. When I send this down, two cells end up having an error. The

3:59reason for this is I'm dividing this number with a zero. Now technically, I don't have a zero here.

4:05If I had a zero, I'd end up with the same thing, but an empty cell used in a formula is treated

4:12as a zero by Excel. Now, what you don't want to do is go in manually and remove these because

4:18later on when you end up getting a number here, no value is going to show up because you've removed

4:25the formula. Instead, what you want to do is use error handling directly in the formula.

4:32So, once you've written your formula and you've sent this down and you've noticed that this is

4:37going to result in errors, you could put it inside a function that can handle errors. There are many

4:44different ways that you can do this. You could use an IF function and check whether you have empty

4:50cells, or you could use a generic error handling function which is the IFERROR function. You're

4:56going to start off your formula with IFERROR, then use the formula that you had written before,

5:01and then the last argument is the value you want returned, in case you end up with an error. So,

5:07in this case, let's say I want to return nothing, I want to return an empty cell which means I

5:12have to put "" (quotation quotation), close the bracket, press Enter, and now check this out,

5:18when I send this formula down, my errors are gone, but everything remains dynamic. I'm using the same

5:24formula throughout. So, if I end up getting a number here, my calculation automatically runs.

5:32Number seven: "#N/A". The "#N/A" error is a common error when you use lookup functions in Excel. It

5:40happens when your lookup function can't find what you're looking for. So, in this case, I'm using

5:45the VLOOKUP function. If you want to learn more about VLOOKUP, I have a separate video on this,

5:50link to it is in the cards and in the description of the video. But if you have Office 365,

5:55forget VLOOKUP, use XLOOKUP instead, and I have a separate video on that as well.

6:00Check it out in the description or in the cards of the video. Now, the reason I'm getting an

6:05error for here is that "Limbo" doesn't exist in my customer master data. The correct one is "Limba".

6:14Now, in case you want to flag these errors or you want to return something else if you get the

6:19"#N/A" error, you can use the IFNA function. This checks for this specific error. Your value here is

6:28your formula and what you want returned in case you have an error. Let's say is "Person doesn't

6:34exist," close the bracket, press Enter, and now when I send this down, instead of getting "#N/A"

6:41I get "Person doesn't exist". You can, of course, also use the IFERROR function. IFERROR checks

6:47for any type of error, not just the "#N/A" error. The "#N/A" error only checks for this

6:53specific error. So, that was my list of common Excel errors and how you can fix them. Which error

7:00do you come across more often and do you use IF or IFERROR to fix it? Let me know in the comments.

7:06I hope you enjoyed this video. If you did, as usual, don't forget to hit that thumbs up,

7:10subscribe if you aren't subscribed yet, and I'm going to see you in the next video.