Quick, clean, and to the point

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

Below, 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 NA 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. 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 is includes a reference to F9, which is also showing NA.

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 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 formula 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 flow backwards to 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 the error in F9.

If I fix that problem all errors are resolved, and the red arrows are replaced by blue arrows.

The arrows now show all the direct precedents to C17. I'll cover the idea concept of precedents and dependents in more detail in another video.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.