Excel Formulas: Using Trace Precedents to Find & Resolve Errors
The power of Excel comes from the ability to do complicated calculations using formulas and functions. Sometimes, though, there are errors in formulas that give an error message or return the wrong value. In those instances, Excel also provides tools to resolve errors and check your work. One such tool is trace precedents.
The trace precedents tool finds any and all cells that provide information for the target cell. For example, if you summed up the totals in A1 and B1 into cell C1, both cells A1 and B1 would be precedents for C1.
Some errors in Excel are related to one or more precedent cells giving information that throws off the formula. Trace precedents is a great way to determine if there is a cell causing the issue, and if so, which cell needs to be corrected
For an example of using trace precedents to resolve formula errors, we will use the following spreadsheet:
In this instance, the table is using a simple formula to create revenue projections. The previous year’s quarterly total is increased by the projected growth of 5%. As you can see though, the Q3 project, cell I5, is showing an error message.
So, we will use the trace precedents tool to see if there are any cells causing the issue:
In the demo above, the error was created because one of the cells was incorrect. Instead of the cell containing the percentage amount (5% in cell F4) it was trying to pull the definition of that number one cell above (F3).
The issue could have been caused by simple human error. The person entering the formulas could have simply clicked on the wrong cell.
Once the wrong cell was identified using trace precedents, it was a simple matter of editing the formula to the correct cell and the error was resolved.
There are many instances where this tool could come in handy. Aside from resolving errors, sometimes you might just want to see which cells feed into a formula.
Stay tuned for part 2 next week on Trace Dependents!
We here at Learn Excel Now hope you now feel confident using the trace precedents tool to resolve formula issues.
Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter
Kevin – Learn Excel Now
