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 in Excel (Table of Contents)
- Data Validation in Excel
- Purpose of Excel Data Validation
- How to Use Data Validation in Excel?
- Examples of Excel Data Validation
Data Validation in Excel
- 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 Excel Data Validation
- The purpose of data validation in excel 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 is 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?
Here we are going to discuss the steps to create data validation in excel.
- The first step to create data validation in excel is to select the cells or column containing data.
- Goto Data tab and click on Data Validation
- Choose option 1 i.e. Data Validation from the list of options.
- Once you click on Data Validation, a new window will appear as shown in the following screenshot.
- 1st tab is Settings. It contains validation criteria and the data format which is to be allowed.
- One can choose to ignore blank values by clicking ‘Ignore blank’ checkbox.
- 2nd tab is Input Message where one can enter the message to be displayed when a cell is selected.
- 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 Excel 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.
Examples of Excel Data Validation
It is very simple and easy to use. Let understand the working of Data validation by some examples.
Example #1 – Number Validation
As shown in figure. 1, -2.5 is marked red indicating an invalid data. A tooltip with 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 –