- Excel 2013 Trace Precedents On Another Sheet Online
- Excel 2013 Trace Precedents On Another Sheet
- How To Trace Dependents On Another Sheet 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.
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?
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.
- 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.
Excel 2013 Trace Precedents On Another Sheet Online
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. Problem changing trace precedents to another sheet. Background: I have a sheet with multiple sources of data of personal information (A, B, and C). These 3 sources are used to populate cells (e.g., act as precendents) for formulas within different cells in the same sheet. Problem: I have to add more rows of information than I intended. Jan 26, 2015 When I go to Sheet2 and select Trace Dependents under Tools / Formula Auditing), Excel shows an arrow to an icon that resembles a spreadsheet. But I have not found a way to expand the icon into a cell reference or even to identify the worksheet. Moreover, if I visit each worksheet, I do not automatically see off-sheet arrows to those dependent. Excel Formula Training. Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.
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
Excel 2013 Trace Precedents On Another Sheet
How To Trace Dependents On Another Sheet In Excel
Popular Articles: