Content
Resources
To Do
Toolbox

 Data Validation
Data Validation

If you do manual entry into a spreadsheet, even if it’s to enter in a parameter, you not only know how important it is to enter the right kind of data, but you also appreciate adding in only a correct value. Data Validation can make sure you enter the right kind of data, assure that it is restricted to a limited set of values, in some cases, give you a drop down list of valid values and in still other cases allow only one correct entry.

The best way to apply data validation is to select the cell in which the validation will be applied. If you need the same validation in other cells, you can use Copy / Paste Special -> Validations to apply it to the other cells after you have checked it out.

This is one user interface Microsoft got right. To apply data validation, select Data -> Validation.

This selection brings up a dialog box.

This dialog box shows the various types of validations you can apply to a cell. We’ll take them one at a time.

## Validation Types

### Any value

This is the default setting for a cell and it means exactly that, any value typed in will be accepted. Use this to “remove” a validation.

### Whole Number

You can restrict entry to a whole number. Text, decimals, TRUE/FALSE, etc. will not be accepted.

When you select whole number, you get the dialog box shown above. Some examples of the use of this validation are:

• Restricting entries to positive whole numbers by selecting “greater than” and entering in 0 in the remaining box.
• Limiting priority assignments to 1, 2, or 3 by selecting “between” 1 and 3.
• Limit data entry to the current year by entering a formula like: Equal to =YEAR(TODAY())

### Decimal

Decimal is similar to whole number except that you can use decimals.

Some examples of this validation are:

• Restricting a percentage to the range 0% - 100%

### List

List is the second most versatile of the validation types.

You can type in values such as: High,Medium,Low; or you can refer to a range of data such as \$G\$2:\$G\$11 (Ten values in cells G2:G11) or a named range. If you refer to a range of cells they MUST be on the same page with the drop down list, EXCEPT if they are named ranges. A named range can be on any page in the spreadsheet. (Edit: This used to be true in versions 2003 and earlier. In 2007 and later, you can reference an unnamed range on another sheet).

If the list of values that you want to validate is in an Excel Table you can use the following as the source: =Indirect("Table_Name[Field_Name]").

You can type in values on the list manually (sorry, it does not “autofill” like its counterpart in Access) or select the value from the dropdown list.

Some uses of this type of validation are:

• Limit the selection of a priority to High, Medium or Low.
• Provide a list of names in an “Assigned to” column. This is especially powerful if the range you point to is dynamic. You can add names to the list, and the drop down list grows and shrinks with the dynamic range.

If you have your lookup data in an Excel Table, then you can use a column as a dynamic range for a list lookup. Use the following syntax: =INDIRECT("Table_Name[Table_Column]")

### Date

Date has similar settings to whole number and decimals except it works with dates.

Some examples of this validation are:

• Make sure a date value is entered (date > 01/01/1900).
• Restrict date entries for planned vacation to between January 1st and December 31st.
• Restrict date entries for a date of actual completion for a project to the current date or less.

### Time

Time Validations restrict you to a time of day.

Some uses of this validation are:

• Make sure a time entry is entered (time >= 12:00 AM)
• Restrict time entry to between shift start time and shift end time.

### Text Length

Text length measures the length of the string entered.

Some examples of this validation are:

• Limit the validation to a 4-letter office code.

### Custom Validation

I’m saving the best until last, mainly because it’s the last thing on the menu. Custom validation allows a great deal flexibility. If you can write a formula that yields TRUE or FALSE you can validate against it.

Some examples of this evaluation are:

• Prevent a duplicate entry. If you are entering values in column B, a validation such as: =COUNTIF(B:B,B9)<2 will prevent you from entering in a value that already exists in column B.
• Allow only the next number in sequence. If the last value entered is in cell B3, and you need the next number in sequence in cell B10, then the validation is =B10=B3+1. This technique also works for enforcing an entry of the next consecutive day.
• Allow only entries that are weekdays. =WEEKDAY(B12,2)<6
• Check to see if the entry can be a valid ESN (8-digit hexadecimal number). This formula works
```     =AND(LEN(B11)=8,ISNUMBER(HEX2DEC(B11))) – Well, sort of.
```

Some formulas (those that come with the analysis tool pack and HEX2DEC is one of them) don’t work in validations, even though they work in a cell. Excel gives a meaningless error message about trying to reference something off-page.

When faced with a complicated formula or, in this case, the need to work around a roadblock, a helper cell can work. That is, enter the formula above in cell D11 and validate B11 with the formula =D11=TRUE.

If you are really interested, you can check for a valid 8-digit hexadecimal number without using the analysis tool pack. All you need to enter is =AND(LEN(B11)=8,ISNUMBER(SUMPRODUCT((MATCH(MID("0"&B11,ROW(INDIRECT("1:"&LEN("0"&B11))),1),MID("0123456789ABCDEF",ROW(INDIRECT("1:16")),1),0)-1)*16^(LEN("0"&B11)-ROW(INDIRECT("1:"&LEN("0"&B11)))))))

Don’t ask me to explain it. I looked it up!

## Input Messages

Input message is used to give the user a hint as what to enter in the cell

When the cell is selected, the tip is displayed on the screen

Error Alerts allow you to display a message other than the standard error message. What happens after that depends on the alert style.

### Stop

Stop will not allow you to proceed until a valid entry is entered.

### Warning

Warning will give you an option to accept an invalid entry.

### Information

Information lets you know an invalid entry was entered, and lets you proceed

## Limitations

The attached spreadsheet contains examples of these validations.