Choosing Preferences for Error Checking

Excel can automatically identify various errors in your formulas. To choose which errors Excel marks, you set the error-checking preferences. Choose Excel Preferences or press Cmd+, (Cmd and the comma key) to display the Excel Preferences dialog box, and then click the Error Checking icon in the Formulas and Lists area to display the Error Checking pane (see Figure 5–5).

Figure 5–5. In the Error Checking pane of the Excel Preferences dialog box, choose whether to use background error checking and decide which rules to use.

You can then choose the following settings in the upper box:

• Enable background error checking. Select this check box to turn on error checking in the background. Background error checking is a good idea for small and medium-size workbooks, but you may want to turn it off for larger workbooks because it can slow Excel down. If you do turn off background error checking, be sure to check for errors manually (choose Formulas Audit Formulas Check for Errors, clicking the main part of the Check for Errors button).
• Reset Ignored Errors. Click this button to reset all the errors in the active workbook that you've told Excel to ignore. You may find it helpful to ignore errors when you're building a worksheet and the errors occur because the data isn't in place. When the worksheet is finished, you can click the Reset Ignored Errors button to see any remaining errors.
• Flag errors by using this color. In this pop-up menu, choose the color to use for flagging errors. The default setting is Automatic, which lets Excel choose the color.

In the Rules box, you can select or clear the following check boxes to control which errors (or apparent errors) Excel flags by putting a green triangle at the upper-left corner of the cell:

• Flag cells containing formulas that result in an error. Select this check box to make Excel flag cells whose formulas produce errors. This is normally helpful.
• Flag cells containing years represented as 2 digits. Select this check box to make Excel flag cells that contain years represented by two digits rather than four digits (for example, 11 instead of 2011). Using four-digit years helps avoid confusion in your spreadsheets.
• Flag numbers formatted as text. Select this check box to make Excel flag numbers that have text formatting rather than number formatting or that you've preceded with an apostrophe to force Excel to treat them as text. This option can be helpful, but it's apt to flag cells you've deliberately formatted as text.
• Flag formulas that are inconsistent with the formulas in adjoining cells. Select this check box to make Excel flag formulas that appear wrong because they're different from formulas in adjoining cells. For example, if you have a row of eight cells, and six contain SUM() formulas adding the cells above them but two contain SUM() formulas adding the three cells to their left, this setting makes Excel flag those two cells because they contain formulas different from their neighbors. This can be helpful, but if the two oddball cells are summarizing the fiscal year quarters, you may want the formulas to be different.
• Flag formulas which omit other cells in the region. Select this check box to make Excel flag formulas that are apparently intended to cover a whole region of a worksheet but omit particular cells in it. If these omissions are intentional, you can suppress the flags.
• Flag unlocked cells containing formulas. Select this check box to make Excel flag unlocked cells that contain formulas. This setting can help you track down cells you need to lock to prevent your colleagues taking liberties with your calculations.
• Flag formulas that refer to empty cells. Select this check box to make Excel flag formulas that try to use empty cells. Empty cells can cause plenty of problems in your formulas, including division-by-zero errors, so having Excel identify formulas that use empty cells is usually a good idea.
• Flag inconsistent calculated column formula in tables. Select this check box to make Excel flag formulas in tables that differ from the other formulas in their columns. (Chapter 10 shows you how to create tables.)

When you've finished choosing error-checking preferences, click the OK button to close the Preferences dialog box.