Data Validation in Excel

Data validation is an essential part of any data handling task whether you’re in the field collecting information, analyzing data, or preparing to present data to stakeholders. If data isn’t accurate from the start, your results definitely won’t be accurate either. That’s why it’s necessary to verify and validate data before it is used.

While data validation is a critical step in any data workflow, it’s often skipped over. The following examples will flesh out how data validation in Excel can ensure that users enter certain values into a cell.

Data Validation Example


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



Create 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

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.

Input Message

Input messages appear when the user selects the cell and tell 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.

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.

4. Click OK.

Data Validation Result


1. Select cell C2.

2. Try to enter a number higher than 10.


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.

Screenshot (75).png
Screenshot (76).png
Screenshot (82).png
Screenshot (83).png
Screenshot (85).png
Screenshot (86).png
Screenshot (87).png