This chapter explores a very useful Excel feature known as data validation. Data validation enables you to add useful dynamic elements to your worksheet without using any macro programming.
Excel’s data validation feature enables you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell 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 25.1.
Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex 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 25.2).
Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying 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.
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 drop-down 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
TrueorFalse). 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 Setting: 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 Data Tools Data Validation drop-down control contains an item named Circle Invalid Data. When you click 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 25.3, invalid entries are defined as values that are greater than 100.
Perhaps one of the most common uses of data validation is to create a drop-down list in a cell. Figure 25.4 shows an example that uses the month names in A1:A12 as the list source.
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 separators specified in your regional settings (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
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 can specify a formula in the Data Validation dialog box by selecting the Custom option in the Allow drop-down list of 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. The Formula control appears in the Setting tab of the Data Validation dialog box when the Custom option is selected.
If the formula that you enter into the Data Validation dialog box contains a cell reference, that reference is considered 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 Excel’s Data Validation rules can limit entry to odd numbers, so a formula is required.
Follow these steps:
Select the range B2:B10 and ensure that cell B2 is the active cell.
Choose Data
Data Tools Data Validation. The Data Validation dialog box appears.Click the Settings tab and select Custom from the Allow drop-down list.
Enter the following formula in the Formula box, as shown in Figure 25.5:
=ISODD(B2)
This formula uses Excel’s
ISODDfunction, which returnsTrueif its numeric argument is an odd number. Notice that the formula refers to the active cell, which is cell B2.Click the Error Alert tab and choose Stop for the Style and type An odd number is required here as the Error Message.
Click OK to close the Data Validation dialog box.
Notice that the formula entered contains a reference to the upper-left cell in the selected range. This Data Validation formula was applied to a range of cells, so you might expect that each cell would contain the same Data Validation formula. Because you entered a relative cell reference as the argument for the ISODD function, Excel adjusts the Data Validation formula for the other cells in the B2:B10 range. To demonstrate that the reference is relative, select cell B5 and examine its Data Validation formula. You’ll see that the formula for this cell is
=ISODD(B5)
Generally, when entering a Data Validation formula for a range of cells, you use a reference to the active cell, which is normally the upper-left cell in the selected range. An exception is when you need to refer to a specific cell. For example, suppose that you select range A1:B10, and you want your Data Validation to allow only values that are greater than C1. You would use this Data Validation formula:
=A1>$C$1
In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected range—which is just what you want. The Data Validation formula for cell A2 looks like this:
=A2>$C$1
The relative cell reference is adjusted, but the absolute cell reference is not.
The following sections contain a few data validation examples that use a formula entered directly into the Formula control on the Settings tab of the Data Validation dialog box. These examples help you understand how to create your own Data Validation formulas.
On the CD-ROM
All the examples in this section are available on the companion CD-ROM. The file is named data validation examples.xlsx.
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.
The following data validation formula enables 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.
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 25.6 shows this validation criterion in effect, using a custom error alert message. The user is attempting to enter 17 into cell B6.
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 uppercase 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 this 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
The following Data Validation formula ensures that the cell entry is a date, and 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.
Figure 25.7 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
