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.
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.
Read More:Â Excel VBA Watch Window
📌 Steps:
- First and foremost, click on the error cell (E11 here).
- 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.
- 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.
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.
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.
Thus, you will see that all the error tracing arrows are gone. And, the output should look like this.
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!
Conclusion
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 Formulas |Â Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
