Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.