Microsoft Office Tutorials and References
In Depth Information
Data Validation Errors and Criteria
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.
Validation
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
entered.
Gender
Invalid
dialog box
error
message
Retry button
leaves invalid
value in cell
Cancel button
removes
invalid value
from cell
Figure 5–11
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
Out (GIGO)
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
Description
Any value
Allows the user to enter anything in the cell. Any value is the default for all cells in a worksheet.
Whole number
Allows whole numbers in a specifi c range.
Decimal number
Allows decimal numbers in a specifi c range.
List
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.
Date
Allows a range of dates.
Time
Allows a range of times.
Text length
Allows a certain length of text.
Custom
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.
Bypassing Validation
Excel ignores data
validation when you
paste data from the
Offi ce Clipboard or use
the mouse to copy by
dragging.
 
 
Search JabSto ::




Custom Search