In this video, we’ll look at how to trace a formula error.
Here we have a simple sales summary for a team of salespeople over a period of 4 months.
You can see that we have monthly totals in the bottom row, and totals for each sales person in the last column.
Below the table, we have a sales target, and calculations that are mean to calculate a bonus when the sales target is exceeded.
As you can see, these calculations aren’t working at the moment and there are a number of errors in the worksheet.
One way to trace this error in manually in Excel, is just to start clicking into cells with errors to see what the formulas are doing and which other cells they reference . For example, in G11, we can see a SUM formula, and we can guess that this formula is throwing an error because it refers to another cell that displays an error, F11.
F11, in turn, includes a reference to F9, which also displays the #N/A error.
So, the first thing to notice,is that one error often leads to another. In many cases, this means that if you can fix the source error, you can fix other errors at the same time.
If I type a zero into F9, all the other errors are resolved at once.
Let me undo that and let’s look at a tool that can make this visual tracing easier.
On the formulas tab of the ribbon, under error checking, you’ll find an option called Trace error. To use this feature, first select a cell that contains an error, then choose Trace error.
Excel will draw arrows in red to indicate the source of the problem. In this case, we can see that the error in G11 is caused by an error in F11, which in turn is caused by the error in F9.
Click remove errors button to clear the arrows.
You can also access trace errors directly from the smart tag menu that appears when an error is flagged by Excel.
If I select C17, then click the smart tag, I can select Trace Error in the menu. Now we can see that the error in C17 is part of an even larger chain of errors that leads back to cell F9.
Once I fix that problem, all errors are resolved, and the red arrows are replaced by blue arrows.