Data Validation
Excel's data validation feature is similar in many respects to the conditional formatting feature. This feature enables you to set up certain rules that dictate what you can enter into a cell. For example, you may want to limit data entry to whole numbers between 1 and 12. If the user makes an invalid entry, you can display a custom message, such as the one shown in Figure 19-26.
Figure 19-26: Displaying a message when the user makes an invalid entry.
As with the conditional formatting feature, you can use a logical formula to specify your data validation criteria.
Excel 2007 has lots of new conditional formatting features, but the data validation feature has not changed at all.
| Caution | The data validation feature suffers from a potentially serious problem: If the user copies a cell and pastes it to a cell that contains data validation, the data validation rules are deleted. Consequently, the cell then accepts any type of data. |
Specifying Validation Criteria
To specify the type of data allowable in a cell or range, follow these steps:
-
Select the cell or range.
-
Choose Data
Data Tools 
Data Validation. Excel displays its Data Validation dialog box. -
Click the Settings tab (see Figure 19-27).
Figure 19-27: The Settings tab of the Data Validation dialog box. -
Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, and display controls based on your choice. To specify a formula, select Custom.
-
Specify the conditions by using the displayed controls. Your selection in Step 4 determines what other controls you can access.
-
(Optional) Click the Input Message tab and specify which message to display when a user selects the cell. You can use this optional step to tell the user what type of data is expected. If this step is omitted, no message will appear when the user selects the cell.
-
(Optional) Click the Error Alert tab and specify which error message to display when a user makes an invalid entry. The selection for Style determines what choices users have when they make invalid entries. To prevent an invalid entry, choose Stop. If this step is omitted, a standard message will appear if the user makes an invalid entry.
-
Click OK.
After you've performed these steps, the cell or range contains the validation criteria you specified.
Types of Validation Criteria You Can Apply
The Settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow drop-down box. Keep in mind that the other controls in the Settings tab vary, depending on your choice in the Allow drop-down box.
-
Any Value: Selecting this option removes any existing data validation. Note, however, that the input message (if any) still displays if the check box is checked in the Input Message tab.
-
Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the Data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.
-
Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.
-
List: The user must choose from a list of entries you provide. This option is very useful, and I 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 by using the Data dropdown list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.
-
Time: The user must enter a time. You specify a valid time range by using the Data drop-down list. For example, you can specify that the entered data must be greater than 12:00 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. For example, you can specify that the length of the entered data 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 formula 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 two other check boxes:
-
Ignore Blank: If checked, blank entries are allowed.
-
Apply These Changes to All Other Cells with the Same Settings: If checked, the changes you make apply to all other cells that contain the original data validation criteria.
It's important to understand that even with data validation in effect, the user can enter invalid data. If the Style setting in the Error Alert tab of the Data Validation dialog box is set to anything except Stop, invalid data can be entered. Also, remember that data validation does not apply to the calculated results of formulas. In other words, if the cell contains a formula, applying conditional formatting to that cell will have no effect.
| Tip | The Data |
Figure 19-28: Excel can draw circles around invalid entries (in this case, cells that contain values greater than 100).
Creating a Drop-Down List
Perhaps one of the most common uses of data validation is to create a drop-down list in a cell. Figure 19-29 shows an example that uses the month names in A1:A12 as the list source.
Figure 19-29: This drop-down list was created using data validation.
To create a drop-down list in a cell
-
Enter the list items into a single-row or single-column range. These items are the ones that appear in the drop-down list.
-
Select the cell that will contain the drop-down list and access the Data Validation dialog box.
-
In the Settings tab, select the List option and specify the range that contains the list using the Source control.
-
Make sure that the In-Cell Dropdown check box is checked.
-
Set any other Data Validation options as desired.
After performing these steps, the cell displays a drop-down arrow when it's activated. Click the arrow and choose an item from the list that appears.
| Tip | If you have a short list, you can enter the items directly into the Source control in 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 separator specified in your regional settings; use a comma if you use the U.S. regional settings. |
| Tip | If you specify a range for a list, the range must be on the same sheet. If your list is in a range on a different worksheet, you can provide a name for the range and then use the name as your list source (preceded by an equal sign). For example, if the list is on a different sheet in a range named MyList, enter the following: =MyList |
Using Formulas for Data Validation Rules
For simple data validation, the data validation feature is quite straightforward and easy to use, but the real power of this feature becomes apparent when you use data validation formulas.
| Note | 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 specified in the Error Alert tab of the Data Validation dialog box. |
You specify a formula in the Data Validation dialog box by selecting the Custom option in the Allow drop-down list on the Settings tab. You can enter the formula directly into the Formula control, or you can enter a reference to a cell that contains a formula. Note that the Formula control appears in the Settings tab of the Data Validation dialog box only when the Custom option is selected.
If the formula that you enter contains a cell reference, that reference will be considered to be a relative reference, based on the active cell in the selected range. This works exactly the same as using a formula for conditional formatting. (See "Creating Formula-Based Rules," earlier in this chapter.)
USING DATA VALIDATION FORMULAS TO ACCEPT ONLY SPECIFIC ENTRIES
Each of the following data validation examples uses a formula entered directly into the Formula control in the Data Validation dialog box. You can set up these formulas to accept only text, a certain value, nonduplicate entries, or text that begins with a specific letter.
| On the CD | All the examples in this section are available on the companion CD-ROM. The filename is |
data validation examples.xlsx
Accepting Text Only
Excel has a Data Validation option to limit the length of text entered into a cell, but it doesn't have an option to force text (rather than a number) into a cell. To force a cell or range to accept only text (no values), use the following data validation formula:
=ISTEXT(A1)
This formula assumes that the active cell in the selected range is cell A1.
 |
Earlier in this chapter, I describe how to use custom VBA functions for custom formatting. (See "Using Custom Functions in Conditional Formatting Formulas.") For some reason, Excel does not permit you to use a custom VBA function in a data validation formula. If you attempt to do so, you get the following (erroneous) error message: A named range you specified cannot be found.
To bypass this limitation, you can use the custom function in a cell formula and then specify a data validation formula that refers to that cell.
|
Accepting a Larger Value Than the Previous Cell
The following data validation formula allows the user to enter a value only if it's greater than the value in the cell directly above it:
=A2>A1
This formula assumes that A2 is the active cell in the selected range. Note that you can't use this formula for a cell in row 1.
Accepting Nonduplicate Entries Only
The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20:
=COUNTIF($A$1:$C$20,A1)=1
This formula assumes that A1 is the active cell in the selected range. Note that the first argument for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts for each cell in the validation range. Figure 19-30 shows these validation criteria in effect, using a custom error alert message. The user is attempting to enter 17 into cell B6.
Figure 19-30: Using data validation to prevent duplicate entries in a range.
Accepting Text That Begins with a Specific Character
The following data validation formula demonstrates how to check for a specific character. In this case, the formula ensures that the user's entry is a text string that begins with the letter A (either upper- or lowercase).
=LEFT(A1)="a"
This formula assumes that the active cell in the selected range is cell A1.
The following formula is a variation of the preceding validation formula. In this case, the formula ensures that the entry begins with the letter A and contains exactly five characters.
=COUNTIF(A1,"A????")=1
Accepting Only a Date That's a Monday
The following data validation formula ensures that the cell entry is a date and also that the date is a Monday:
=WEEKDAY(A1)=2
This formula assumes that the active cell in the selected range is cell A1. It uses the WEEKDAY function, which returns 1 for Sunday, 2 for Monday, and so on.
Accepting Only Values That Don't Exceed a Total
Figure 19-31 shows a simple budget worksheet, with the budget item amounts in the range B1:B6. The total budget is in cell E5, and the user is attempting to enter a value in cell B4 that would cause the total to exceed the budget. The following data validation formula ensures that the sum of the budget items does not exceed the budget:
=SUM($B$1:$B$6)<=$E$5
Figure 19-31: Using data validation to ensure that the sum of a range does not exceed a certain value.
EAN: 2147483647
Pages: 212