0:03

This week Alex has been asked to help

a stationary retailer correct a worksheet that contains a lot of errors.

Errors can get into our workbooks when we enter data,

and we saw one way of addressing that when we talked about Data Validation.

But errors can also get into our calculations,

and it's very important that we find and correct those errors,

otherwise our spreadsheets are not going to produce the right outputs.

Take a quick look at the spreadsheet,

and you'll see a lot of the common errors that occur.

You can see an N/A error,

a #REF! And a #VALUE!

error. When you type in a calculation and you make

a mistake so that either the syntax of the calculation is incorrect,

or one of the arguments is invalid,

Excel will give you an error message.

And this is really helpful,

because it means we can easily find and fix those errors.

For example, in column H we've got quite a few errors.

You can see all these hashes here.

We know that that generally means the column is not wide enough,

but when I widen this column a little bit,

you can see, those numbers must be very large and I'm still getting hashes.

So, it looks as though we have a slightly more serious problem.

And we also have these #VALUE!

errors occurring the #VALUE!

error occurs when you've either made a typo in your calculation,

or one of the inputs is invalid.

So, if we double click on the cell to actually have a look at that calculation,

this has an advantage in that it actually highlights the inputs.

So you can see here G6 is highlighted and H4 is highlighted,

and we can immediately see the problem.

We're trying to do arithmetic with text and,

that you can't do.

The reason this has occurred is because when they came and put in the calculation for H5,

they failed to make the H3 absolute.

So, we're just going to fix that easily by making sure we click on H3,

press our F4, press Ctrl+Enter,

so we stay in the same cell,

and then double click to copy down.

Problem fixed. Now that was very easy to spot because

there were a lot of errors and they were right near the top of our worksheet.

But if you have a very large worksheet,

it might be a little harder to locate each of the errors,

and fortunately, Excel gives us a range of tools to help with this.

I'm going to click away from that selection for a moment,

and we're going to come up to the 'Home' tab on the ribbon,

and on the far right,

we're going to click 'Find & Select' and then choose 'Go To Special'.

One of the options 'Go To Special' gives us,

is to find formulas,

and then we can be more specific and say,

only find formulas that have errors.

So we're going to unselect everything except the errors and we're going to click 'OK'.

And immediately, you'll see all the cells that contain an error have been selected.

The problem is, the moment I click away,

they will be unselected.

So if you want to retain the selection,

just come up to a little paint bucket here,

and we will highlight them as yellow.

And now you can see that all the errors are standing out,

bright yellow on the worksheet,

and we can systematically work through them.

Another great tool we have,

and I'm just going to click unselect,

is when you come up to your 'Formulas' tab,

and in the center group,

which is called 'Formula Auditing',

we have a great selection of tools for helping us find and correct calculation errors.

And the one we're going to look at first is 'Error Checking'.

If we click 'Error Checking',

what it's going to do is start from wherever we're clicked,

and work systematically through each error in our workbook,

giving us the opportunity to correct them, so that's really great.

So for the first error it's identified,

it's showing us the calculation which is a VLOOKUP, and the error.

We've got an N/A error coming back and that means

it's tried to do a look up but it hasn't found the look up value.

Generally, that means we've typed the look up value in incorrectly,

or we haven't provided it at all.

But in this case the employee ID is definitely correct.

So, what is going on?

Well, one of the options we have is to click 'Show Calculation Steps',

and so click on that.

What this actually does is it takes us into the calculation process for

that formula and we can see it's doing a VLOOKUP up on Employee ID.

Well, there's our problem.

We're not actually looking up the correct value at all.

So I'm going to close this,

and then we're going to select the option 'Edit in Formula Bar',

which will allow us to change the calculation.

And here we can see the error.

We've clicked on A4 instead of A5.

So, if we just make that A5 instead and click Enter, problem fixed.

To get back to our error checking,

we simply click the 'Resume' button.

And if we corrected the error,

it will automatically take us to the next one anyway.

The next error is a reference error.

These errors occur either when you copy paste

relative references to cells where they cannot refer to the correct values,

or they happen quite often with lookup errors.

When you refer to a range that doesn't actually exist.

Now once again, let's click 'Edit in Formula Bar'.

And if you have a look here,

we've just got a little typo.

We're looking up the staff name in column 45,

and that is a typo,

it should just be column four.

So let's enter that,

and then we click Enter.

Again, problem is fixed,

and we can hit the 'Resume' button.

Interestingly, you'll notice the next cell it

jumps to doesn't actually have an error message,

and this is one of the advantages of error checking.

It will actually let you look at potential errors.

So these are things that Excel has flagged as looking like it might be problematic,

even though it hasn't yet produced an error message.

We're going to look at this type of error more in the next video,

so let's skip this one for now.

If you want to then jump to the next error,

you can simply click next,

and once you're done,

you can close the dialog.

Let's take a look at another tool.

This one is really useful as an option to show formulas.

Sometimes, looking at a single formula on its own doesn't really help us.

So I've got a #DIV/0 error here in H24.

Usually, you get a #DIV/0 when you try to divide by zero,

and this often happens when one of the input cells is blank or contains a zero.

But looking at this data,

that's not the case,

so it would be helpful if I could see the formula,

but also see the other formulas that work correctly.

So I'm going to come up to my 'Formula Auditing' and click 'Show Formulas',

and I can now see all the formulas in my workbook.

And if I scroll right so I can get back to my average,

when we compare to the formulas around us we

can see it's obviously selecting the wrong range.

It's correct, this one,

we're just going to click on the cell above and copy the formula down.

Problem fixed.

To turn off your 'Show Formulas',

same process, just click back on your 'Show Formulas' button.

One more quick tool we're going to look at.

Down here, we have a name error.

Name errors normally occur because you've either typed the function name in wrong,

or because you've forgotten your double quotes and you're working with text.

When we double click on this function to have a look at it, it's absolutely perfect.

It doesn't have a problem with it. So, what has happened here?

Well sometimes, we get an error in a cell because it's

referring to another cell that in itself has an error in it.

So sometimes, this can be quite hard to find.

So what we're going to use is the trace error function

to locate which cell has actually got an error in it.

So I'm back in H34,

I'm going to come up to 'Formula Auditing'.

On the 'Error Checking',

I'm going to click the dropdown and select 'Trace Error',

and you'll see it's actually traced back to find all the inputs in for this calculation.

And where you see the red arrow,

that is telling you which cell is producing the error.

To turn the arrows back off again,

just click 'Remove Arrows'.

Let's come to this cell and see what is causing the error.

And there it is, it's a simple typo.

It should be COUNTIFS.

So, we're just going to put the I in there,

click enter, and all of our name errors are corrected.

So, what we've looked at in this video are

some tools to help us quickly locate and correct errors within our workbook.

Those were easy because they had error messages.

In the next video,

we're going to look at how we can find those slightly more subtle errors

that don't necessarily produce an error message. I'll see you then.