5 THINGS ABOUT DATA VALIDATION IN EXCEL

Excel is not only restricted to maintaining, organizing or managing the data; it also performs functions like the creation of survey forms, games, etc. There are tools such as developer tabs, data validation, and many more to perform such different functions in Excel. As the name suggests, data validation is related to the validity of the data being entered into a particular cell or sheet.

5 THINGS ABOUT DATA VALIDATION IN EXCEL

Here are 5 things about data validation in Excel.

• Where to use Data Validation

Data validation restricts the type of data that can be stored in a cell. The type of data can be a date within a range, number—whole numbers, decimals, time in a given interval, text of a specified length, entries from a list, etc. By applying data validation, you restrict the entries into the cells. The cell automatically rejects values other than the type specified by the user under the data validation window. 

• Adding data validation

The data validation can be inserted into a cell by going to the Data Tab > Data tools section > Data validation. Now, you have the data validation window. Under the settings tab, select the validation criteria. You have chosen the type as a date. Now you can select the validation as date equals to, less than, more than, in between, etc. Supposedly, you choose the date. Between now and then, you need to enter the start and end dates. Check the information entered and press OK. Now, if you go back to the cell, you will observe a warning sign as soon as you enter any other data type or a date beyond the validation criteria.

• Removing the Data Validation

To remove the data validation from a cell or a range of cells, the procedure is, 

• Select the validated cells. 

• Go to the data tab > data tools > Data validation

• In the data validation window, select the option to clear everything. 

Now, you have successfully removed the validation from the selected cells. 

• Input message 

Creating a sheet for other users requires adding an input message while validating the data. When the cell is selected, the input message is displayed to the users to specify the entry that will be accepted by the cell. The input message can be of the following type: kindly enter a valid date or your age, etc. 

• Error alert

When a data entry is made in a cell that does not meet the validation criteria, an error message can be displayed to warn users. The warning message or the error alert can be inserted by shifting to the error alert section in the data validation window. 

Data validation is an essential feature of Excel that many analysts use to collect data. It helps to restrict the data that a user enters in the cells. It helps to eliminate irrelevant data responses by limiting the input.