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.

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 to use 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

Read More: Excel VBA Watch Window

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

  • To do 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

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: How to Monitor Cells Using Excel Watch Window

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

Download Practice Workbook

You can download our practice workbook from here for free!


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. Thank you!

<< Go Back to Trace Formula | Auditing FormulasExcel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF