Locked lesson.
About this lesson
Learn to work with formula auditing tools including tracing precedent and dependent cells and formula evaluation.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the Excel workbook used in the video tutorial and try the lesson yourself.
Formula Auditing.xlsx15.1 KB
Quick reference
Topic
Working with formula auditing tools including tracing precedent cells, dependent cells, and stepping through formula evaluation.
When to use
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
- On the FORMULAS tab, click 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
- On the FORMULAS tab, click 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
- On the FORMULAS tab, click Evaluate
- As you click Evaluate, Excel will evaluate the underlined portion of the equation
- Notice that Excel follows the Order of Operations (BEDMAS, BODMAS, PEMDAS or whichever acronym you may know it by)
- 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.