This chapter explores a useful Excel feature: data validation. Data validation enables you to add rules for what's acceptable in specific cells, and it allows you to add dynamic elements to your worksheet without using any macro programming.
The Excel data validation feature allows you to set up rules that determine 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 23.1.
Excel makes it easy to specify the validation criteria. 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:
From the Settings tab of the Data Validation dialog box, you can specify a variety of data validation criteria. The following options are available from the Allow drop-down list. Keep in mind that the other controls on the Settings tab vary, depending on your choice from the Allow drop-down list.
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 these other check boxes:
One of the most common uses of data validation is to create a drop-down list in a cell. Figure 23.4 shows an example that uses region names in cells A2:A6
as the list source.
To create a drop-down list in a cell, follow these steps:
Unfortunately, you cannot control the font size used in drop-down lists. If the cell that displays the drop-down is formatted to show large text, the drop-down list does not use that formatting. If you zoom out on a worksheet, it may be difficult to read the items.
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 the Custom option and supply a formula.
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.
Specify 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 appears on the Settings tab of the Data Validation dialog box when the Custom option is selected.
We present several examples of formulas used for data validation in the section “Data Validation Formula Examples” later in this chapter.
If the formula that you enter in 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 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 required.
Follow these steps:
B2:B10
in this example) and make sure that cell B2 is the active cell. =ISODD(B2)
This formula uses the ISODD
function, which returns TRUE
if its numeric argument is an odd number. Notice that the formula refers to the active cell, which is cell B2.
An odd number is required here
in the Error Message field.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 formula for the other cells in the B2:B10
range. To demonstrate that the reference is relative, select cell B5 and examine its formula displayed in the Data Validation dialog box. 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 the value in cell C1. You would use this 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.
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 is a logical formula that returns TRUE
if the value in the cell occurs only one time in the A1:C20
range. Otherwise, it returns FALSE
, and the Duplicate Entry dialog box is displayed.
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 23.6 shows this validation criterion in effect using a custom error alert message. The user is attempting to enter 12 into cell B5.
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 (uppercase or lowercase):
=LEFT(A1)="a"
This is a logical formula that returns TRUE
if the first character in the cell is the letter A. Otherwise, it returns FALSE
. This formula assumes that the active cell in the selected range is cell A1.
The following formula is a variation of this validation formula. It uses wildcard characters in the second argument of the COUNTIF
function. 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 assumes that the cell entry is a date, and it ensures 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. Note that the WEEKDAY
function accepts any non-negative value as an argument (not just dates).
Figure 23.7 shows a simple budget worksheet, with the budget item amounts in the range B1:B6
. The planned budget is in cell E5, and the user is attempting to enter a value in cell B4 that would cause the total (cell E6) 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
As we described previously, you can use data validation to create a drop-down list in a cell (see “Creating a Drop-Down List” earlier in this chapter). This section explains how to use a drop-down list to control the entries that appear in a second drop-down list. In other words, the second drop-down list is dependent upon the value selected in the first drop-down list.
Figure 23.8 shows a simple example of a dependent list created by using data validation. Cell E2 contains data validation that displays a three-item list from the range A1:C1
(Vegetables, Fruits, and Meats). When the user chooses an item from the list, the second list (in cell F2) displays the appropriate items.
This worksheet uses three named ranges:
A2:A15
B2:B9
C2:C5
Cell F2 contains data validation that uses this formula:
=INDIRECT($E$2)
Therefore, the drop-down list displayed in F2 depends on the value displayed in cell E2.
The most common use of data validation is to prevent a user from entering invalid data. But data validation can also be used as a component of your spreadsheet's user interface without restricting the user's entry. The two examples of showing an input message and making suggestions would require quite a bit of VBA programming to accomplish, but it can be accomplished easily with data validation.
Data validation provides a way to display a message when the user selects a cell. Normally, this message would tell the user what would be considered invalid data for that cell to prevent them from getting the error message if they enter invalid data. But you can use this message for anything.
Figure 23.9 shows an input message reminding the user to complete a previous step. The Allow drop-down list is set to Any Value, so the user is not prevented from entering anything into this cell. It's just a reminder set to a cell that the user will likely use early in the process of completing this workbook.
The default Style value on the Error Alert tab of the Data Validation dialog box is Stop. This not only displays a message, but it also prevents the user from completing the entry of invalid data. There are two other options, Warning and Information, which will allow the user to enter any data. You can also deselect the check box and Excel will not show any message.
Suppose that you have a field where the user is to enter the name of a fruit. To help the user, you provide a list of common fruits, but you want the user to be able to enter fruits that aren't on the list. The list is merely there to save the user some typing if they need to enter a common fruit.
To set up this scenario, select List and point to the list of fruits, as shown in Figure 23.10. Then uncheck the Show Error Alert After Invalid Data Is Entered check box on the Error Alert tab. Now the user can select from the list or type in a fruit that's not on the list. Of course, you can't prevent the user from typing in nonsense.