Download our examples
Download an example workbook with all data validation examples in this article
-
Select the cell(s) you want to create a rule for.
-
Select Data >Data Validation.
-
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.
-
-
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
-
-
On the Settings tab, under Allow, select an option:
-
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).
-
Select the Ignore blank checkbox if you want to ignore blank spaces.
-
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.
-
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).
-
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.”
Source: Microsoft