How to trace a formula error

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 salesperson in the last column.

Below the table, we have a sales target and calculations that are meant 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 NA errors in the worksheet.

One way to trace this error manually in Excel is to start clicking into formulas 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 NA 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 cell F9, all the 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 errors in red that 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 the Remove Arrows button to remove the arrows.

You can also 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.