Formula Auditing

Quick reference

• 00:04 In this video we are going to learn how to audit formulas and
• 00:07 evaluate them to see what's actually driving the results that we get.
• 00:12 You'll notice that I have a few different cells in blue here.
• 00:14 Each of these has a different formula in it.
• 00:17 And I've got a set of data on the left hand side which
• 00:20 looks like the salaries for hockey players in Canada.
• 00:23 Now I'd like to try and
• 00:24 figure out what is driving the $34 million that's actually showing up in this area.
• 00:29 And I've got a couple of ways that I can do that.
• 00:31 The first is to just click inside the cell.
• 00:34 And this will actually draw a box around all of the different
• 00:37 ranges that are being used in these particular formulas.
• 00:40 So in this case, you can see that it's D4 through D11.
• 00:44 The other way that we can do this is we can actually go to the formulas tab.
• 00:49 And we can click trace precedence.
• 00:52 And trace precedence does something in addition to this.
• 00:54 It also it draws that blue box around the formulas that are being used still, but
• 00:58 then it puts an arrow across to the cell, so
• 01:01 this is kinda nice because I can actually print this and it will show up on paper.
• 01:05 So if I'm trying to show somebody how one of my models works,
• 01:09 I can put trace press some arrows on it, print it out, take it to them, and
• 01:12 they can see where the flow is.
• 01:14 And I can give them some comfort that I've actually built it correctly.
• 01:17 If I wanna get rid of that arrow, I can click Remove Arrows.
• 01:21 I could go to my Average Salary.
• 01:24 Again, I could Trace Precedents here and
• 01:26 you can see that the same area is being filled into this and
• 01:30 maybe I want to try and figure out if the other one is still working the same way.
• 01:35 I can click Trace Precedents again you can see that these layer on top of each other,
• 01:38 so I can get multiple different precedent arrows in one place.
• 01:43 Likewise, I can come to the other side and
• 01:45 I can say, let's take a look at this cell, let's go Trace Dependence.
• 01:50 And what dependence does, it says,
• 01:51 these are all the formulas that are actually relying on this particular cell.
• 01:56 And this is great.
• 01:57 Because, what this means is if you've ever seen this where somebody's gone in and
• 02:01 they've actually gone right click and delete a column, and it turns everything
• 02:05 to reference errors, well this is the way that you guard against that.
• 02:09 So I'll just undo this and I say, gee I think I'm gonna delete this column.
• 02:13 I'm gonna click in this cell and say, Trace Dependents.
• 02:15 Oh hey look, it's gonna be used somewhere else.
• 02:17 I probably shouldn't delete it, or at least I should replace these formulas with
• 02:20 hard coded numbers before I do, so it doesn't cause problems.
• 02:25 Now, one of the neat things about these as well is that I can actually take
• 02:29 a look at cells that have bigger sets of precedents as well.
• 02:33 So you can see this one is using two columns to feed the one formula.
• 02:38 If I've got multi step formulas here,
• 02:42 watch what happens when I click Trace Precedents.
• 02:44 It says yep, you're looking at this one here.
• 02:47 If I now go and click trace precedence again,
• 02:51 it tells me the rest of the cells that are feeding this as well.
• 02:54 And it's not the cells that are feeding this formula
• 02:58 It's actually now the cells that are feeding this formula.
• 03:00 You can see it's got all kinds of weirdness going on here,
• 03:03 and it's pulling from a bunch of different individual cells.
• 03:05 If I click in the formula bar now I can see where all of those are.
• 03:08 So this is a really handy way to be able to actually go back and
• 03:11 figure out what's going on, is you can keep clicking trace precedents.
• 03:16 Or keep clicking trace dependents and
• 03:18 it will actually extend the chain a little bit further.
• 03:22 Now one of the things that's really important to understand about Excel too is
• 03:26 that it does follow the mathematical order of operations whether you learned
• 03:29 that as BODMUS, or BEDMAS, or PEMDAS, or whatever the acronym is you used,
• 03:34 it basically goes through evaluating brackets, exponents, multiplication,
• 03:38 division and then addition and subtraction.
• 03:40 Now, if you want to see how that works in your formula,
• 03:43 there is a trick that I can show you that will let you evaluate what cells are in
• 03:47 what places, but I'm gonna warn you that it is extremely dangerous to use.
• 03:52 The way that this happens is we can say, I wanna figure out what's in cell D4.
• 03:57 So I'm going to press F9, and it will change it to 610,000, whatever it is.
• 04:01 If I want to figure out what's in cell A8, I can highlight that and press F9.
• 04:07 I could then highlight my six to the power of two and press F9.
• 04:11 And then I could highlight the entire sets of brackets and
• 04:15 I could step through this very, very carefully.
• 04:16 It's a little bit painful, but at the end of the day, I could actually go back and
• 04:20 I could evaluate each individual section of this entire formula
• 04:24 to figure out how it actually goes and calculates to the end number.
• 04:28 Why is it dangerous?
• 04:30 Because right now, if I press return or enter on this, and I go back and
• 04:34 look, it's now a hard coated number, it doesn't go and
• 04:37 put it back into what it should have been.
• 04:39 So I need to edit that, or undo it.
• 04:42 If I'm going through and testing this function to see what's going on in here,
• 04:46 I always wanna make sure at the end that I press escape so it doesn't lock it in and
• 04:50 it keeps my formula alive.

Lesson notes are only available for subscribers.