Data Validation in Excel

 

Use data validation in Excel to make sure that users enter certain values into a cell.

In this example, we restrict users to entering a whole number between 0 and 10.

Excel Data Validation Example

Create a Data Validation Rule

To create the data validation rule, execute the following steps.

1. Select cell C2.

2. On the Data tab, in the Data Tools group, click Data Validation.

Click Data Validation

On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

Validation Criteria

Input Message

An input message appears when the user selects the cell and tells the user what to enter.

On the Input Message tab:

1. Check 'Show input message when cell is selected'.

2. Enter a title.

3. Enter an input message.

Enter Input Message

Error Alert

If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check 'Show error alert after invalid data is entered'.

2. Enter a title.

3. Enter an error message.

Enter Error Message

4. Click OK.

Data Validation Result

Now, let's check if the data validation is working properly and as expected.

1. Select cell C2.

Input Message

2. Try to enter a number higher than 10.

Result:

Error Alert

Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All. You can use Excel's Go To Special feature to quickly select all cells with data validation.