Data Validation in Excel
Data Validation in excel is used to limit a user to provide inputs to a certain cells or input ranges, for a data with a large number of users there can be multiple formats for the same data so we use data validation where we provide a pre-defined inputs for a user to select among to avoid multiple different formats of same data, data validation is available in the data tools of data tab.
Data validation allows you to – make a list of the entries that are allowed/ not allowed to be entered in a worksheet cell, create an error/alert/warning message to explain more about the allowed values in a worksheet cell, create messages to be shown when an invalid data is entered in a cell.
Purpose of Data Validation
- The purpose of data validation is to restrict users from entering the invalid data values in the worksheet.
- It is also useful to quickly gather, sort, and study the data having said that the values have a criterion defined and can be seen via the Data Validation window.
- This acts as a guideline to the users while entering the data as custom messages mentioning the valid data format is displayed when a worksheet cell is selected.
How to Use Data Validation in Excel?
Step 1: The first step to creating data validation is to select the cells or columns containing data.
Step 2: Goto Data tab and click on Data Validation
Step 3: Choose option 1 i.e. Data Validation from the list of options.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 4: Once you click on Data Validation, a new window will appear as shown in the following screenshot.
Step 5: 1st tab is Settings. It contains validation criteria and the data format which is to be allowed.
Step 6: One can choose to ignore blank values by clicking ‘Ignore blank’ checkbox.
Step 7: 2nd tab is Input Message where one can enter the message to be displayed when a cell is selected.
Step 8: 3rd tab is Error Alert. This is used to configure the error message/s to be displayed whenever an invalid data entry is made in the configured cells or column. The types of error and hence the sign displayed for the error can be Stop, Warning or Information.
Different Data Formats of Allowed Data
- The whole Number – states that the format to be allowed is the whole numbers.
- Decimal – states that the format to be allowed is decimal numbers.
- List – states that the format to be allowed is a drop-down list.
- Date – states that the format to be allowed is the dates.
- Time – states that the format to be allowed is the time values.
- Text Length – states the allowed length of the text to be entered in a cell.
- Custom – indicates any custom formula.
Different Criteria for Data Validation
- between – states that the value should be between the specified minimum and maximum numbers.
- not between – states that the value should not be between the specified minimum and maximum numbers.
- equal to – states that the value should be equal to the given number.
- not equal to – states that the value should not be equal to the given number.
- greater than – states that the value should be greater than the given number.
- less than – states that the value should be lower than the given number.
- greater than or equal to – states that the value should be greater than or equal to in excel the given number.
- less than or equal to – states that the value should be less than or equal to the given number.
Different Alert Styles in Excel Data Validation
- Stop – It stops users from entering invalid data in a worksheet cell. The Stop alert window gives two options to the user – Retry and Cancel.
- Warning – It warns users that the invalid data is entered. However, a warning does not stop the user from entering the invalid data. It gives three options to the user – Yes, No and Cancel.
- Information – It informs user that the data entered is invalid. Like Warning, it does not stop the user from entering the invalid data. It displays two options to the user – OK and Cancel.
Example of Excel Data Validation (Number Validation)
As shown in the figure. 1, -2.5 is marked red indicating invalid data. A tooltip with a message – ‘Enter number greater than zero’ is shown. A rule assigned to column. B is as follows.
As shown in figure. 2, the validation criteria entered is, the number “decimal” number should be greater than or equal to zero. Meaning, negative values are restricted.
Things to Remember
- It is not fully proofed and be breached easily.
- If the data from cells with no-validation is copied into the cells with validation in place, then the entered data is not validated and data validation overrides the data validation resulting in no validation on the copied data.
This has been a guide to Data Validation in Excel. Here we discuss how to create Data Validation in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –