Formula Auditing Tools in Excel

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 17 courses • 391,860 students

Lecture description

Here's the scenario: you've spent the past 852 hours building a incredible, fancy-pants Excel model, but now -- mere moments before you present it to the entire company -- you see the dreaded #REF! error where you should be seeing projected Q4 profits. What do you do? If you're a power user, you flash a confident smile, turn to your trusted formula auditing tools, and quickly identify the source of the problem to a thunderous round of applause. If you aren't a power user, you quietly curl into the fetal position until you are promptly fired. In this tip, I'll walk through some of Excel's most useful formula auditing and error checking tools (trace precedents/dependents, evaluate formula, etc.).

Learn more from the full course

Excel PRO TIPS: 75+ Tips to go from Excel Beginner to Pro

Excel tips & hands-on demos to become an Excel POWER USER. Learn Excel formulas, pivot tables, charts, analytics & more!

10:28:10 of on-demand video • Updated November 2020

  • Build tools to help you automate, streamline, and absolutely revolutionize your workflow with Excel
  • Explore 75+ unique tips, tools and case studies that you won't find in ANY other Excel course, guaranteed
  • Get LIFETIME access to resources, Excel project files, quizzes, and 1-on-1 expert support
  • Master Excel's powerful data analysis tools like Goal Seek, Scenario Manager, Solver, and Analysis ToolPak
  • Practice with fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Become an absolute Excel POWER USER
English [Auto] This next tip is all about finding and fixing errors in your worksheet using formula auditing tools. Now in your formulas tab you'll find a group of individual tools under the formula auditing category that are designed to help you trace references evaluate your formula calculations and ultimately diagnose and fix any errors in your worksheet. So we're going to talk about all of these different options in this demo. We're going to start with tracing precedents independence which basically draws arrows to any cells that either impact or are impacted by the selected value. Kind of like a parent child relationship. And here's an example of what that looks like. In this case which we'll talk through in our demo. We're tracing the precedents that impact these cash to close and monthly expense values so that we can understand which input cells will impact those values or those outputs. We also have a number of other tools we're in to talk about show formulas is simply going to temporarily display any worksheet formulas as text and then error checking is going to scan the sheet for errors. Help us trace the source back to any precedent cells. And finally my personal favorite The evaluate formula tool that's going to allow us to actually evaluate every single individual component of a function or formula step by step. And that's great for pinpointing where a formula might be breaking especially if you have a very complex formula or a number of nested functions. So a ton of practical use cases here. I for one simply understanding how some of these complex formulas and functions might be operating. Number two visualizing which cells factor into a certain formula output or cell and then three tracing diagnosing and hopefully fixing the source of any errors that you run into. So that let's jump into the demo get hands on in are protip workbook and practice working with some of these auditing tools. All right so if you're following along go ahead and open up your protip workbook look for the formula auditing tools demo in the formula tip section and go ahead and press linked to jump straight to that sheet. And what we're looking at here is a property calculator. Basic model that I actually use to evaluate property and loan costs and essentially what's going on here is you've got some basic information about a property got a purchase price and a tax rate. You can enter some loan terms here like a down payment an interest rate and a term like. And those values will run through a number of calculations and ultimately spit out the cash required to close on the property and an estimate for monthly expenses. But obviously we're not here to talk about mortgage payments and property costs right now we're here to talk about evaluating formulas. So with that let's go into our formulas tab. Take a look at our formula auditing group which contains those tools that we talked about. And let's start by talking about precedence and dependence. Because it sounds very complicated. It's actually quite simple. And one way to think about it is you know for this monthly expense value in the cell that I have selected this is kind of the end of the calculation follow this monthly expense number doesn't feed into any other formulas. No other output cells are impacted by this monthly expense value. In other words this cell has no dependant cells. And I can prove that by clicking trace dependence and I'll get this kind of warning message that says there are no formulas that refer to this active cell but there are cells of course that serve as inputs to this monthly expense value. In other words there are cells that act as precedence to the selected cell and to show those precedents. All I need to do is click that button and it's going to say Yep these five values here H 14 through age 18 all impact this monthly expense number. In other words if you change any of these values monthly expenses will change as well. Same story here with cash to close. There are no dependents. This is the end of the calculation flow. But there are a number of precedents. So the cash to close value is a function of purchase price down payment and estimated closing costs. Now another way to kind of tell a similar story here. We can remove the arrows is to select a formula cell and either click into the formula bar or use the two shortcut to end it. And what this will do you won't get the arrows but you'll still see selected cells that are referenced within your formula. And what I actually like about this approach is that they're color coded as well. So you can kind of map them to the individual components within that formula. Just yet another tool that you can put in your back pocket to help you diagnose and understand your formulas. So go ahead press enter. And we've traced precedents from these cells. But the opposite relationship holds true as well. So we know that Castillos was a dependent of purchase price therefore purchase price is a precedent to cashed close and we can show that by tracing the dependents from this cell. And now this is showing us is that if we change purchase price there are 1 2 3 4 5 cells or outputs that are going to change as a result. There are five dependent cells based on this value and one cool tip that I didn't discover until just recently is that this only shows you the first step of the calculation path when in turn some of these dependent cells also have additional dependents from there. So purchase price impacts loan amount. Closing costs and property tax. But if we click trace dependence again now we can see there's another layer here where the property tax value as we've shown also impacts this monthly expense output. So now we're seeing kind of the full chain of events and the full calculation flow across multiple steps really powerful tool. Let's go ahead and remove those arrows I'll show you what the show formulas tool looks like. Pretty straightforward. It just stretches out your cell so that it can show the actual formulas as text kind of all side by side. Good way to compare you know which cells in a sheet are constants and which ones are formulas and then can simply toggle that on or off. Now onto my favorite option the evaluate formula tool. Let's pick one of these formulas here something like property tax. Click evaluate formula. It's going to pull up this dialog box and you'll see right from the start. The exact same formula that you'll see in your formula bar. But what you can do now is actually look at the underlined component when you click evaluate here just that underline component will evaluate. So it just said that H-3 evaluates to four hundred ninety nine thousand because H-3 is the purchase price. And then when that gets divided by a thousand that evaluates to 499. And as we step through we can continue the process. H four is nine point five nine point five times for ninety nine. It's forty seven forty point five divided by 12. Gives us that 395 monthly property tax payment. So again great way to kind of understand your formulas at a deeper level. And there's one other feature too that evaluate formula tool that can be helpful. Show you that we're going to go to cash to close formula and we're going to evaluate that one and it's pretty simple formula. Three times seven plus H 11 H 3 is 499 h 7 this point to multiplied together ninety nine eight hundred. Now here's the thing we get to h 11 h 11 contains a value of ninety nine eighty but that ninety nine eighty isn't a static hard coded value. It's produced itself from another function. So this step in button allows you to say OK H 11 is a little bit more complicated. Let's see how h 11 itself is being produced and by going one level deeper by peeling back kind of one more layer of the onion we can now see that H 11 is calculated as h three times point zero two can evaluate this formula kind of nested within our original one and then step back out and say okay that's how we're getting to the 99 80. And then when we add them up we get to our final product which is 1 2 9 7 80. So there you have it. That's evaluate formula. Obviously everything looks a lot easier and simpler when things are going well. So let's walk through one quick sample of when things might not be functioning quite as you'd expect. So let's say we want to enter a down payment percent of 10 percent but our finger slips whoops and we type 10 percent you know obviously something's wrong here we've got four cells that are now spitting out a value error. This is a good time to use that error checking tool here which will actually cycle through your sheet and say hey we found a bunch of errors first ones and sell nine. You click next to another one at age 10. Got one in each 14 one in just three. And then you'll have a bunch of options here for each of the errors help on this error will take you to the office support web site show calculation steps will actually take you to the evaluate formula tool and specifically to the step that yields the error. And this right here will show me that we're trying to multiply. Four hundred ninety nine thousand which is a value by this text string surrounded by quotes 10 P percentage which evaluates to that value error which obviously as you carry it through is just going to yield a final error at the end of the day. So let's close that out. One other way that you can use this error checking tool. We go to the next error it completed it. This is the one we want. Monthly expenses go back and earn checking. Sometimes you get this option to actually trace the error as well. And when you do that it will populate those precedent arrows with one difference. It will turn the arrow red at the stage where the error is taking place. And this is a nice way to kind of trace things back and say OK step one is fine. Step two is fine point where Step three should be evaluated. That's where an error is taking place. So I know that it's the input value right before Step 3 that's causing my problem which in this case is so H7 it's that messed up down payment percentage and we've isolated the problem so we can go ahead and close out of this we can remove our arrows and we can fix that percentage to 10 percent and Precentor and all is right with the world. So a lot of tools in there a lot to cover but really helpful valuable options to keep in your back pocket if you work with formulas and functions in Excel.