How to Use Data Validation in Excel

You can use Excel's data validation options to control the type and value of data entered into specific cells in a worksheet.

Data Validation in Excel

In addition to placing restrictions on the type of data allowed in a cell, you can also create an Error Alert message explaining the limitations when someone inputs invalid data.

The levels of control you can apply to cells are:

• Restricting the format and range of data that users can enter into a cell.
• Displaying a prompt message when a cell containing data validation restrictions is clicked on but with no data restrictions on the cell itself.
• Using a formula located in a different location to determine if data entered into a cell is valid.

There are three types of error alerts:

• Stop — Prevents the entry of invalid data.
• Warning — Warns that invalid data is in a cell with an option to override the restrictions.
• Information — Informs users that invalid data is in a cell but does not prevent its entry.

Error Alerts are displayed only when data is typed into a cell; they do not appear if one of the following data entry methods is used:

• Entering data via the fill handle even if it adds invalid data
• Entering data via a formula that calculates an invalid result
• Entering data via a macro that enters invalid data into a cell

Prevent Invalid Data Entry

In this example, we apply data validation to cell D1 that allows only whole numbers with a value of less than 5. If invalid data is entered into the cell, a Stop error alert displays.

1. Select cell D1, which is the location where data validation will be applied.

2. Select the Data tab. Choose the Data Validation icon in the Data Tools section of the ribbon to open the dialog box.

   

3. Select the Settings tab.

   

4. Under the Allow option choose Whole Number from the list.

5. Under the Data option choose less than from the list.

6. In the Maximum line type the number 5.

7. Select the Error Alert tab.

   

8. Make sure the Show error alert after invalid data is entered box is checked.

9. Under the Style option choose Stop from the list.

10. In the Title line, type Invalid Data Value.

11. In the Error message line type Only numbers with a value of less than 5 are allowed in this cell.

12. Select OK to close the dialog box and return to the worksheet.

Test the Data Validation

Finally, you should ensure that the data validation for cell D1 is functioning as it should — to restrict the entry of numbers with a value greater than 5:

1. Select cell D1.

2. Type the number 9 in cell D1.

3. Press the Enter key on the keyboard. The Stop error alert message box appears since this number is greater than the maximum value set in the dialog box.

   

4. Select the Retry button on the error alert message box.

5. Type the number 2 in cell D1.

6. Press the Enter key on the keyboard.

7. The data should be accepted in the cell since it is less than the maximum value set in the dialog box.