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
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment