How to Trace Errors in Excel (with Quick Steps)

Excel is mighty for large data handling and calculations. Now, when working with extensive calculations, anyone might encounter some errors. It might get too difficult and time-consuming sometimes to understand the reasons behind the error. In this regard, Excel has a fantastic feature that you can use to trace errors. In this article, I will show you all the quick steps to trace errors in Excel.

Download Practice Workbook

You can download our practice workbook from here for free!

Trace Errors in Excel: A Quick Overview

To trace errors in Excel follow the workflow below:

Click on your error cell >> go to the Formulas tab >> Formula Auditing group >>  Error Checking tool >> Trace Error option.

Use Formulas Tab to Trace Errors in Excel

Types of Errors in Excel

Excel generally shows the errors described below.

#VALUE! : This normally occurs when you pass an incorrect argument or reference inside a formula. It also occurs when you use the wrong operator inside a formula. It happens most of the time for using text instead of numbers inside a formula.

#DIV/0 : This happens if you divide any cell or number by 0 or a blank cell.

#NUM! : This mainly occurs if the number inside a cell is too large or too narrow to show inside the cell.

#REF! : This error occurs mainly due to incorrect or missing references. If you use some reference inside a formula, but there is no value or incorrectly formatted value in the referenced cell, then the formula will show you this type of error.

#N/A : This type of error mainly conveys the message Not Available. This is not an error really. This mainly shows if some necessary values are unavailable.

 #NAME? : This type of error mainly occurs due to wrong spelling or using unavailable table or range names.

#NULL! : This does not happen much normally. It occurs mainly if you use an intersection operator between ranges that do not intersect.

How to Trace Errors in Excel

Suppose, you have a dataset containing the units sold and the unit price of a particular product for 6 months. Following, you have calculated the total sales money in individual months and finally in total 6 months. But, there occurred some #VALUE! Errors. It happened because, in the formula of the E11 cell, it expected numbers as arguments. But, E7 and E9 cells have errors. And, the E7 and E9 cells have #VALUE! errors because D7 and C9 cells need numbers inside the cell whereas they do not possess any number here. Consequently, you have not gotten your desired result. Now, you can trace errors in Excel to solve your calculation. Follow the steps below to do so.

Sample Data to Trace Errors in Excel

📌 Steps:

  • First and foremost, click on the error cell (E11 here).

Click on the Error Cell

  • Consequently, a yellow marked triangle symbol will appear on the left side of the cell.
  • Afterward, hover over the symbol.
  • Following, click on the symbol’s down arrow.
  • Subsequently, choose the option Trace Error from the dropdown menu.

Choose the Trace Error Option to Trace Errors in Excel

  • As a result, you will get the error tracing arrows.

Besides, you could also use the Formulas tab to trace errors.

  • For doing this, click on the E11 cell >> Go to the Formulas tab >> Formula Auditing group >> Error Checking tool >> Trace Error option.

Use Formulas Tab to Trace Errors in Excel

Thus, you can trace the errors for the E11 cell. You will get all the responsible cells for errors connected by arrows. For instance, the outcome will look like this.

Traced Errors

Read More: How to Trace Dependents in Excel (2 Easy Methods)

How to Remove Error Tracing Arrows in Excel

Now, after tracing errors, you can easily fix your errors. But, even after fixing the errors, it might happen that the error tracing arrows are not removed. Follow the steps below to remove the error tracing arrows in Excel.

📌 Steps:

  • Go to the Formulas tab.
  • Afterward, go to the Formula Auditing group.
  • Subsequently, click on the Remove Arrows tool.

Removing Error Tracing Arrows

Thus, you will see that all the error tracing arrows are gone. And, the output should look like this.

Removed Error Tracing Arrows

Read More: Trace Precedents and Dependents in Excel (with Quick Steps)

💬 Things to Remember

  • The error cells have green triangles at the top left corner of the cells. You can easily find an error cell through this.


So, in this article, I have shown you all the quick steps to trace errors in Excel. Go through the full article carefully to understand it better and accomplish your desired result. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy for many more articles like this. Thank you!

Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features