Data
Validation in Excel
1. Select the cell(s) you want to
create a rule for.
2. Select Data >Data Validation.
3. On the Settings tab, under Allow,
select an option:
Whole Number - to restrict the cell to accept
only whole numbers.
Decimal - to restrict the cell to accept
only decimal numbers.
List - to pick data from the drop-down
list.
Date - to restrict the cell to accept
only date.
Time - to restrict the cell to accept
only time.
Text Length - to restrict the length of the text.
Custom – for custom formula.
4. Under Data, select a condition:
between
not between
equal to
not equal to
greater than
less than
greater than or equal to
less than or equal to
5. On the Settings tab, under Allow,
select an option:
6. Set the other required values,
based on what you chose for Allow and Data. For example, if you select between,
then select the Minimum: and Maximum: values for the cell(s).
7. Select the Ignore blank checkbox
if you want to ignore blank spaces.
8. If you want to add a Title and
message for your rule, select the Input Message tab, and then type a title and
input message.
9. Select the Show input message when
cell is selected checkbox to display the message when the user selects or
hovers over the selected cell(s).
10. Select OK.
Now, if the user tries to enter a value that is not valid, a pop-up
appears with the message,
“This value doesn’t match the data validation restrictions for this
cell.”
