What is Data Validation in Excel?
Microsoft Excel is a powerful tool and is widely useful too. It has various features to ease our work. One such feature is Data Validation. Now suppose you want the user to enter some specific values into the cells and for that, you need to set some pre-defined rules so that the user wouldn’t be able to enter other values and that’s where Data Validation steps in.
Data Validation gives you the control to receive particular inputs from users. We all have encountered using this feature in our day-to-day lives, one such example is while filling out forms in which the age cell will accept numbers similarly name column accepts text with limited characters, and data of birth will have years pre-defined to rule out the ineligible candidates.
The data validation function can be found in the DATA tab from the excel ribbon(as seen in the picture below).
After clicking on the Data Validation, a menu appears.
Select Data Validation and a dialogue box appear.
There are 3 tabs in the dialogue box.
- Settings: This will help you to select the data type and the type of data that you want to be filled in the desired row or column.
- Input Message: This tab will help to let the user know about the constraints you’ve decided for the row/column. It will alert the user to input the right set of values.
- Error Alert: The error alert tab will help the user to know that they had entered invalid data.
Note: The data validation feature is not 100 percent reliable. If you will try to copy the data from cells which has no defined validation rules and then try to paste those cells to cells having data validation then all the validation part get vanished. Basically, validation rules get changed from the corresponding cell based on the copied cell content.
Example of Data Validation
Let’s take the example of filling a form. The form requires your name which has a limitation of 3-7 characters, it requires your date of birth and has a list of cities for the exam centre. Not considering all the other requirements as of now.
The form looks like this.
To apply data validation with a word limit of 3-7 characters for the Name cell.
Step 1: Select the empty cell in front of the Name.
Step 2: From the DATA tab in the ribbon, select Data Validation.
Step 3: A Dialogue box will appear.
Step 4: In the dialogue box from the setting tab, in the dropdown, select Text Length (as shown in the image below).
Step 5: We want our user to enter the name between 3-7 characters, So in the Minimum column we’ll write 3 and in the Maximum column we’ll write 7 and then click OK.
The Name row will now accept only text between 3-7 characters.
To use data validation as Date of Birth:
Step 6: Select the cell in front of Data of Birth in excel.
Step 7: Repeat steps 2 and 3.
Step 8: In this step, instead of selecting text length, you need to select Date (as shown in the image below).
If you want the user must be born between 1st January 2000 to 1st January 2021. Enter the Start date as 1st January 2000 and End date as 1st January 2021.
Step 9: Click OK.
Now, the Date of the Birth row will accept dates between 1st January 2000 to 1st January 2021.
To use data validation as a List:
Step 10: Select the empty cell in front of Exam Centre..
Step 11: Repeat steps 2 and 3.
Step 12: Select List (as shown in the image below).
You want to add “Kanpur”,”Agra”,”Aligarh”,”Lucknow”,”Varanasi” to the list.
Step 13: Add the names in the source column separated by a comma(,).
Step 14: Click OK.
The Exam centre cell will look like this.
You’ve successfully created a form with 3 requirements using Data Validation.