All language subtitles for 1. Troubleshooting Common Errors

In this section of the course, we're going to be taking a look at troubleshooting in Excel.

And we're going to start out in this lesson just by taking a bit of a deep dive into some of the areas

that you might come across when you're working in Excel because it is so important that you understand

the error that you're getting, because that's going to give you more of a chance to be able to fix

it.

And we always want to keep our spreadsheets as error free as possible.

So let's take a look at some of the most common errors.

Now, one error that you might see pop up from time to time is the div. error.

And this is what we call a divide by zero error.

And in general, you'll get this come up when you're trying to divide a number by zero or something

that isn't valid.

So for example, if I was trying to do a calculation here where I wanted to do this number divided by

this number, I'm going to get a div error because I'm trying to divide by zero.

Similarly, if I try and do the same thing, but I'm using a blank cell instead, so I'm doing 20 divided

by blank.

I'm also going to get a div error.

You also might see this type of error if you're trying to do something like an average.

So let's type in average IPS.

I'm going to select my average range, so let's say we're trying to find out the average of these numbers.

My criteria range is also going to be these numbers and my criteria well.

Well, I want to find out the average of everything that's above 50.

So I'm going to put in quote marks greater than 50 and close the bracket.

Now I'm going to get a div error here as well, because the criteria that I've specified is not valid.

I'm trying to find the average of everything in this list that is greater than 50.

And none of these values meet that criteria, and that is why I'm getting the div error.

So those are the types of scenarios where you might find yourself with one of these types of errors.

Let's move on to the name error.

Now this generally comes up when a name you're trying to use is not recognized.

And really, two scenarios spring to my mind when I think of a name error.

The first one is a spelling error in the formula.

So let me just add a value into here.

If I'm trying to do a sum calculation, I just want to add up those two numbers if I've accidentally

spelt some incorrectly.

So maybe I've accidentally pressed an X on the end there and then I'm trying to add these two numbers

together.

I'm going to get a name error because I've spelled the formula wrong.

So if I go back in hit backspace and enter, then my formula is going to work.

You'd also get this type of error if you're using an invalid named range.

So let's say, for example, again, I want to do some calculation, but I want to use the named range,

and I think that I've named these two cells values.

So theoretically, I should just be able to type in the name of that range of cells and get it to perform

the calculation.

Now, if I hit enter, I'm going to get a name error there because the values range doesn't exist.

I haven't set it up.

If I was to now go in, select these two cells and name those values, my formula then works.

So in general, a spelling error in the formula or a missing named range is going to give you a name

error.

Let's move down to the next one, which is an end, a error.

And this is a really common error that you see frequently throughout Excel spreadsheets.

And again, there are a few different reasons why you might get this type of error.

Now, the first one is if you already have a cell that contains an NRA and you're trying to perform

a calculation.

So again, let's use good old some.

If I'm trying to add up this range of numbers.

I'm going to get an error because there's an NRA error in the values that I've selected.

Similarly, I might get an NRA if I do an invalid v look up.

So let's say that I want to look up the word purple in this table over here and get it to return a value.

Well, you can probably imagine what's going to happen here.

If I do a v look up, my look up value is purple.

I'm looking for it in this table over here.

I want to return the values that is column number two, and I want to do an exact match of the word

purple.

And if I hit enter, I'm going to get an error.

Why is that?

Well, it's because the word purple doesn't exist in the table, so invalid lookup values in V lookups

will also throw up and a errors.

Now, a NUM error isn't quite as common, but there are a couple of scenarios where you might get this

error.

For example, if the value in the sale is too large, you're going to get a NUM error.

So if I do something like that?

Say a 40 to the power of 100, that number is so large that it's given me a numb error.

If I modify this number to 10, then Excel can just about cope with that.

You can see it's converted to scientific notation, so values that are too large for the cell will throw

up a numb error.

You also get a numb error if you have your values in the formula in the wrong order.

So, for example, I have two dates just here, and if I want to find out the difference between these

two dates in months, if I do the date diff function.

So let's say C 40 is my first argument.

C 41 is my second argument, and I want to find out how many months there are between these two dates.

If I hit enter, it's going to give me 12.

So that answer is correct.

But what about if I selected these dates in the wrong order?

So instead of C 40, I'm doing C 41, basically minus C 40.

I'm going to get a NUM error because it doesn't make sense to minus this number from this number to

get the result that we want.

So if you have your values in the wrong order, you can also receive a NUM error.

The next very common error that you will see is the value error.

And this generally occurs when the argument is the wrong type.

So if I'm using a date formula, so maybe something like Work Day and I'm typing in a start date, which

is 849, and then I'm specifying the number of days closing the bracket and enter, I'm going to get

a value error because the word red is an incorrect value type for this formula.

When we're using the work day formula, we want to have a start date, which is fine 849.

But then we want to have how many days because what we're looking to return here is a finish date.

So because this isn't a numeric value, it's a text value.

I'm getting an error.

The same thing would happen if I was to use something like the month function, which is basically going

to extract the month from a date.

If I click on the cell and enter, I'm going to get a value error because I don't have a date in this

cell.

I have the word blue.

So basically one of your arguments is of the wrong type.

And then the final error that I want to talk you through is the ref error.

And this is an invalid reference.

Now this generally occurs when a cell has been deleted and it's being used in a formula.

For example, if I type in equal sum and select these numbers, I'm going to get the correct answer.

But what about if I was to delete Column C?

If I deleted that, I'm going to get that reference error.

And you can see in the formula bar, it's telling me exactly where this reference error is occurring.

So all of the values have been removed, and that's why we're getting that reference error.

Now there are a few more errors that might pop up in Excel, but those are definitely the main ones

you're going to come across.

So make sure you understand them so that when you inevitably do come across them, you know how to fix

them.