How to Trace and Fix Formula Errors in Excel

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.
0022
Let us learn how to use error tracing in excel.
Example: Find the source of the error:
0023
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?

  • 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.
0024
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.
0025
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.
0026
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.
0027
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.
0028
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

How to correct a #NUM! Error

#VALUE Error And How to Fix It in Excel

IFERROR and VLOOKUP function

How to Use IFERROR function in Excel

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Comments

  1. Manford Sihle Skhosana

    Hi
    I tried the error tracing above.the arrows come out blue but the error remains.
    Is there anything else i can do?

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.