Part IV: Managing and Analyzing Data
List The user must choose from a list of entries that you provide. Type a comma-separated
list of values in the Source text box. This option is useful, and we discuss it in detail later
in this chapter. (See “Creating a Drop-Down List.”)
Date The user must enter a date. You specify a valid date range from the choices in the
Data drop-down list. For example, you can specify that the data entered must be greater
than or equal to January 1, 2019.
Time The user must enter a time. You specify a valid time range from choices in the Data
drop-down list. For example, you can specify that the data entered must be greater than 12
p.m.
Text Length The length of the data (number of characters) is limited. You specify a valid
length by using the Data drop-down list and the Length text box. For example, you can
specify that the length of the data entered be 1 (a single alphanumeric character).
Custom To use this option, you must supply a logical formula that determines the validity
of the user’s entry. (A logical formula returns either TRUE or FALSE.) You can enter the for-
mula directly into the Formula control (which appears when you select the Custom option),
or you can specify a cell reference that contains a formula. This chapter contains examples
of useful formulas.
The Settings tab of the Data Validation dialog box contains these other check boxes:
Ignore Blank If selected, blank entries are allowed.
In-cell Dropdown If you select List in the Allow drop-down list, you can choose to show
or hide a drop-down arrow in the cell to aid the user in the selecting a valid value.
Apply These Changes to All Other Cells with the Same Settings If selected, the changes
that you make apply to all other cells that contain the original data validation criteria.
The Data ➪ Data Tools ➪ Data Validation drop-down list contains an item named Circle Invalid Data. When you
select this item, circles appear around cells that contain incorrect entries. If you correct an invalid entry, the circle
disappears. To get rid of the circles, choose Data ➪ Data Tools ➪ Data Validation ➪ Clear Validation Circles. In
Figure 26.3, valid entries are defined as values between 1 and 105. Values that are not within this numerical range
are circled.
