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.
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?
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.
#VALUE Error And How to Fix It in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.