Formula Auditing

Quick reference

• 00:04 One of the challenges you can get when working with formulas, particularly if
• 00:07 you pick up someone else's spreadsheet, is understanding what they do, but
• 00:11 this can also of course happen to your own work if you're trying to figure out why
• 00:14 a formula is or isn't giving you the result you're looking for.
• 00:17 So let's look at some auditing techniques.
• 00:20 The first auditing technique I'm going to show you is that we can actually select
• 00:24 a cell and just click in the formula bar.
• 00:26 And immediately what will happen is it will highlight all of the precedents on
• 00:30 the sheet, all the things that feed into that formula, in different colors.
• 00:34 So, right now we can see we've got one contiguous block here that's indicated by
• 00:39 our blue shading that we have here.
• 00:41 But the good news is I can see that we're covering all the numbers so
• 00:44 this formula is probably doing what I want it to do.
• 00:47 If I then go and take a look at average salary, I can do the same
• 00:50 thing by pressing F2, that will get me into edit mode and you'll notice that all
• 00:54 of the precedents again, are highlighted and we've got the entire range here.
• 00:58 The challenge is though, what it's not showing us here is,
• 01:02 does anything else use these cells?
• 01:05 So let me show you another way to actually show this information.
• 01:09 On the Formulas tab, we have this Formula Auditing group over here, and
• 01:13 if I go and click Trace Precedents,
• 01:15 it will show me all of the things that actually feed into this formula.
• 01:19 And if there happens to be more precedents of the cap hit column here,
• 01:23 if I click Trace Precedents, it would show me those.
• 01:26 Now in this case, there doesn't appear to be.
• 01:28 But what this doesn't still show me is, is anything else using this number?
• 01:34 And that's what Trace Dependence will do for me.
• 01:37 If I click on that, we can see that the value in this cell is
• 01:40 actually being used in this G9 cell here for random information.
• 01:45 So I now have an idea of where this stuff is going, and again, if
• 01:48 I click Trace Dependents it will actually show me even more if there's more or
• 01:53 it dings at me if there aren't.
• 01:55 I'm going to click Remove Arrows now to get rid of this, and what I want to show
• 01:59 you is when we go to Random Information, If I go and click inside, here's all
• 02:03 the different colors that we actually have, which tie back to the formula.
• 02:06 So D6 is showing in blue, D11 is showing in red, A10 in purple and
• 02:12 G6 over here in a slight green on top of my blue background.
• 02:16 Let's look at this with the Trace Precedents command.
• 02:19 And there we go,
• 02:20 we can now see all of these arrows that are actually filling in here.
• 02:24 So this is really cool because it gives us a nice view of where information's
• 02:28 coming from, and if you actually print this worksheet, it even shows up in print,
• 02:32 which is pretty nice.
• 02:33 Having said that, if you save the file, close it and reopen it, the arrows always
• 02:37 go away, you don't actually have to remove them if you don't want to.
• 02:40 I'm going to click Remove Arrows for right now,
• 02:43 because what I also want to show you is how this particular formula,
• 02:47 this nasty looking thing here, is actually calculated.
• 02:51 And to do that, I can actually walk through the calculation step by
• 02:55 step using the Evaluate Formula button.
• 02:57 This brings up a little box here, and it is tiny that shows you the entire formula,
• 03:02 and you'll notice that D6 is underlined.
• 03:05 And what's going to happen here is I'm going to evaluate this,
• 03:09 step by step as we go through every step of the calculation, and
• 03:13 what you're going to see is it follows the acronym BEDMAS or potentially BODMAS or
• 03:18 BEMDAS or however you actually learned it back in school.
• 03:22 But for me, it was always BEDMAS which was brackets, exponents, division,
• 03:26 multiplication, addition, subtraction.
• 03:29 We can see that we've got something here that's multiplying first.
• 03:31 So what I'm going to do is I'm actually going to click Evaluate and
• 03:35 you'll notice that it goes back and
• 03:36 converts that cell value into the numeric constant that underlies it.
• 03:40 It's then underlined then it's going to to multiply it by 2, so
• 03:44 I'm going to to click on that and we get to this value.
• 03:47 Then comes along and says, okay, well, D11, let's figure out what this is.
• 03:50 And I'm actually going to look at this one, I'm going to do a step in.
• 03:54 And this says, hey, D11 has the value 5.3 million in it, that's cool.
• 03:59 So now when I step out, it automatically does the evaluation of that value for you.
• 04:03 Now, remember I said this follows BEDMAS,
• 04:06 we're now into our brackets or parenthesis in here.
• 04:10 Why didn't it evaluate those first?
• 04:12 Well, because it doesn't need to, it can step across left and right,
• 04:15 but it notice that there's an addition here and
• 04:17 it can't evaluate addition until the other things in the equation are done.
• 04:20 It worked out what this value was but we're multiplying it by something within
• 04:24 the parenthesis, so the inside of the parenthesis needs to be evaluated first.
• 04:28 We have 6 to the power of 2, we'll evaluate that, comes to 36.
• 04:32 We'll take a look at A10, step in, that says that A10's value is 3,
• 04:37 so we'll step out, which forces the evaluation.
• 04:41 We've got 36 divided by 3, we'll evaluate that, the value within the parenthesis is
• 04:45 12 so we're just going to evaluate that, which will remove the parenthesis from it.
• 04:50 Now we can do our multiplication, evaluate that,
• 04:53 these two numbers can be added, we'll do that one next.
• 04:58 And finally we get to G6.
• 04:59 Now again, I could step in and then step out, but I'm just going to force
• 05:02 the evaluation of this without actually looking at the cell value.
• 05:05 We'll say Evaluate, and finally, Evaluate one more time and we get to 110 million.
• 05:11 And that's basically how the formula evaluation tool works.
• 05:14 If you want to walk through it again, you can click Restart, or
• 05:17 you can close at any time.
• 05:18 So those are your formula auditing tools.

Lesson notes are only available for subscribers.