Formula Auditing

Quick reference

• 00:04 In this video I'd like to show you a real world problem.
• 00:07 And that is where you inherit a workbook from someone else,
• 00:10 so it's got formulas in it but you don't know what they are.
• 00:13 Kind of like this one.
• 00:15 So what we're gonna do is we're gonna try and figure out what's driving things here.
• 00:18 We're gonna start with cell G6 that's giving us a total of $34 million.
• 00:24 Now the fastest way to figure out where this is coming from is actually to go and
• 00:27 click in the formula bar.
• 00:29 And you'll see that it highlights all of the cells that are actually
• 00:31 being referred to.
• 00:32 In this case it's wrapped them in a sum function, so we can be pretty sure
• 00:36 that it's adding all these things together in order to return the $34 million.
• 00:40 But there is another way to look at this as well.
• 00:43 We could go to the formulas tab and on the formula auditing group,
• 00:46 we could click the button Trace Precedents, and
• 00:49 this doesn't require actually clicking in the formula bar.
• 00:52 You notice it draws a box around it and it points an arrow directly into the cell,
• 00:56 which is kind of neat.
• 00:58 The other thing that's really cool about this is that these arrows print.
• 01:01 So if you need to see this and you actually put it out in front of someone,
• 01:04 you can do that.
• 01:05 Now I'm gonna get rid of these arrows by clicking remove arrows.
• 01:09 I'm gonna go to average salary.
• 01:11 We'll click Trace Precedents here.
• 01:13 Notice that the box is around the same area and feeding into this one,
• 01:16 which we wrap in an average function.
• 01:18 All right, that makes sense.
• 01:21 Let's remove the arrows again, and we'll go to this random information cell, and
• 01:26 we'll click Trace Precedents.
• 01:27 And at this point you can see the stuffs coming from all over the place in single
• 01:31 cells, because it doesn't have a big box wrapped around it.
• 01:35 If I go and click in the formula bar, you'll notice that it actually highlights
• 01:38 all of the different components of different colors so
• 01:40 that we can see which cells are being referred to.
• 01:43 And with the help of the arrows and the referencing that we have here,
• 01:46 we get a little bit more information we can look at, which is pretty good.
• 01:50 Now I'm gonna hit Escape to get out of the formula bar.
• 01:53 And you'll notice the arrow stick around.
• 01:54 I'm gonna click Trace Precedents one more time.
• 01:58 And you'll notice that because the 34 million came from somewhere and this cell
• 02:02 feeds into a 110 million here, it actually sets up the precedents for this as well.
• 02:07 So now things start to get really messy.
• 02:09 But it does give us an ability to go back and audit things.
• 02:13 I'm gonna click Remove Arrows and now we're gonna go the other way.
• 02:16 We'll go to Daniel Sedin's salary here, and we're gonna click Trace Dependents.
• 02:22 Notice that Daniel Sedin's salary feeds into three different cells.
• 02:26 And again, if I go and click Trace Dependents again,
• 02:29 we'll see that the 34 million gets another arrow that actually feeds into this.
• 02:33 So we can keep clicking the Trace Precedents and
• 02:36 Trace Dependents buttons to see where these cells are used.
• 02:39 This is actually really important because if you're ever thinking
• 02:42 about deleting a column, maybe you should click inside that cell first and
• 02:46 say trace dependents, because if it has dependence,
• 02:48 deleting the column is going to fill it with ref errors everywhere.
• 02:52 So it's a good way to check and make sure that you don't get into that situation.
• 02:57 Now let's remove these arrows again, and
• 02:59 let's go down to this weird random information cell.
• 03:02 So we know that this is coming from all over the place.
• 03:05 That's not really helpful.
• 03:07 I wanna see how this is actually calculated.
• 03:09 So for that, we have the ability to go and step into the Evaluate Formula button.
• 03:14 This will actually pull up a metric here that allows us
• 03:18 to actually evaluate step by step an entire formula.
• 03:22 Now you notice that Excel does follow the order of operations,
• 03:26 whether you know it as BODMAS, or BEDMAS, or PEMDAS, or
• 03:29 something else, it always works with brackets, exponents, then multiplication,
• 03:34 division, and then finally, addition and subtraction.
• 03:37 You're gonna see that as we actually go rack through this.
• 03:41 The first thing you'll notice is that we have underlined D6.
• 03:44 This is part of the formula as you can see over here.
• 03:48 And the underline signifies the part that Excel is
• 03:50 actually going to going through and evaluate first.
• 03:53 And I can go and say, evaluate, and it says, well D6,
• 03:56 which we can see right here, is $6.1 million.
• 04:01 It then says, I'm gonna multiply D6 times two,
• 04:03 because that's the next thing to do in the order of operations.
• 04:06 There we are, we get 12.2.
• 04:09 Next it's going to evaluate D11, but
• 04:11 I want to know what this is before I click the Evaluate button.
• 04:14 So I'm going to click Step In, and this will actually step right into the cell,
• 04:19 select it, and show me that this is $5.3 million.
• 04:22 So now that I know that, I'll click Step Out and
• 04:25 it does essentially what the Evaluate button did, and brings the value over.
• 04:29 But notice the next piece against highlighted is six to the power of two.
• 04:33 Because we can't do addition, because that's not in the proper order of
• 04:36 operations, there's a multiplications here.
• 04:38 So we need to step inside the parenthesis.
• 04:41 We'll evaluate 6 to the power of 2 which is 36, we'll go and take a look at A10,
• 04:46 let's go Step In and see what that one is, ahh, it's 3, we'll Step Out.
• 04:52 We'll evaluate 36 divided by 3, and then we'll evaluate the entire
• 04:57 contents of the parenthesis, multiply it by the 5.3 million,
• 05:02 added to the 12.2, and finally we'll evaluate G6.
• 05:06 And we'll evaluate the final piece which comes out 110 million.
• 05:10 And of course, if we wanna do this again, we could restart.
• 05:13 So this is a helpful tool that allows you to step through each step of your
• 05:17 formula and combined with the trace precedents and
• 05:19 dependencies and marking when you go in and actually edit a formula here.
• 05:23 You have lots of tools to try to figure out exactly what's going on with any
• 05:27 formula inside Excel.

Lesson notes are only available for subscribers.