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
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
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.