How to Trace Formula in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to show how to trace formula in excel

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.

select the cell & then press ALT + M + P

  • 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.

press ALT+M+P repeatedly to trace all precedents to the formula

  • Now press ALT + M + D to trace the dependent to this formula cell. Then you will see the following result.

press ALT + M + D for dependents

  • Now apply the shortcut repeatedly to trace all the dependents to the formula cell. After that, you will see the following result.

press ALT+M+D repeatedly for all-level dependents to trace formula in excel

  • 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.

select Formula >> Formula Auditing to trace formula in excel


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.

press ALT + H + FD + S for Go To Special to trace formula in excel

  • Finally, you will see all the relevant cells selected as follows.

traced dependents to the formula in cell I4 to trace formula in excel


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


<< Go Back to Auditing FormulasExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo