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 should be conducted after a piece of work is complete
- Traces are the best in-built Excel tool for conducting formula audits
- Traces only work well when connected cells are on the same worksheet
- As a result, models tend to contain a lot of calculations on the same worksheet
Alt + M, H: Show/hide formulas
Alt + M, P: Trace precedents
Alt + M, D: Trace dependents
Alt + M, A, A: Remove all traces
F2: Jump back inside a formula
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.