Alex has already corrected a lot of errors in his workbook,
but there are still a few outstanding.
We still have one green triangle that we have not
addressed and where there are a lot of errors in a workbook,
it's always a good idea to do a double-check to make sure you've pick them all up.
One good way of auditing a workbook is to create cross-check totals.
In other words, to try and get to your totals using
different methods and then to make sure that those totals still agree.
For example, in this workbook we have worked out
the Sales by Account Manager for each Year and then totalled them up.
In this section, we have worked out
the Total Sales by Year for each Region and then totalled them up.
But both of these totals should now show the Total Sales
for 2013-2016 and they should be the same.
But if you have a look at this Total figure and this Total figure,
they don't match and that is
a clear indicator that there are still problems in our workbook.
When you're trying to locate more subtle errors like this,
we have two great tools to help us and they're
called Trace Precedents and Trace Dependents.
A precedent is a cell that is referred to in a formula.
A dependent is the opposite -
it is a formula that refers to your cell.
So what we're going to do is come down to G26,
and we're going to find out which cells are being referred to by this formula.
So we're going to click Trace Precedents and it's
summing up the three cells above which looks absolutely fine.
But fortunately, we can keep going back so we can find out which cells these refer to.
So we're going to click Trace Precedents again.
And here, you can see the problem.
We have not included the 2013 Sales figures in our totals.
Now before we fix the error,
we'd like to remove the arrows so we're going to come back
up to Formula Auditing and click Remove Arrows.
And here's a nice way to fix the problem.
We're going to select those three cells and press Delete,
and then we're going to come up to AutoSum on our Formulas tab,
and single click, and it will immediately AutoSum each of the rows.
And if we double-click,
that formula is now correct.
Our total is looking much closer to the one over here,
but they're still not the same.
So we still obviously have a problem and it seems
quite likely that it's to do with this green triangle.
But we'd like to double-check,
does the cell in any way impact this total.
So let's click on it,
and this time we're going to Trace Dependents.
So I'm going to come back up to Formula Auditing and click Trace Dependents.
And it's quite interesting,
we can see it's being used in that total.
It's being used in this total and it's even being used down here.
So this cell, being an error is actually impacting quite a lot of different cells,
but so far we haven't seen a direct connection to this total.
So let's click Trace Dependents again,
and there you go.
This total is feeding into this total over here,
so the error is impacting that cell.
So, let's once again remove our arrows and go and fix the problem.
This time we have a little green triangle,
but we'd like to first identify where it is,
so let's Trace Precedents.
What we're trying to do here is sum up all the totals,
where our employee ID is equal to this value and our year is equal to 2015.
And so far, it looks like it's referring to the correct cells,
but we have this new symbol.
It's this dash black arrow,
and that indicates that we're referring to a value in a different worksheet.
If you double-click on the black arrow,
it will not only tell you which worksheet or worksheets,
but it will give you an exact cell reference
and you can actually go to that cell reference.
So I'm going to come to the first one and
double-click and that shows us what the problem is.
Instead of adding up the Totals,
we are adding up the Order Quantity,
and that's why it's gone wrong.
Okay, let's come back to our Sales Dash and quickly fix the problem.
We're going to remove our arrows and we're just going to come
to our dropdown to the right of our green triangle and we'll say,
"Copy Formula from Left."
and you see, problem fixed.
If you have a look at your total over here,
it now matches our one over here.
So clearly, we have corrected those problems.
So just to summarize then,
both Trace Precedents and Trace Dependents
can be used to help you identify where the errors
are happening in your workbook and what impact
an error cell might be having on the rest of your workbook.
We still have a couple more errors that we need to address,
but we're going to look at that in the next video.
When we look at another great tool called Evaluate
Formula as well as the Watch Window. So keep watching.
Dr Yvonne BreyerProgram Director, Design and Experience, Global MBA
