All language subtitles for 2. Tracing Precedents and Formula Auditing

Tracing presidents and dependents can be really helpful when you're trying to troubleshoot a formula

sometimes, particularly with more complex formulas where you have cells referenced across multiple

different worksheets trying to pinpoint exactly where the error in the formula lies can be quite difficult.

So you want to make sure that we know all of the tools that we have available to help us work out where

the issue is.

And one of those tools is tracing presidents and dependents.

Now, as you can see, I've opened the dashboards workbook that we were using in the previous section.

And the reason why I'm using this is because on the calculations page, we have lots of calculations

which refer to cells all over the place on different worksheets and in different columns, so on and

so forth.

So this is a pretty good worksheet to demonstrate tracing presidents and dependents.

So let's make sure that we collect in a cell that contains a formula.

And if we take a look, we do have a some ifs formula in this cell, which references different tables

and different columns.

Now, let's say that I had a problem with this formula.

I want to make some sense of this formula and work out, which sells my formula is referring to one.

An easy way to do that is to jump up to the Formulas tab.

And we have a formula auditing group over here, and this group is really useful when you're troubleshooting

formulas.

So the first thing that we have on here is trace presidents.

And if we look at the screen tape, it says it's going to show arrows that indicate which cells affect

the value of the current selected cell.

So if we click Trace President's notice that I get a whole bunch of arrows.

So this is basically showing me the cells that this formula in cell D6 relies on to perform the calculation.

So I have an arrow from Cell A 15, and if I look at my formula in the formula bar right at the end

here, I can see, yes, I need the value in Cell A15 for this formula.

He's also telling me that it needs the value in Cell C six.

And again, if I take a look at my formula, I can see just here right in the middle.

Yes, I need the value in Cell C six in order to perform this calculation.

And then the final arrow that I have is a little bit different to the others.

It has a little spreadsheet icon just here, and that basically tells me that the other cells that I'm

referencing in this formula are on a different worksheet.

And that would be correct because if you take a look at my formula, you can see that a lot of the other

cells that I'm referring to are on that product sales worksheet.

And what you're normally find is that if you do have some kind of error in your formula and you trace

the presidents, you might find that you're actually referencing the wrong cell or maybe even the wrong

worksheet.

So this can be really useful.

Now, once you've finished tracing your presidents, you're probably gonna want to remove these arrows

from your worksheet.

And if we go to the Formula Auditing Group, we have a Remove Arrows button.

Now what about trace dependence?

What does that do?

Well, for this particular formula, there are no dependents.

And the difference between dependents and presidents is that dependence means what cells rely on the

value in D6.

And for this cell, no other cells rely on this value.

What about if I click over here in cell IE6 and click on Trace Dependence?

Well, yes, it looks like this one does have some dependence.

And if I go down here and click on this formula, this is doing a max of I six to IE11.

So yes, this formula refers to the cells over here.

The same thing with the one underneath this one is doing an index, a match of cells six to IE11.

And if I was to click on this cell just here and do trace dependence, you're going to find that this

formula has dependents as well.

So these two calculations depend on this calculation being that I once again, if you're finding that

you've got errors, clicking trace dependence can be really useful.

So let's remove those arrows as well.

Now, another couple of things that you can do on this formula, or timber, is you can choose to show

all formulas on your worksheet.

So if you just want to see all of the underlying formulas on your worksheet, you can click this button

and you can see them all listed out just here.

And sometimes again, that can be super useful if you're trying to read through different formulas or

compare formulas with other formulas to hide those formulas, just click the button again.

We also have an error checking button underneath, so this is going to check your entire worksheet for

any common errors.

So if I click this one, would you look at that?

I don't have any errors on my worksheet.

Now what about if I was to delete this column over here?

And then if I click error checking, it's telling me that I have an error in Celle de 22.

And yes, I do take a look.

I now have those reference errors.

Do you remember in the last lesson when we were looking at the different error types that you can receive,

one of them was a reference error.

And as I said, that normally means that you've deleted a column that this formula relies on.

So yes, I can see here it's telling me it's trying to do a max, but the values I'm trying to find

the max solve are no longer there.

And then I have various different options.

I can get some help on this error.

I can show the calculation steps, which is going to allow me to step through the error.

I can ignore the error or I can edit the formula in the formula bar.

Now I'm going to undo just to pull that column back again, which will fix that error.

And then finally, we have an evaluate formula button just here.

And this is really useful if you're trying to debug a complex formula because it allows you to evaluate

each part of that formula in order to pinpoint exactly where the error lies.

So if I select this formula just here and click on Evaluate Formula, it's going to bring up the entire

formula in this little evaluation window and I can go through and evaluate each different part of this

formula.

So currently, I'm evaluating whatever has an underlying underneath it.

So product sales units sold.

So if I click Evaluate, it's going to reveal exactly what that part of the formula is referencing.

So I can see here it's referencing the Pivot Tables worksheet, which we have down here sells see two

to see one two two nine, and that all looks pretty good.

I'm not seeing any underlying errors.

Notice that the line has now moved across to the next part of the formula.

So I can then choose evaluate again, and it's going to evaluate that parts of the formula as well.

And I can carry on going evaluating each part of the formula.

Now notice there.

When I do the evaluation, it changes to Japan.

So it's basically taken a look at the sell reference in the formula and it said, OK, this refers to

the word Japan.

And if you get to any particular part, the formula and you decide you want to step into it, you can

click the step in button.

And again, it's going to show you exactly what that formula is doing.

So if you do have any errors in your formula, you're going to see exactly which parts of the formula

the error is occurring, which is going to help you troubleshoot that formula.

So all of these options in the Formula Auditing Group are super useful when you're working with complex

formulas.