Microsoft Office Tutorials and References

In Depth Information

**Formula Checking**

Table 4–4 Error Checking Rules

Rule

Name of Rule

Description

1

Cells containing formulas that result in

an error

The cell contains a formula that does not use the

expected syntax, arguments, or data types.

2

Inconsistent calculated column formula

in tables

The cell contains formulas or values that are inconsistent

with the column formula or tables.

3

Cells containing years represented as

2 digits

The cell contains a text date with a two–digit year that

can be misinterpreted as the wrong century.

4

Numbers formatted as text or

preceded by an apostrophe

The cell contains numbers stored as text.

5

Formulas inconsistent with other formulas

in the region

The cell contains a formula that does not match the

pattern of the formulas around it.

6

Formula that omits cells in a region

The cell contains a formula that does not include a

correct cell or range reference.

7

Unlocked cells containing formulas

The cell with a formula is unlocked in a protected

worksheet.

8

Formulas referring to empty cell

The cells referred to in a formula are empty.

9

Data entered in a table is invalid

The cell has a data validation error.

To Enable Background Formula Checking

Through the Excel Options dialog box, you can enable background formula checking.

Background formula checking
means that Excel continually will review the workbook

for errors in formulas as you create or manipulate it. The following steps enable background

formula checking.

1
Click the Ofﬁ ce Button on the Ribbon, click the Excel Options button, and then click the

Formulas button.

2
If necessary, click ‘Enable background error checking’ in the Error Checking area to select it.

3
Click any check box in the ‘Error checking rules’ area that does not contain a check mark

(Figure 4–64 on the next page).

Excel Help

The best way to become

familiar with Excel Help

is to use it. Appendix C

includes detailed

information about

Excel Help and exercises

that will help you gain

conﬁ dence in using it.

4
Click the OK button.

How can I decide which rules to have the background formula checker check?

You can decide which rules you want the background formula checker to highlight by

adding and removing check marks from the check boxes in the ‘Error checking rules’ area

(Figure 4–64). If you add or remove check marks, then you should click the Reset Ignored

Errors button to reset error checking.