![best trace precedents excel tool best trace precedents excel tool](https://cdn.wallstreetmojo.com/wp-content/uploads/2019/11/Auditing-Tools-in-Excel-300x200.jpg)
If I want to trace precedence with Alt + M + P, I'll first need to click on this arrow that links a spreadsheet icon to our cell. Let's take a look at our exchange rate, which I've linked to cell A1 on sheet two. Unfortunately, traces don't work quite as well when you need to move to a dependent or precedent on another sheet. If I now trace dependence on March revenue again, Alt + M + D, I can now see that the March Swiss Franc revenue is referencing the correct month.Įven in this simplified example, you can see how good tracing can be at helping you identify errors in your formulas. When I press F 2, I can see that it's referencing the January dollar revenue and I'll simply switch this. Now let's check the dollar revenue amounts.įor each dollar revenue amount, I'll use the command Alt + M + D, and I'll do this for January, February, and March.Īs 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. 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. And as I can see, the Canadian dollar revenue is now referencing the correct exchange rate for all three months. I'll now remove all the traces with Alt + M + A + A, return to the Canadian dollar, and trace dependence with Alt + M + D. 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. 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. I'll now trace dependence for the British pound with Alt + M + D, and again, I can see that the British pound revenue is referencing the correct exchange rate. I'll now press Alt + M + D to trace dependence and I can see that the Euro revenue is referring to the correct exchange rate. Let's delete all the traces with Alt + M + A + A and go to the top exchange rate. In this case, instead of testing the precedents for each blue cell, we might be quicker tracing the dependence of the dollar revenue and the exchange rate.
![best trace precedents excel tool best trace precedents excel tool](https://cdn.extendoffice.com/images/stories/doc-excel/trace-precedents/doc-trace-dependent-8.png)
As you can see, they're much more readable than the show formulas command. Trace uses arrows to show us what cells are affecting our selected cell. Let's press Alt + M + H to return to values, and then press Alt + M + P to trace precedence. A better option for finding errors is trace precedence. Unfortunately, for more complex models, this tool isn't very useful, because we often need to see the values to intuitively spot an error. When a cell is selected, it also highlights the cell dependence.īy moving around the array, we can see if we're referencing the correct cells and if an error has occurred. This replaces all the values in the data set with their formulas. One command you may have already seen is show formulas, which can be accessed with the shortcut Alt + M + H. To help us in this task, Excel provides a few helpful auditing tools in the Formula bar, which can be accessed with Alt + M. I've added a few errors to the foreign exchange rate calculations from an earlier lesson and I'm going to use formula auditing to find and fix these errors.
![best trace precedents excel tool best trace precedents excel tool](https://image1.slideserve.com/2724567/menu-bar-tools-formula-auditing-trace-precedents-l.jpg)
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. Formula errors can give very misleading results and can be very embarrassing for the individuals concerned. Formula auditing, much like real auditing, can be quite tedious, but is also very important.
#BEST TRACE PRECEDENTS EXCEL TOOL HOW TO#
In this lesson, we'll learn how to identify errors in formulas using formula auditing tools. In the previous lesson, we learned how to generate random numbers in Excel.