Using Data Validation in Excel

To protect against incorrect data entry, you can use data validation to restrict the type of data that may be entered into a cell (see Figure 1 below). You can specify a list of the valid entries or limit the number of characters in an entry. To further assist in accurate data entry, you can have a data input message appear that informs you of the type of data to be entered in a particular cell (see Figure 2 below). You can also have an invalid data error message appear when incorrect data is entered (see Figure 3 below).

Figure 1: The Data Validation Dialog Box

Figure 2: A user-generated data input message (Data validation help)

Figure 3: Data entry error message box (user-generated data error message)

Method to set data validation

To apply data entry restrictions:

1. Select the desired cells.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. In the Data Validation dialog box, select the Settings tab.
4. In the Validation criteria area, in the Allow drop-down list box, select the desired data type.
5. In the drop-down list box(es) that appear, make the necessary selections.
6. Choose OK.

To create a data input message:

1. Select the cells to which you have applied data validation.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. In the Data Validation dialog box, select the Input Message tab.
4. On the Input Message page, make sure the Show input message when cell is selected check box is selected.
5. In the Title text box, type a title.
6. In the Input message text box, type an input message.

To create an invalid data error message:

1. Select the cells to which you have applied data validation.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. In the Data Validation dialog box, select the Error Alert tab.
4. On the Error Alert page, make sure the Show error alert after invalid data is entered check box is selected.
5. From the Style drop-down list, select the desired style.
6. In the Title text box, type a title.
7. In the Error message text box, type an error message.

Also see Protecting your worksheet.