▷ Data Validation in Excel | How to validate cells
By The data validation In excel it is a very useful tool when we want to restrict or delimit the information in each of the excel cells, between a range of numbers, a list of data, be it numbers or words, also when you want the texts in each of these be more specific you can add input messages or custom error messages to make data validation much clearer. It is important to know how to apply this tool to facilitate data management in your company, projects or in the field you want to implement it.
To understand the validation of data in a practical way, we will use an Excel database on the students who are going to enroll in different school grades in the year 2022. For this, information such as telephone, name, date, method of payment, the which we are going to restrict and specify according to the validation criteria by validating each data using different options of the tool Data- Data validation in excel. Download the excel template with the solved exercise here:
The path to validate data in an excel cell is as follows:
- Data > data validation > configuration window opens.
The criteria of data validation in excel are the following:
- any value
- whole number
- Decimal number
- List
- Date
- Time
- text length
- personalized
Related links:
DATA VALIDATION CRITERIA IN EXCEL:
VALIDATION CRITERIA: ANY VALUE
Used to validate on a cell any value that you want to give to the entered data, in which you can add an input or error message.
- Go to the Data tool > data validation
- Select Settings > Allow: Any Data
- Select Incoming message > Title (write title) > Incoming message (write incoming message) > accept
- Finally, the information is displayed in this way to validate the information when modifying the cell:
VALIDATION CRITERIA: INTEGER
- Go to the Data tool > data validation
- Select settings > Allow: Integer > Data: between (used to choose a specific range of numbers) > Minimum: (write number) > Maximum (write number)
- Select error message > Style: (Choose Height, Warning or Information) > Title: (write title) > Error message (write message you want to appear) > accept
- Finally, the range allowed for the cell information in this case is from 2 to 4, if we enter the number 5, 1 or a different one, the established error message will appear
VALIDATION CRITERIA: LIST
- Go to the Data tool > data validation
- Select configuration > Allow: List > source: select the data range from a list > enter or accept
- In the cell, the option to select only the data corresponding to the aforementioned list is activated. You can also enter an input message or error message by following the steps performed in the previous validation criteria.
VALIDATION CRITERIA: DATE
This criterion allows the date to have some limitations in the cell such as: dates between, is not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to. These validations are important to restrict the criteria to a date that is different from any of the above.
- Go to the data tool > data validation
- Select configuration > Allow: Date > Data: (in this case select “less than or equal to”)
- Enter the formula in End Date: =TODAY() "This formula is used to restrict dates greater than or equal to the current one when entering it in the cell
When entering a date greater than today's in the validated cell, an error message will appear which you can customize in the "error message" option.
VALIDATION CRITERIA: TEXT LENGTH
This criterion restricts the text that is entered in a cell, be it numbers or characters. For example, if we want to enter a 10-digit telephone number, we can restrict that only 10 digits are allowed, neither more nor less, and the error message can be customized so that it appears when the instructions are not followed.
- Go to the data tool > data validation
- Select settings > Allow: Text length > Data: equal to > length: (in this case 10) > accept
When typing more or less than 10 numbers in the validated cell, an error message appears.
EN 
ES