Formula Auditing

Quick reference

• 00:04 In this video we are going to look at the formula auditing and evaluation tools inside Excel to see how they work. You can see
• 00:11 that we have a little table of information over here and we've got customer numbers and
• 00:14 dates and names and purchases and they're all out of order. But that's ok because
• 00:18 the purpose of what we are going to do here we are going to evaluate the formulas between G4
• 00:22 and G8. So the first thing I'm going to do is I'm going to select G4
• 00:26 and then I'm going to go to the formulas tab and I'm going to click on the trace precedents button.
• 00:32 And what the trace precedents button does is it highlights all the individual cells
• 00:36 and ranges that are actually feeding into this particular formula. As you can see
• 00:40 it says =SUM(D4:D11) and it's actually drawn a box around D4 through D11.
• 00:46 So we know that this whole range is being fed into this particular formula.
• 00:52 The nice thing about these lines and arrows boxes and whatnot is that they print as well. So if you are actually trying to explain to somebody
• 00:59 or you need a note for file as to how your spreadsheet works you can actually set these up,
• 01:02 click print and it will actually show up exactly like this on the worksheet
• 01:05 when it prints out which is really handy for handing it off to an auditor.
• 01:09 Now we can also work with multiple precedent traces at the same time. So if I'm in another cell click trace precedents.
• 01:17 What's happening here is it's saying this box also feeds into this particular cell
• 01:21 but if I wasn't convinced maybe it's only one cell that's feeding that way I could
• 01:25 remove the arrows and then click trace precedents again and it will show me absolutely
• 01:29 this is this whole range that's going this direction.
• 01:31 What about this next one here. The sum of purchases where customer numbers are greater than three. If I click trace precedents I now have
• 01:39 two boxes that are actually picked up and pointing back into this particular cell.
• 01:44 Again I can remove the arrows and I can trace precedents again to see, yup, that's definitely what's happening here.
• 01:50 The last one that I'm going to look at is the something bizarre formula here
• 01:54 when I click trace precedents on this you can see that this one
• 01:57 actually feeds from individual cells rather from
• 02:01 rather than the actual blocks. Now what gets kind of interesting
• 02:04 here too is that we can actually continue to walk this chain backwards. If I go and say well
• 02:08 G4 is feeding into this particular formula, where does G4 feed from?
• 02:12 I can actually click on that and it'll take me back as well so you can actually walk all the way
• 02:16 down through a chain of commands to see how it all fits together which is quite cool.
• 02:21 Now conversely we also have another command called trace dependents and what trace dependents does, if I go and select D4 and
• 02:29 trace the dependents it will show me every single cell in the workbook that is actually using this cell in a calculation. So
• 02:37 this is a really handy command to be aware of when you're trying to decide; can I delete the cell or do I need to leave it around? If you
• 02:44 click trace dependents it will tell you right away whether or not there's any dependents
• 02:48 on it. If I go and click on Bill Shatner here and a trace dependents
• 02:52 you can see it comes up and says that there were no dependents found for this particular cell
• 02:57 which means that I could actually delete the contents here and it wouldn't be a problem.
• 03:02 Now another thing that's really really handy in Excel is that sometimes you are looking at a formula like this one in G7 and it's pretty long
• 03:10 and horrendously ugly and you can't figure out exactly what it means. Well the nice thing
• 03:13 is we have a tool to help us with that. That's the Evaluate Formula tool.
• 03:17 When I click on that you'll notice that the evaluate formula in the evaluation box I actually get the
• 03:23 contents of the entire formula as it's written in the formula bar.
• 03:28 But D4 is underlined and that's because Excel is going to evaluate this as soon as I click the evaluate button here. When I click D4 you'll
• 03:36 notice that it comes back with 1188 which happens to be the value in D4.
• 03:41 Next thing it says is I'm going to multiply 1188 by 2 and it's underlined . When I click evaluate
• 03:48 it works out the math for me.
• 03:50 Next thing it's going to do is it's going to convert D9 into a value. D9 is right here. 7568 we'll click evaluate. There we go.
• 03:58 Now it steps inside the brackets to actually go through and evaluate the rest of the steps. So as I click
• 03:59 each piece will be evaluated and
• 04:07 eventually I'm going to come to an answer that shows me exactly how it got
• 04:13 to 66,280. And then I can restart if I want or I can close and get out of here.
• 04:19 This is a really, really handy tool for debugging things like VLOOKUP statements that aren't working when you're getting a #N/A error or other
• 04:27 things like that. It allows you to step through each piece individually. Just select the formula go through click Evaluate Formula
• 04:33 and step through each step be aware that it does follow the Order of Operations.
• 04:38 Whether you learned that as BODMAS, PEMDAS or some other acronym for it
• 04:42 but it always deals with the brackets, exponents first then the multiplication, division
• 04:46 then the addition and subtraction afterwards. So really really handy to know, really really handy
• 04:51 to use when you are trying to get through something complicated.

Lesson notes are only available for subscribers.