Formula Auditing

Quick reference

• 00:04 In this video, we're going to deal with a real world problem.
• 00:07 And that is where you open up a workbook.
• 00:10 And you look at a formula in it, and
• 00:12 you go I don't know what that's actually supposed to be doing.
• 00:15 Or why is it doing what it's doing?
• 00:17 So what I'm gonna do is,
• 00:18 I'm gonna show you some of the formula auditing tools that we have.
• 00:22 The number one tool, when we select the cell is,
• 00:25 we can click inside the formula bar.
• 00:27 And it draws color around in a box around the cells that are being used as input.
• 00:31 So in this area here, you can see that we're summing D4:D11, and
• 00:35 it actually draws a box around that.
• 00:38 Now we also have another feature, that can actually point to the cells that
• 00:43 are feeding into our formula, and this is called trace precedence.
• 00:47 It's on the formulas tab, when we click trace precedence,
• 00:51 it will draw an arrow into the cell that we're actually working with.
• 00:55 And that's kind of nice, because now we can actually see,
• 00:58 exactly where in the worksheet stuff is coming from.
• 01:01 Now, there's a remove arrows button here that will actually remove them.
• 01:05 Let's go take a look at average salary.
• 01:07 Click trace precedence.
• 01:09 Looks like the same cells are feeding in to average salary.
• 01:12 Probably makes sense, but what about this random information cell?
• 01:15 If I click Trace Precedence there, you'll see that it comes from all over the place.
• 01:20 And when there's multiple areas that are feeding in,
• 01:22 it doesn't actually draw boxes around them.
• 01:25 But we can fix that too.
• 01:27 We can just click inside the formula bar.
• 01:29 And it will actually show us, and
• 01:31 color code where all these cells are coming from.
• 01:33 So, this is actually using a mixture of the cell highlighting from before
• 01:38 plus the trace precedent arrows.
• 01:40 And I'll just press escape to get out of that formula now, so we can go back and
• 01:44 look at things.
• 01:46 Let's click Remove Arrows.
• 01:48 Let's go the other way.
• 01:49 Let's go over to Daniel Sedin's salary here and let's click Trace Dependents.
• 01:54 So dependents are where is this cell being used?
• 01:59 You can see it's being used in three locations.
• 02:01 What's kind of interesting about this is that if you click it again.
• 02:06 You'll notice that it draws another arrow here.
• 02:08 Let's click Remove Arrows and let's see why.
• 02:09 We'll click trace dependents the first time.
• 02:14 We'll come and select this next cell and we'll trace the dependents here.
• 02:18 Basically, this is replicating the same thing.
• 02:21 So if you click trace dependants multiple times, it keeps feeding through
• 02:25 the next step of the dependency chain to see where things go.
• 02:28 So that can be kind of handy as well.
• 02:31 Interestingly enough, all of these print on a worksheet.
• 02:34 So if you print this out,
• 02:35 you would actually have these arrows to show somebody how this actual flow
• 02:39 worked and was put together, which can be very helpful too.
• 02:42 So I'm gonna say remove arrows here.
• 02:44 And what I'd like to do now is I'd like to go down to this random information.
• 02:48 And say, let's figure out how this is actually calculated and what it's doing.
• 02:52 Because this thing obviously is a bit of a mess.
• 02:54 And we don't really know, even when we say Trace Precedents.
• 02:58 And actually show the cells here, how this is actually working.
• 03:03 So what we're gonna do is we're gonna click on Evaluate Formula.
• 03:07 And what you'll notice is that, Evaluate Formula comes up with a little box
• 03:11 that allows us to actually step in, and see what's going on.
• 03:14 This box follows the order of operations, BADMAS or BEDMAS or PEMDAS,
• 03:18 whatever you like to call it.
• 03:20 But what you'll notice right now is that D4 is underlined.
• 03:23 So Excel is actually looking at D4 and it says, I'm going to evaluate what it is.
• 03:29 So we click on evaluate and it says D4 is $6.1 million.
• 03:34 And then it underlines 6.1 million times 2, and it says, all right,
• 03:38 let's evaluate that.
• 03:40 And the next thing it does,
• 03:41 it steps over the plus because it has to follow the order of operations.
• 03:44 So I need to know what this D9 is, so let's evaluate that.
• 03:48 Okay, there we go, 5.3 million.
• 03:51 And then, we're gonna take six to the power of two because we're
• 03:55 inside the brackets, and exponents come before the division.
• 04:00 So we'll take six to the power of two is 36.
• 04:02 What is A8?
• 04:04 We'll evaluate that.
• 04:05 It's three so 36 divided by 3 is 12.
• 04:08 Let's evaluate the total of the brackets.
• 04:11 Still comes to 12.
• 04:12 Now we do our multiplication here.
• 04:15 Evaluate that, next thing we know we got addition going through so
• 04:18 we'll start with the first one.
• 04:20 Click evaluate, let's figure out what G4 is, cuz now we need to add that too.
• 04:25 And there we go, we can evaluate it and
• 04:28 we come up with the total that's actually here.
• 04:30 This is kind of a nonsensical formula.
• 04:32 But it does show how this thing all works together.
• 04:35 You can restart and keep going.
• 04:37 If you want to see what's inside one of these cells,
• 04:39 where it says D4, you can step in.
• 04:42 And see that this is $6.1 million.
• 04:44 And then you can step out.
• 04:46 And as you start going through, you can do that with all of these individual pieces
• 04:50 here, to figure out what they are.
• 04:55 The place where this actually makes a difference bleep is on G4,
• 04:58 because G4 actually has a formula in it,
• 05:02 which means we can evaluate that formula as well, to see what's going on.
• 05:05 So this can be a handy tool for auditing your formulas.
• 05:09 The last thing I just want to mention to you is,
• 05:11 with Trace Dependence, you'll know that when you right click and
• 05:16 delete a cell if the cells here were referring to it we get #REF errors.
• 05:22 If we actually go and undo this now, the test to make sure you don't get yourself
• 05:26 in that trouble is, before you delete a column, click trace dependence.
• 05:31 If it feeds anything out, don't delete it, it's needed.
• 05:33 You don't wanna get those #REF errors.
• 05:35 So just a handy little tip to keep you with good, well working workbooks.

Lesson notes are only available for subscribers.