Data Validation in Excel

Options

Several validation options are available:

1. Any value
2. Whole number
3. Decimal
4. List
5. Date
6. Time
7. Text length
8. Custom

List-style example

The List-style validation compares the input values against a list in the form of a range of cells in Excel. The list can be on a different sheet or even a different workbook. The dropdown has the added advantage of making data entry extremely easy and accurate.

Download and import both CSV files into one Excel workbook.

1. Activate the Data menu and select E2:E1043 (select E2 and use the Ctrl + Shift + ↓ keyboard shortcut) on the album-data sheet
2. Click Data Validation to open the Data Validation options dialog and select the List option from the Allow dropdown on the Settings tab
3. Click in the Source field and select the range B1: B69 on the music-genres sheet
4. Leave the Ignore blank & In-cell dropdown options (both of these options are self-explanatory – play with them)
5. Select the Input Message tab
6. Leave the Show input message when cell is selected option checked
7. Enter an appropriate message Title
8. Enter an appropriate Input message
9. Select the Error Alert tab
10. Leave the Show error alert after invalid data is entered option checked
11. Enter an appropriate error Title
12. Enter an appropriate Error message
13. Click the OK button
14. Try out your new validation: click in cell E2 and observe the input message you created in Step 8 above
15. Type a valid entry into E2 and watch the “type-ahead” value appear from the list
16. Click on the dropdown control
17. Select an option from the dropdown list
18. Enter a value not in the list to test your Error Alert.
19. Click the Cancel button

In the example above the validation list is on a second sheet in the same workbook. You could “protect” your validation list by entering it on a separate worksheet and protecting and hiding that sheet.