Microsoft Office Tutorials and References
In Depth Information
If you have a short list, you can enter the items directly into the Source control of the
Settings tab of the Data Validation dialog box. (This control appears when you choose
the List option in the Allow drop-down list.) Just separate each item with list separators
specified in your regional settings (a comma if you use the U.S. regional settings).
Using Formulas for Data Validation Rules
For simple data validation, the data validation feature is quite straightforward and easy to use. The real power of
this feature, though, becomes apparent when you use data validation formulas.
The formula that you specify must be a logical formula that returns either TRUE or
FALSE. If the formula evaluates to TRUE, the data is considered valid and remains in
the cell. If the formula evaluates to FALSE, a message box appears that displays the
message that you specify on the Error Alert tab of the Data Validation dialog box. Speci-
fy a formula in the Data Validation dialog box by selecting the Custom option from the
Allow drop-down list of the Settings tab. Enter the formula directly into the Formula
control or enter a reference to a cell that contains a formula. The Formula control ap-
pears on the Setting tab of the Data Validation dialog box when the Custom option is
I present several examples of formulas used for data validation in the upcoming section “Data Validation For-
Understanding Cell References
If the formula that you enter into the Data Validation dialog box contains a cell reference, that reference is con-
sidered a relative reference, based on the upper-left cell in the selected range.
The following example clarifies this concept. Suppose that you want to allow only an odd number to be entered
into the range B2:B10. None of the Excel data validation rules can limit entry to odd numbers, so a formula is
Follow these steps:
1. Select the range (B2:B10 for this example) and ensure that cell B2 is the active cell.
2. Choose Data ⇒ Data Tools ⇒ Data Validation. The Data Validation dialog box appears.
3. Click the Settings tab and select Custom from the Allow drop-down list.
4. Enter the following formula in the Formula field, as shown in Figure 20-5: