Perform calculations on data

Find and correct errors in calculations

Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your formulas. With Excel, you can find the source of errors in your formulas by identifying the cells used in a specific calculation and describing any errors that have occurred. The process of examining a worksheet for errors is referred to as auditing.

Excel identifies errors in several ways. The first way is to display an error code in the cell holding the formula generating the error.

A worksheet with an error code displayed.

When a cell with an erroneous formula is the active cell, Excel displays an Error button next to it. If you point to the Error button, Excel displays an arrow on the button’s right edge. Clicking the arrow displays a menu with options that provide information about the error and offer to help you fix it.

The following table lists the most common error codes and what they mean.

Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. You can identify the source of an error by having Excel trace a cell’s precedents, which are the cells with values used in the active cell’s formula. You can also audit your worksheet by identifying cells with formulas that use a value from a particular cell. Cells that use another cell’s value in their calculations are known as dependents, meaning that they depend on the value in the other cell to derive their own value. They are identified in Excel by tracer arrows. If the cells identified by the tracer arrows aren’t the correct cells, you can hide the arrows and correct the formula.

A worksheet showing a cell’s dependents.

If you prefer to have the elements of a formula error presented as text in a dialog box, you can use the Error Checking dialog box to locate errors one after the other, choose to ignore the selected error, or move to the next or the previous error.

Identify and manage errors using the Error Checking dialog box.

When you just want to display the results of each step of a formula and don’t need the full power of the Error Checking tool, you can use the Evaluate Formula dialog box to move through each element of the formula. The Evaluate Formula dialog box is particularly useful for examining formulas that don’t produce an error but aren’t generating the result you expect.

Step through formulas using the Evaluate Formula dialog box.

Finally, you can monitor the value in a cell regardless of where you are in your workbook by opening a Watch Window that displays the value in the cell. For example, if one of your formulas uses values from cells in other worksheets or even other workbooks, you can set a watch on the cell that contains the formula, and then change the values in the other cells. As soon as you enter the new value, the Watch Window displays the new result of the formula. When you’re done watching the formula, you can delete the watch and hide the Watch Window.

Follow cell values by using the Watch Window.

To display information about a formula error

1. Click the cell that contains the error.

2. Point to the error indicator next to the cell.

   Or

   Click the error indicator to display more information.

To display tracer arrows identifying formula precedents

1. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.

To display tracer arrows identifying cell dependents

1. In the Formula Auditing group, click the Trace Dependents button.

To remove tracer arrows

1. Do either of the following:

   • In the Formula Auditing group, click the Remove Arrows button (not its arrow).

   • Click the Remove Arrows arrow and select the arrows you want to remove.

To evaluate a formula one calculation at a time

1. Click the cell that contains the formula you want to evaluate.

2. In the Formula Auditing group, click the Evaluate Formula button.

3. In the Evaluate Formula dialog box, click Evaluate.

4. Click Step In to move forward by one calculation.

   Or

   Click Step Out to move backward by one calculation.

5. Click Close.

To change error display options

1. Display the Backstage view, and then click Options.

2. In the Excel Options dialog box, click Formulas.

3. In the Error Checking section, select or clear the Enable background error checking check box.

4. Click the Indicate errors using this color button and select a color.

5. Click Reset Ignored Errors to return Excel to its default error indicators.

6. In the Error checking rules section, select or clear the check boxes next to errors you want to indicate or ignore, respectively.

To watch the values in a cell range

1. Click the cell range you want to watch.

2. In the Formula Auditing group, click the Watch Window button.

3. In the Watch Window dialog box, click Add Watch.

4. Click Add.

To delete a watch

1. Click the Watch Window button.

2. In the Watch Window dialog box, click the watch you want to delete.

3. Click Delete Watch.