Troubleshooting formulas

Using the formula error checker

If you use Microsoft Word, you’re probably familiar with the double blue line that appears under words and phrases that the grammar checker has flagged as being incorrect. The grammar checker operates by using a set of rules that determines correct grammar and syntax. As you type, the grammar checker operates in the background, constantly monitoring your writing. If something you write goes against one of the grammar checker’s rules, the wavy line appears to let you know there’s a problem.

Excel has a similar feature: the formula error checker. Like the grammar checker, the formula error checker uses a set of rules to determine correctness, and it operates in the background to monitor your formulas. If it detects something amiss, it displays an error indicator—a green triangle—in the upper-left corner of the cell containing the formula, as shown in Figure 3-3.

FIGURE 3-3 If Excel’s formula error checker detects a problem, it displays a green triangle in the upper-left corner of the formula’s cell.

Choosing an error action

When you select the cell with the formula error, Excel displays a formula error icon beside the cell. If you hover your mouse pointer over the icon, a pop-up message describes the error, as shown in Figure 3-4. The formula error icon drop-down menu contains the following actions:

FIGURE 3-4 Select the cell containing the error and then move the mouse pointer over the formula error icon to see a description of the error.

• Corrective Action: This is a command (the name of which depends on the type of error) that Excel believes either will fix the problem or help you troubleshoot the error. For example, in Figure 3-4, Excel is reporting that the formula in cell C3 differs from its neighboring formulas. (In the formula bar, the expression in the parentheses should be 1+C2 instead of 1-C2.) In this case, the corrective action command in the formula error icon is Copy Formula From Left. Or, if Excel can’t suggest a solution, it might show the command Show Calculation Steps, which runs the Evaluate Formula feature. See “Evaluating formulas,” later in this chapter.

• Help On This Error: Select this option to get information on the error via the Excel Help system.

• Ignore Error: Select this option to leave the formula as is.

• Edit In Formula Bar: Select this option to display the formula in Edit mode in the formula bar. You can then fix the problem by editing the formula.

• Error-Checking Options: Select this option to display the Formulas tab of the Excel Options dialog box (discussed next).

Setting error checker options

Like Word’s grammar checker, Excel’s formula error checker has a number of options that control how it works and which errors it flags. To see these options, you have two choices:

• Select File > Options > Formulas to open the Excel Options dialog box and display the Formulas tab.

• Select Error-Checking Options in the formula error icon’s drop-down menu (as described in the previous section).

Either way, the options appear in the Error Checking and Error Checking Rules sections in the Formulas tab, as shown in Figure 3-5.

FIGURE 3-5 In the Formulas tab, the Error Checking and Error Checking Rules sections contain the options that govern the workings of the formula error checker.

Here’s a rundown of the available options:

• Enable Background Error Checking: This check box toggles the formula error checker’s background operation on and off. If you turn off the background checking, you can run a check at any time by choosing Formulas > Error Checking.

• Indicate Errors Using This Color: Use this color palette to select the color of the error indicator.

• Reset Ignored Errors: If you’ve ignored one or more errors, you can redisplay the error indicators by selecting this button.

• Cells Containing Formulas That Result In An Error: When this check box is selected, the formula error checker flags formulas that evaluate to #DIV/0!, #NAME?, or any of the other error values discussed earlier.

• Inconsistent Calculated Column Formula In Tables: When this check box is selected, Excel examines the formulas in a table’s calculated column and flags any cell that contains a formula with a different structure than the other cells in the column. The formula error icon for this error includes the command Restore To Calculated Column Formula, which enables you to update the formula so that it’s consistent with the rest of the column.

• Cells Containing Years Represented As 2 Digits: When this check box is selected, the formula error checker flags formulas that contain date text strings in which the year contains only two digits (a possibly ambiguous situation because the string could refer to a date in either the 1900s or the 2000s). In such a case, the list of options supplied in the formula error icon contains two commands—Convert XX To 19XX and Convert XX To 20XX—that enable you to convert the two-digit year to a four-digit year.

• Numbers Formatted As Text Or Preceded By An Apostrophe: When this check box is selected, the formula error checker flags cells that contain a number that is either formatted as text or preceded by an apostrophe. In such a case, the list of options supplied in the formula error icon contains the Convert To Number command to convert the text to its numeric equivalent.

• Formulas Inconsistent With Other Formulas In The Region: When this check box is selected, the formula error checker flags formulas that are structured differently from similar formulas in the surrounding area. In such a case, the list of options supplied in the formula error icon contains a command such as Copy Formula From Left to make the formula consistent with the surrounding cells.

• Formulas Which Omit Cells In A Region: When this check box is selected, the formula error checker flags formulas that omit cells that are adjacent to a range referenced in the formula. For example, suppose that the formula is =AVERAGE(C4:C21), where C4:C21 is a range of numeric values. If cell C3 also contains a numeric value, the formula error checker flags the formula to alert you to the possibility that you missed including cell C3 in the formula. Figure 3-6 shows this example. In such a case, the list of options supplied in the formula error icon will contain the command Update Formula To Include Cells to adjust the formula automatically.

  

  FIGURE 3-6 The formula error checker can flag formulas that omit cells that are adjacent to a range referenced by the formula. In this case, the formula in C23 should include cell C3.

• Unlocked Cells Containing Formulas: When this check box is selected, the formula error checker flags formulas that reside in unlocked cells. This isn’t an error so much as a warning that other people could tamper with the formula even after you have protected the sheet. In such a case, the list of options supplied in the formula error icon will contain the command Lock Cell to lock the cell and prevent users from changing the formula after you protect the sheet.

• Formulas Referring To Empty Cells: When this check box is selected, the formula error checker flags formulas that reference empty cells. In such a case, the list of options supplied in the formula error icon will contain the command Trace Empty Cell to enable you to find the empty cell. (At this point, you can either enter data into the cell or adjust the formula so that it doesn’t reference the cell.)

• Data Entered In A Table Is Invalid: When this check box is selected, the formula error checker flags cells that violate a table’s data-validation rules. This can happen if you set up a data-validation rule with only a Warning or Information style, in which case the user can still opt to enter the invalid data. In such cases, the formula error checker will flag the cells that contain invalid data. The formula error icon list includes the Display Type Information command, which shows the data-validation rule that the cell data violates.

• Misleading Number Formats: When this check box is selected, the formula error checker flags cells that have a numeric, date, or time format that Excel deems incompatible with the formula result. For example, Excel displays this error if a formula applies a mathematical operation to a currency value, but the formula’s cell is formatted as a date or time. The formula error icon list includes the Update Format command, which changes the cell data format to fit the formula result.