Microsoft Office Tutorials and References
In Depth Information
Data Validation Errors and Criteria
The Style box in the Error Alert sheet shown in Figure 5–10 sets the level of error.
Valid entries include Stop, Warning, and Information. Figure 5–11 shows the Gender
Invalid dialog box that Excel displays when a user enters a value other than F or M into
a cell in the Gender column in the table. The Retry button leaves the invalid value in the
cell for you to change. The Cancel button removes the invalid value.
Data validation rules
can be mandatory or cau-
tionary. If the rule is man-
datory (a Stop), then Excel
rejects the cell entry via a
dialog box (Figure 5–11)
and gives you a chance
to correct it. If the rule is
cautionary (a Warning),
Excel displays a dialog box
to warn you of the invalid
entry and then gives you
a chance to redo the
cell entry or leave it as
value in cell
Excel’s built-in data validation features are powerful and easy to use. The different data
validation criteria allowed by Excel and summarized in Table 5–2 can be selected in the Allow
list in the Settings sheet in the Data Validation dialog box (Figure 5–9 on page EX 348).
Garbage In Garbage
In information processing,
the phrase “garbage in,
garbage out“, or GIGO
(pronounced gee-go), is
used to describe the out-
put of inaccurate infor-
mation that results from
the input of invalid data.
Table 5–2 Types of Data Validation Criteria Allowed
Allows the user to enter anything in the cell. Any value is the default for all cells in a worksheet.
Allows whole numbers in a speciﬁ c range.
Allows decimal numbers in a speciﬁ c range.
Allows the user to enter only an item from a list. Useful when working with codes, such as M
for male and F for female.
Allows a range of dates.
Allows a range of times.
Allows a certain length of text.
Allows you to specify a formula that will validate the data entered by the user. For example,
the formula <3 would require the cell entry to be less than 3.
Although this chapter validates only the values entered into the Gender column,
Table 5–2 shows that you can validate, in one way or another, all of the columns in the
Silver Photography Accessories Sales Rep Table. For example, you can validate the data
entered in the Age column by establishing limits for a whole number between 18 and 65.
Or, you can validate the data entered in the Hire Date column to ensure the user enters a
date between 1960 and 2008.
Excel ignores data
validation when you
paste data from the
Ofﬁ ce Clipboard or use
the mouse to copy by