Microsoft Office Tutorials and References

In Depth Information

**Telling Excel Which Errors to Flag**

Just What Is a Calculated Column?

To understand what a calculated column is, you have to ﬁrst understand tables. Tables are created from

organized rows and columns when you format them as a table (using the Format as Tables button on

the Home tab or the Table button on the Insert tab). Once formatted as a table, you can quickly ﬁlter

your data (limit its display to selected types, such as all rows with a Sales value over $1,000) or sort it.

You can also create a calculated column by typing a formula in one cell of that column, and letting

Excel copy it down the rest of the column for you automatically. After that, if you change the copied

formula in any cell in that column, Excel once again copies the new formula throughout the column.

Of course, when Excel does that, it displays the Paste Options button, which allows you to prevent this

automatic copying business. At that point, one formula in the calculated column will be different from

all the others, and it will be ﬂagged as an error unless you tell Excel differently. In addition, because of

the one inconsistent formula, any changes to the consistent formulas in that column will not be

automatically copied down the column.

•

4.
In the Error Checking Rules section, select the

check boxes for the errors you want Excel to

flag if they occur (deselect any you do not

want):

•

Formulas Which Omit Cells in a Region:

Controls whether a formula that omits

cells near a range used in the formula

(assuming these nearby cells contain

data) is flagged as an error.

Cells Containing Formulas That Result

In An Error:
Controls whether the #

error messages such as #VALUE! and

#NAME? are ignored.

•

Formulas Referring to Empty Cells:

Controls whether formulas that include

empty cells as arguments are flagged as

errors. You already know about dividing

by zero (an empty cell), and how that

is flagged with #DIV/0! message. But

other errors can occur when blank cells

are used, such as =AVERAGE(H12:J24),

in which the average would be different

if an empty cell were included.

•

Inconsistent Calculated Column

Formula In Tables:
Controls whether

an inconsistency in a calculated column

is treated as an error.

•

Cells Containing Years Represented As

2 Digits:
Controls whether a date with a

two digit year (such as 3/10/11), when

used in a formula, is flagged as an error.

•

Data Entered in a Table Is Invalid:

Controls whether invalid data (as defined

by the Data Validation command) is

flagged as an error when entered into

a table.

•

Formulas Inconsistent with Other

Formulas in the Region:
Controls

whether Formula A is flagged as an error

if adjacent cells contain similar formulas

that are different from Formula A.

5.
Click OK.