Sign in or start a free trial to avail of this feature.
11. Formula Auditing
Mistakes in formulas happen all the time, so you'll be glad to hear Excel provides a few helpful auditing tools to help you check your calculations.
Formula Auditing (00:04)
Errors in Excel formulas can create misleading results and be very embarrassing. Formula Auditing helps you find and remove these errors to avoid these problems. Auditing tools can be found in the formula tab by pressing Alt, M.
Show Formulas (00:43)
The Show Formulas command replaces the values in a data set with the formulas used to create them. The keyboard shortcut for Show Formulas is Alt, M, H. When you select a cell, the dependents, or the cells used in the formula, are also highlighted. This lets you see if an error has occurred in the formula. Show Formulas is less useful in larger models, as you often need to see the values to spot an error.
Trace Precedents and Trace Dependents (01:19)
Trace Precedents uses arrows to show us which cells affect the selected cell. For example, revenue in euro is affected by revenue in US dollars and the euro to dollar exchange rate. To see these traces you use the shortcut Alt, M, P.
Trace Dependents uses arrows to show us which cells are affected by the selected cell. For example, the euro to dollar exchange rate affects revenue in euro. To see these traces you use the shortcut Alt, M, D. To remove traces you press Alt, M, A, A.
Using these arrows, you can make a quick visual check of your spreadsheet to see if your formulas are affecting the correct cells. You can easily identify and fix any mistakes.
Trace and Multiple Sheets (04:05)
Traces are less effective when a dependent or precedent is on another sheet. In this situation you need to use the Go To dialog box to see the original cell. When you’re building complex models, you should concentrate your work on a single sheet where possible. This will allow you to use tracing effectively.
Formula auditing, much like real auditing, can be quite tedious, but is also very important. Formula errors can give very misleading results and be very embarrassing for the individuals concerned. To minimize the chance of Excel errors, it's best practice to always conduct a quick formula audit after completing a small piece of work. I've added a few errors to the foreign exchange calculations from an earlier lesson and I'm going to perform formula auditing to find and fix these errors. To help us in this task, Excel provides a few helpful auditing tools in the formula bar, which can be accessed with Alt M. One command you may have already seen is show formulas, which can be accessed with the shortcut Alt MH. This replaces all the values in the data set with their formulas. When a cell is selected, it also highlights the cell dependents. By moving around the array, we can see if we're referencing the correct cells and if an error has occurred. Unfortunately, for more complex models, this tool isn't very useful, because we often need to see the values to intuitively spot an error. A better option for finding errors is trace precedence. Let's press Alt MH to return to values and then press Alt MP to trace precedents.
Traces use arrows to show us what cells are affecting our selected cell. As you can see, they're much more readable than the show formulas command. In this case, instead of testing the precedents for each blue cell, we might be quicker tracing the dependents of the dollar revenue and the exchange rate. Let's delete all the traces with Alt MAA and go to the top exchange rate. I'll now press Alt MD to trace dependents, and I can see that the UR revenue is referring to the correct exchange rate. I'll now trace dependents for the British pound with Alt MD and again, I can see that the British pound revenue is referencing the correct exchange rate. I'll repeat for the Canadian dollar, again with Alt MD, but this time, I can spot an error, because the March revenue for the Canadian dollar is not referencing the Canadian dollar exchange rate. If I navigate to this cell and press F2, it's actually referencing the Swiss frank exchange rate, so I'll need to change this.
I'll now remove all the traces with Alt MAA, return to the Canadian dollar, and trace dependents with Alt MD.
And as I can see, the Canadian dollar revenue is now referencing the correct exchange rate for all three months. Lastly, I'll check the Swiss frank exchange rate, again with Alt MD, and this shows me that the Swiss frank revenue is referencing the correct exchange rate.
Now, let's check the dollar revenue amounts. For each dollar revenue amount, I'll use the command Alt MD, and I'll do this for January, February, and March. As you can see, it's pretty easy to spot the error in this scenario where the March revenue in Swiss franks is referencing the incorrect amount. When I press F2, I can see that it's referencing the January dollar revenue, and I'll simply switch this.
If I now trace dependents on March revenue again, Alt MD, I can now see that the March Swiss frank revenue is referencing the correct month. Even in this simplified example, you can see how good tracing can be at helping you identify errors in your formulas. Unfortunately, traces don't work quite as well when you need to move to a dependent or precedent on another sheet. Let's take a look at our exchange rate, which I have linked to a cell A1 on sheet two. If I want to trace precedents with Alt MP, I'll first need to click on this arrow that links a spreadsheet icon to our cell.
I then need to select the go to option and press okay. And this eventually brings me to my original cell. This functionality isn't very user friendly and you'll notice, when we build complex models that will include a lot of information on a single sheet so that we can use formula tracing effectively.