This article illustrates how to trace formula in Excel. Auditing formulas is necessary to fix errors or to make sure references are correct. You can trace formulas in Excel to do that.
Precedents and Dependents are the two terms directly linked to tracing formulas in Excel. Precedents are the cells used as references in the formula. On the other hand, dependents are the cells whose values depend on the output of the formula. You can see the precedents and dependents indicated by arrows to any formula or cell in Excel. Follow the methods below to learn how to do that.
Assume you have the following dataset. This is actually a reducing-balance EMI calculator. Now you need to trace the formulas in this sheet to quickly understand how it works.
You can do that in the following ways.
1. Trace Formula in Excel Using Keyboard Shortcuts
You can easily trace formulas using the formula auditing tools in Excel. Follow the steps below to be able to do that.
📌 Steps:
- First of all, you can use the ALT + M + H shortcut to show or hide the formulas in this sheet. You can also do that by selecting Show Formulas in the Formula Auditing group from the Formulas tab.
- Now select the cell containing the particular formula that you want to trace. Then press ALT + M + P to trace the precedents to the formula.
- For example, apply the shortcut to cell I4 in the example dataset. Then you will see the following result.
- After that, apply the shortcut repeatedly to trace all sub-level precedents until you hear a warning beep. The beep means there are no more traceable precedents to the formula.
- The precedent refers to a different worksheet if you see a dotted arrow. Double-click on it to find the location of the precedent.
- Now press ALT + M + D to trace the dependent to this formula cell. Then you will see the following result.
- Now apply the shortcut repeatedly to trace all the dependents to the formula cell. After that, you will see the following result.
- You can use the ALT + M + A + A shortcut to remove all arrows. Press ALT + M + A + P to remove the precedent arrows only. On the other hand, apply the ALT + M + A + D to remove the dependent arrows only.
2. Tracing Formula Using Excel Formula Auditing Tools
If you are not a fan of keyboard shortcuts, then you can access all those commands from the Formula Auditing group in the Formulas tab.
3. Applying Go To Special Command to Trace Formula
Alternatively, you can trace formulas using the Go To Special command. Follow the steps below to do that.
📌 Steps:
- First, select the cell that contains the formula. Then press ALT + H + FD + S to open the Go To Special dialog box. You can also access it from the Find & Select dropdown in the Home Then select the radio button for Precedents or Dependents as required. Click OK after that.
- Finally, you will see all the relevant cells selected as follows.
Things to Remember
- You must select the cell containing the formula that you want to trace before applying the methods.
- Dotted arrows indicate that the precedents or dependents belong to a different worksheet.
- You should stop if you hear a beep while applying the methods. Because it is a warning saying there are no traceable precedents or dependents.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know 3 ways to trace formula in Excel. Which method do you prefer? Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.
Trace Formula in Excel: Knowledge Hub
- Trace Precedents and Dependents in Excel
- How to Trace Dependents in Excel
- How to Trace Dependents Across Sheets in Excel
- How to Trace Errors in Excel
<< Go Back to Auditing Formulas |Â Excel Formulas | Learn Excel
