Locked lesson.
About this lesson
Learn to work with formula auditing tools including tracing precedent and dependent cells and formula evaluation.
Quick reference
Topic
Formula auditing.
Description
Working with formula auditing tools including tracing precedent cells, dependent cells, and stepping through formula evaluation.
Where/when to use the technique
These tools are incredibly useful for debugging spreadsheets that have errors, or trying to understand the business logic that someone built in the file.
Instructions
Tracing precedents
- Select a cell with a formula in it
- Select FORMULAS then Trace Precedents
- Arrows will be drawn on the worksheet showing all ranges that feed into that cell.
Tracing dependents
- Select any cell on the worksheet
- Select FORMULAS then Trace Dependents
- Arrows will be drawn on the worksheet showing all cells that rely on the selected cell.
Formula evaluation
- Select a cell with a formula in it
- Select a cell reference and press F9
- Notice that Excel follows the Order of Operations (BEDMAS, BODMAS, PEMDAS or whichever acronym you may know it by.)
- DO NOT PRESS ENTER when you are done, as this will commit the number (not the formula) to the cell. (Press ESC instead.)
- 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.