Excel > Back to Basic

Data validation in Excel

How to validate data in MS Excel?



In previous post, we learnt about Find, Replace, Go To etc. in MS Excel. In this post, we shall learn how to validate data in MS Excel.

To understand how validation is implemented, we have created a sample form.

sample data for validation in excel

Let's say, we have a requirement for age data that it should except data between 1 to 18 only (in C4 cell). First select C4 cell by clicking on that cell. Now open 'Data Validation' dialog box by clicking on 'Data Validation' command icon from 'DATA' tab on the ribbon as shown below and choose 'Data Validation...'

Data Validation under Data tab of Excel

This opens up following dialog box.

Data validation dialog box in excel

Now select 'Whole Number' from 'Allow' dropdown.

Data validation dialog box allow

select 'between' from 'Data' dropdown.

Data validation dialog box between

write 'Minimum' text box to 1 and 'Maximum' text box to 18 in the respective text boxes. These values can be referenced by a specific cell value also by clicking on the right side icon of these text boxes.

Data validations data

If we want to provide a hint to the user who is entering the data, we can use 'Input Message' tab and fill Title and Input Message text boxes with the hint we want to show to the user.

Data validation input message

Similarly, we can also provide Error message to the user if he/she has entered wrong data into selected cell. To do this click on 'Error Alert' tab and fill respective text boxes. Please note that if this tab is not used, Excel gives default invalid data error alert.

Now, when you press OK.

Data validation error message

You are done with the validation of C4 cell. Now this cell should accept only Integer data between 1 to 18 only. 

Let's try to fill this form. As soon as we go to C4 cell, we are presented with the 'Input Message' strings we had filled into the Data Validation dialog box. Look at the picture below.

Input message in data validation

If you enter invalid data, you would be welcomed Yell with Validation failed alert that contains the data you had filled in 'Error Alert' tab.

Data validation value error in excel

If you are intelligent enough Innocent to enter correct data, you should be quitely moved to the next cell.

data validated in excel

How to provide dropdown to select data into the cell in Excel?

Many a time we want to restrict the entry of data into a particular cell. Look at the example below.

For the State field, we want to restrict the entry only to 4 states that is written in the G5:G8 cells and want to give a dropdown hint in C7 cell.

Sample data for dropdown items in excel

To do this, open Data Validation dialog box and select 'List' from 'Allow' dropdown. Keep the 'In-cell dropdown' checkbox checked. Now click on the right side icon of the 'Source' text box.

Data validation list in excel

you would get your screen like this. Now select (select and drag) the source from where you want to create dropdowon items and press Enter key or click on the right side icon of the text box.

selection of range in excel

Now click OK on Data Validation dialog box. When you go back to C7 cell, you would notice a dropdown appears as shown in the picture below that allows user to select the data for this cell. 

dropdown in excel

If user wants to write other data that is not in the dropdown, he/she gets error alert.

How to remove the data validation from a cell in MS Excel?

To remove the data validation from a cell, go to the cell and open 'Data Validation' dialog box.

Data validation list in excel

Click on 'Clear All' button.

 Views: 310 | Post Order: 10




Write for us