Locked lesson.
About this lesson
Learn to work with formula auditing tools including tracing precedent and dependent cells and formula evaluation.
Exercise files
Download this lesson’s related exercise files.
Formula Auditing - Begin.xlsx25.6 KB Formula Auditing - Completed.xlsx
22.8 KB
Quick reference
Formula Auditing
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 One of the challenges you can get when working with formulas, particularly if
- 00:07 you pick up someone else's spreadsheet, is understanding what they do, but
- 00:11 this can also of course happen to your own work if you're trying to figure out why
- 00:14 a formula is or isn't giving you the result you're looking for.
- 00:17 So let's look at some auditing techniques.
- 00:20 The first auditing technique I'm going to show you is that we can actually select
- 00:24 a cell and just click in the formula bar.
- 00:26 And immediately what will happen is it will highlight all of the precedents on
- 00:30 the sheet, all the things that feed into that formula, in different colors.
- 00:34 So, right now we can see we've got one contiguous block here that's indicated by
- 00:39 our blue shading that we have here.
- 00:41 But the good news is I can see that we're covering all the numbers so
- 00:44 this formula is probably doing what I want it to do.
- 00:47 If I then go and take a look at average salary, I can do the same
- 00:50 thing by pressing F2, that will get me into edit mode and you'll notice that all
- 00:54 of the precedents again, are highlighted and we've got the entire range here.
- 00:58 The challenge is though, what it's not showing us here is,
- 01:02 does anything else use these cells?
- 01:05 So let me show you another way to actually show this information.
- 01:09 On the Formulas tab, we have this Formula Auditing group over here, and
- 01:13 if I go and click Trace Precedents,
- 01:15 it will show me all of the things that actually feed into this formula.
- 01:19 And if there happens to be more precedents of the cap hit column here,
- 01:23 if I click Trace Precedents, it would show me those.
- 01:26 Now in this case, there doesn't appear to be.
- 01:28 But what this doesn't still show me is, is anything else using this number?
- 01:34 And that's what Trace Dependence will do for me.
- 01:37 If I click on that, we can see that the value in this cell is
- 01:40 actually being used in this G9 cell here for random information.
- 01:45 So I now have an idea of where this stuff is going, and again, if
- 01:48 I click Trace Dependents it will actually show me even more if there's more or
- 01:53 it dings at me if there aren't.
- 01:55 I'm going to click Remove Arrows now to get rid of this, and what I want to show
- 01:59 you is when we go to Random Information, If I go and click inside, here's all
- 02:03 the different colors that we actually have, which tie back to the formula.
- 02:06 So D6 is showing in blue, D11 is showing in red, A10 in purple and
- 02:12 G6 over here in a slight green on top of my blue background.
- 02:16 Let's look at this with the Trace Precedents command.
- 02:19 And there we go,
- 02:20 we can now see all of these arrows that are actually filling in here.
- 02:24 So this is really cool because it gives us a nice view of where information's
- 02:28 coming from, and if you actually print this worksheet, it even shows up in print,
- 02:32 which is pretty nice.
- 02:33 Having said that, if you save the file, close it and reopen it, the arrows always
- 02:37 go away, you don't actually have to remove them if you don't want to.
- 02:40 I'm going to click Remove Arrows for right now,
- 02:43 because what I also want to show you is how this particular formula,
- 02:47 this nasty looking thing here, is actually calculated.
- 02:51 And to do that, I can actually walk through the calculation step by
- 02:55 step using the Evaluate Formula button.
- 02:57 This brings up a little box here, and it is tiny that shows you the entire formula,
- 03:02 and you'll notice that D6 is underlined.
- 03:05 And what's going to happen here is I'm going to evaluate this,
- 03:09 step by step as we go through every step of the calculation, and
- 03:13 what you're going to see is it follows the acronym BEDMAS or potentially BODMAS or
- 03:18 BEMDAS or however you actually learned it back in school.
- 03:22 But for me, it was always BEDMAS which was brackets, exponents, division,
- 03:26 multiplication, addition, subtraction.
- 03:29 We can see that we've got something here that's multiplying first.
- 03:31 So what I'm going to do is I'm actually going to click Evaluate and
- 03:35 you'll notice that it goes back and
- 03:36 converts that cell value into the numeric constant that underlies it.
- 03:40 It's then underlined then it's going to to multiply it by 2, so
- 03:44 I'm going to to click on that and we get to this value.
- 03:47 Then comes along and says, okay, well, D11, let's figure out what this is.
- 03:50 And I'm actually going to look at this one, I'm going to do a step in.
- 03:54 And this says, hey, D11 has the value 5.3 million in it, that's cool.
- 03:59 So now when I step out, it automatically does the evaluation of that value for you.
- 04:03 Now, remember I said this follows BEDMAS,
- 04:06 we're now into our brackets or parenthesis in here.
- 04:10 Why didn't it evaluate those first?
- 04:12 Well, because it doesn't need to, it can step across left and right,
- 04:15 but it notice that there's an addition here and
- 04:17 it can't evaluate addition until the other things in the equation are done.
- 04:20 It worked out what this value was but we're multiplying it by something within
- 04:24 the parenthesis, so the inside of the parenthesis needs to be evaluated first.
- 04:28 We have 6 to the power of 2, we'll evaluate that, comes to 36.
- 04:32 We'll take a look at A10, step in, that says that A10's value is 3,
- 04:37 so we'll step out, which forces the evaluation.
- 04:41 We've got 36 divided by 3, we'll evaluate that, the value within the parenthesis is
- 04:45 12 so we're just going to evaluate that, which will remove the parenthesis from it.
- 04:50 Now we can do our multiplication, evaluate that,
- 04:53 these two numbers can be added, we'll do that one next.
- 04:58 And finally we get to G6.
- 04:59 Now again, I could step in and then step out, but I'm just going to force
- 05:02 the evaluation of this without actually looking at the cell value.
- 05:05 We'll say Evaluate, and finally, Evaluate one more time and we get to 110 million.
- 05:11 And that's basically how the formula evaluation tool works.
- 05:14 If you want to walk through it again, you can click Restart, or
- 05:17 you can close at any time.
- 05:18 So those are your formula auditing tools.
Lesson notes are only available for subscribers.