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.
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.
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.
CLOSE the ConveyerBid workbook.