Finding and Correcting Errors in Calculations | MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))

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. Excel 2007 makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing.

Excel 2007 identifies errors in several ways. The first way is to fill the cell holding the formula generating the error with an error code. In the following graphic, cell F13 has the error code #NAME?.

When a cell with an erroneous formula is the active cell, an Error button appears next to it. You can click the button down arrow to display 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.

Error Code	Description
#####	The column isn't wide enough to display the value.
#VALUE!	The formula has the wrong type of argument (such as text in which a TRUE or FALSE value is required).
#NAME?	The formula contains text that Excel 2007 doesn't recognize (such as an unknown named range).
#REF!	The formula refers to a cell that doesn't exist (which can happen whenever cells are deleted).
#DIV/0!	The formula attempts to divide by zero.

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. For example, you might want to calculate the total number of deliveries for a service level, but you could accidentally create a formula referring to the service levels' names instead of their quantities. You can identify what kind of error has appeared by having Excel 2007 trace a cell's precedents, which are the cells with values used in the active cell's formula. Excel 2007 identifies a cell's precedents by drawing a blue tracer arrow from the precedent to the active cell.

You can also audit your worksheet by identifying cells with formulas that use a value from a given cell. For example, you might use one region's daily package total in a formula that calculates the average number of packages delivered per region on a given day. 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. As with tracing precedents, you can click the Formulas tab on the user interface and then, in the Formula Auditing group, click Trace Dependents to have Excel 2007 draw blue arrows from the active cell to those cells that have calculations based on that value.

If the cells identified by the tracer arrows aren't the correct cells, you can hide the arrows and correct the formula. To hide the tracer arrows on a worksheet, display the Formulas tab and then, in the Formula Auditing group, click Remove Arrows.

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 (which you can display by displaying the Formulas tab and then, in the Formula Auditing group, clicking the Error Checking button) to view the error and the formula in the cell in which the error occurs. You can also use the controls in the Error Checking dialog box to move through the formula one step at a time, to choose to ignore the error, or to move to the next or the previous error. If you click the Options button in the dialog box, you can also use the controls in the Excel Options dialog box to change how Excel 2007 determines what is an error and what isn't.

Tip

You can have the Error Checking tool ignore formulas that don't use every cell in a region (such as a row or column). If you clear the Formulas that omit cells in a region check box, you can create formulas that don't add up every value in a row or column (or rectangle) without Excel 2007 marking them as an error.

For times 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. To display the Evaluate Formula dialog box, you display the Formulas tab and then, in the Formula Auditing group, click the Evaluate Formula button. The Evaluate Formula dialog box is much more useful for examining formulas that don't produce an error but aren't generating the result you expect.

Finally, you can monitor the value in a cell regardless of where in your workbook you are 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. To set a watch, click the cell you want to monitor and then, on the Formulas tab, in the Formula Auditing group, click Watch Window. Click Add Watch to have Excel 2007 monitor the selected cell.

As soon as you type in the new value, the Watch Window displays the new result of the formula. When you're done watching the formula, select the watch, click Delete Watch, and close the Watch Window.

In this exercise, you use the formula-auditing capabilities in Excel 2007 to identify and correct errors in a formula.

USE the ConveyerBid workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Formulas folder.

OPEN the ConveyerBid workbook.

1.	Click cell D20.
2.	On the Formulas tab, in the Formula Auditing group, click Watch Window. The Watch Window appears.
3.	Click Add Watch and then, in the Add Watch dialog box, click Add. Cell D20 appears in the Watch Window.
4.	Click cell D8. =SUM(C3:C7) appears in the formula bar.
5.	On the Formulas tab, in the Formula Auditing group, click Trace Precedents. A blue arrow appears between cell D8 and the cell range C3:C7, indicating that the cells in the range C3:C7 are precedents of the value in cell D8.
6.	On the Formulas tab, in the Formula Auditing group, click Remove Arrows. The arrow disappears.
7.	Click cell A1.
8.	On the Formulas tab, in the Formula Auditing group, click the Error Checking button. The Error Checking dialog box appears.
9.	Click Next. Excel 2007 displays a message box indicating that there are no more errors in the worksheet.
10.	Click OK. The message box and the Error Checking dialog box disappear.
11.	On the Formulas tab, in the Formula Auditing group, click the Error Checking button down arrow, and then click Trace Error. Blue arrows appear, pointing to cell D21 from cells C12 and D19. These arrows indicate that using the values (or lack of values, in this case) in the indicated cells generates the error in cell D21.
12.	On the Formulas tab, in the Formula Auditing group, click Remove Arrows. The arrows disappear.
13.	In the formula bar, delete the existing formula, type =C12/D20, and press . The value 14% appears in cell D21.
14.	Click cell D21.
15.	On the Formulas tab, in the Formula Auditing group, click the Evaluate Formula button. The Evaluate Formula dialog box appears, with the formula from cell D21 displayed.
16.	Click Evaluate three times to step through the formula's elements and then click Close. The Evaluate Formula dialog box disappears.
17.	In the Watch Window, click the watch in the list.
18.	Click Delete Watch. The watch disappears.
19.	On the Formulas tab, in the Formula Auditing group, click Watch Window. The Watch Window disappears.

CLOSE the ConveyerBid workbook.

CLOSE Excel.