Data validation is a feature in Excel used to control what a user can enter into a cell. It can also stop invalid user input.
Data validation can be used to present the user with a predefined choice in a dropdown menu. This can be a convenient way to give a user exactly the values that meet requirements.
When adding a data validation rule in Excel, you can choose one of the predefined settings or specify custom criteria based on your own validation formula.
Whole numbers and decimals
To restrict data entry to a whole number or decimal, select the corresponding item in the Allowbox. And then, choose one of the following criteria in the Data box:
-
- Equal to or not equal to the specified number
-
- Greater than or less than the specified number
- Between the two numbers or not between to exclude that range of numbers
For example, this is how you create an Excel validation rule that allows any whole number greater than 0:
Date and time validation in Excel
To validate dates, select Date in the Allow box, and then pick an appropriate criterion in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more.
Similarly, to validate times, select Time in the Allow box, and then define the required criteria.
For example, to allow only dates between Start date in B1 and End date in B2, apply this Excel date validation rule:
Text length
To allow data entry of a specific length, select Text length in the Allow box, and choose the validation criteria in accordance with your business logic.
For example, to limit the input to 10 characters, create this rule:
Note. The Text length option limits the number of characters but not the data type, meaning the above rule will allow both text and numbers under 10 characters or 10 digits, respectively.
Limitation
It is important to understand that data validation can be easily defeated. If a user copies data from a cell without validation to a cell with data validation, the validation is destroyed (or replaced). Data validation is a good way to let users know what is allowed or expected, but it is not a foolproof way to guarantee input.
References
https://exceljet.net/excel-data-validation-guide
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501