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 + M H.
This replaces all the values in the data set with their formulas.
When a cell is selected, it also highlights the cell dependents, which in this case are D38 and C32.
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 precedents.
Let's press Alt + M H to return to values, and then press Alt + M P 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 + M A A, and go to the top exchange rate.
I'll now press Alt + M D to trace dependents, and I can see that the EUR revenue is referring to the correct exchange rate.
I'll now trace dependents for the British pound, Alt + M D, 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 + M D, 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 Franc exchange rate.
So I'll need to change this, from C34 to C33.
I'll now remove all the traces with Alt + M A A, return to the Canadian dollar, and trace dependents with Alt + M D.
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 Franc exchange rate, again with Alt + M D.
And this shows me that the Swiss Franc 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 + M D.
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 Francs 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 from C38 to E38.
If I now trace dependents on March revenue again, with Alt + M D, I can now see that the March Swiss Franc 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 a precedent on another sheet.
Let's take a look at our exchange rate, which I've linked to a cell, A1, on sheet 2.
If I want to trace precedents with Alt + M P, I'll first need to click on this arrow that links a spreadsheet icon to our cell.
And then you just select the go to option and press OK.
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 we'll include a lot of information on a single sheet so that we can use formula tracing effectively.