It’s common to get errors in excel while doing the calculation using excel formulas. You can quickly fix an error if it depends on hardcoded values or one or two references, but it gets tough when the formula relies on a serial of references. It can be that the formula showing error does not contain error itself, but the references it depends on does. It means if you can solve that error, all of the errors will be solved.
To trace the errors, excel provides an error tracing tool. It is in the formula tab of the ribbon, in formula auditing group, under Error Checking.
Let us learn how to use error tracing in excel.
Example: Find the source of the error:
To showcase you, how to use excel error tracing, I have prepared a simple example in excel. Here I have a series. In cell C2, we have a total of series.

Details: To execute Trace Dependents selection must point to a cell or range having formula. Select the cell for which you want to identify the dependent cells. To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Dependents. Excel 2010: In certain versions of Excel, the Navigatearrow method will have trouble navigating through all dependent cells if there are cells outside of the current workbook or worksheet. The only solution is to make VBA code turn on another worksheet before proceeding to cycle through the dependent cells.

In cell C8, I have an average series. As we can see the average cell is showing an #N/A error. You can do this manually here, but if this sheet was large enough that you can’t see all precedent cells at once, it could be a rigorous task. With error tracing, it is simple. Let’s see how?

• Select the error containing the cell that you want to trace. Here I select C8 total of series.
• Now go to --> formula tab --> Formula Auditing --> Error checking drop down --> Trace Errors.

And it is done.
These red line shows the where the error is coming from. Here C8 is getting it’s error from C2 and C2 from B5. Hence if we solve the B5 error, we may get all other errors solved.
If I write 0 in B5, then all errors will be gone. The red arrows will turn blue.
So this how you use the error tracing. Now you might be wondering what will happen if the error is being caused by some cell in a different sheet. Let’s try it.
Here in cell B5, I have given the reference of cell A2 from sheet2, and it contains a #DIV/0 error.
Now select average cell A2 and do the same error tracing.
In the above image, you can see division error coming from a sheet icon. If you double click on the end of this arrow, a go-to dialogue box will open, with reference of error causing cell. Click on that reference and hit ok. You’ll be directed to that error creating cell. Solve it and move to another.
Important: If some other workbook’s reference is causing error, then error tracing of excel will not work. It works only for one workbook.

Remove Error Tracing Arrows

As you trace the error, error arrows will pop up. Now CTRL+Z will not make these arrows go, even if you have solved the error. To get rid of these arrows, click on the remove arrow in the formula auditing group. All errors will disappear immediately.
So yeah guys, this how you can use error tracing in excel to solve formula errors. I find it useful whenever I work on someone else’s file. This helps me locate the source of the error. Let me know if you have any doubts regarding this article or any other excel topic, in the comments section below.

Related Articles

#VALUE Error And How to Fix It in Excel

Popular Articles:

Excel 2013 Trace Precedents On Another Sheet