Using Data Validation you can restrict the type of data or the values that users can enter into a cell. It can also be used to create a drop-down list. Consider you are creating a form and in a cell, you want to have exact Product Code or an integer number between 1-10 only, You can achieve this using Data Validation. It comes in really handy if you are using that cell in a Formula and you do not want any unnecessary error due to a typo or user inputting any imaginary value.
create a Simple drop-down list
Let’s see how to use data validation in its most simple form.
Consider you are creating a form for getting information about people attending a seminar in your office, and you want to collect the following information from the people attending.
- Employee No.
- Age / Date of Birth
In this form, we can create a drop-down list for Gender containing values Male, Female & Other. All we need to do is select cell C7 (because we want the data validation in this cell), then go to
Data → Data Validation → DataValidation
In the Dialog box that appears, Select List under Allow and then enter the values (Male, Female, Other) under Source as shown below. And Click OK.
Now you can see a small drop-down icon appear beside the cell C7. If you click on that icon, the drop-down list with the values you just created will appear.
Hence the user can now input only one of three values in the given cell.
Data Validation using values from Excel Sheet
Instead of putting values manually in the source you can select a range of values from excel sheet as well. Let’s do this for Department. Under department you want the user to choose from a specific department list only. If you have the department list somewhere in Excel sheet you can choose the Data validation source from there.
Select the Cell C9, then Go to Data → Data Validation. In the Dialog Box that appears select List under Allow. Then select the small icon under Source