Finding the Original Error and Fixing Its Formula

You use the Trace Error button on the Auditing Toolbar when you need to track the source of a formula error so that you can correct it. When you click this button when the cell pointer is in a cell that contains an error value, Excel attempts to track down the source by selecting the cell with the original offending formula and then drawing blue tracer arrows to its direct precedents and red tracer arrows to all its direct dependents.

After the Error Trace feature has located the problem formula, you can click the Error Checking button on the Formula Auditing toolbar to fix it. When you click this button, an Error Checking dialog box appears. This dialog box not only diagnoses the source of the error value but also offers you several choices on how to proceed:

• Help on This Error: Click this button to display a Microsoft Excel Help window with information on the error.
• Show Calculation Steps: Click this button to open an Evaluate Formula dialog box that enables you to step through the formula to pinpoint exactly where the computation goes wrong.
• Ignore Error: Click this button to have the program disregard the error value and pass on to the next error value in the worksheet.
• Edit in Formula Bar: Click this button to activate the Formula bar so that you can edit the formula and fix the problem.

If you decide to use the Edit in Formula Bar button, make your changes to the formula on the Formula bar and then click the Enter box on the Formula bar to enter your fix into the cell. You can tell if you corrected the problem because the calculated result will replace the error value in the cell and Excel will remove all the error values in the other dependent cells. Also, the program converts all the red tracer arrows (showing the proliferation trail of the original error) to regular blue tracer arrows, indicating merely that these restored cells are dependents of the formula that once contained the original error.

You can then click the Resume button (which automatically replaces the Help on this Error button) in the Error Checking dialog box. If the program then finds no other error values in the worksheet, an alert dialog box appears, indicating that the error check of the worksheet is complete. Click OK to close both this alert dialog box and the Error Checking dialog box simultaneously. Finally, you can remove all the tracer arrows from the sheet by clicking the Remove All Arrows button on the Formula Auditing toolbar and close the toolbar by clicking its Close button.

Unfortunately, the Error Trace feature is not infallible. Sometimes this feature can't find the source of a formula error the first time you use it. Trace Error will fail to locate the source of the error if the program encounters one of the following conditions in its search for the current cell's precedents and dependents:

• A branch point with more than one error
• source: In this case, Excel doesn't make a determination on its own as to which path to pursue; you have to inspect each path manually.
• Preexisting tracer arrows: Always click the Remove All Arrows button to remove all preexisting trace arrows before you click the Trace Error button.
• A formula containing a circular reference: You need to try to resolve the circular reference by recalculating the worksheet by selecting the Iteration check box on the Calculation tab of the Options dialog box and then increasing the number of iterations in the Maximum Iterations text box and perhaps decreasing the amount of change in the Maximum Change text box.