If you support Excel users, chances are you’ve received calls about error messages that mysteriously appear in cells that previously displayed values. To help those users eliminate error messages, try using Excel’s Auditing tools. They’ll save time for you and your users by providing visual cues to the possible sources of the error condition.

Select the cell; use the tool
You don’t have to wait for an error condition to use the Auditing tools. They are great tools for teaching Excel users how formulas work. In any worksheet, click on a cell and go to Tools | Auditing to display the menu shown in Figure A. From there, you can use the Trace Precedents and Trace Dependents tools to determine whether the current cell feeds information to or depends on information from any number of other cells.

Of special interest to help desk analysts and technical trainers is the Trace Error tool, which only works when the active cell contains an error value.

Figure A
The Auditing tools provide invaluable information for troubleshooting worksheets.

Tracing the error
To illustrate how the Trace Error tool works, consider the sample worksheet shown in Figure B. If you’re familiar with the VLOOKUP function, you probably know at a glance the reason for the #N/A error: The formula in cell B14 depends on the entry in cell A14 to provide a value that is greater than or equal to 1,021.

In this sample formula, the #N/A error will result any time A14 is empty, contains a string, or contains a value less than 1,021. Enter in A14 any number 1,021 or greater, and the formula will look up and return a name from the second column.

Figure B
I used the Trace Error tool to pinpoint the reason this formula is returning an error message.

To use the Auditing tools during a help desk call, have the user click on the cell that contains the error message, and then go to Tools | Auditing | Trace Error. Figure C shows what my sample worksheet looks like after Excel audited the error message. The lines can help your end user see exactly where information needs to be added or changed.

Let’s start with the blue line that starts in cell A14 and points to cell B14. Blue lines point from cells that provide information to cells that depend on that information. So that line visually tells us that the formula in B14 depends in some part on the entry in A14.

Figure C
The Trace Error tool highlights the path to the cells that might be causing the error.

Now let’s talk about the blue line that starts in A2 and points to cell B14. Notice what’s special about cell A2: It’s the top-left cell in a range that’s been set off by a blue border, which is the lookup range referenced by the formula in B14. In this context, the blue border and the line from A2 pointing to B14 indicate that B14 depends in some part on the entries in all of the entries in A2:B11.

The red arrows
From time to time, Excel will use a red arrow instead of a blue one to point the way from a precedent cell to a dependent cell. When that happens, you know you have at least one more error condition to deal with. The red arrow means that the cell feeding the current cell contains an error.

Adventures in auditing
The Trace Error tool provides useful information for troubleshooting error messages. You’ll also want to experiment with the Trace Precedents, Trace Dependents, and Circle Invalid Data options.

When you finish troubleshooting, you erase the lines by going to Tools | Auditing | Remove All Arrows. If you have a lot of error messages to work on, you may want to go ahead and display the Auditing toolbar for easy access to the auditing tools, especially the eraser.

Don’t be surprised if you’re editing the worksheet and the arrows suddenly disappear. Excel will erase the tracer arrows when you change the formula to which the arrows point, insert or delete columns or rows, or delete or move cells. Just go to Tools | Auditing again to display the arrows based on the updated information.

How do you like this one?

To comment on this tip or to share your favorite Excel technique, post a comment or write to Jeff.