---

Search posts under Excel:

---

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.

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...'

This opens up following dialog box.

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

select 'between' from 'Data' dropdown.

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.

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.

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.

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.

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

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

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.

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.

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.

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. 

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.

Click on 'Clear All' button.