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.

Â