You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list.
Need help setting this up?
If you'd rather not deal with the technical side, we can fully set up and manage your Microsoft 365 for you — including email, DNS, and ongoing support.
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.”
Still stuck? Ask an expert.
Get live, instant help from a Got It Excel expert. Experts are available now, and are online 24/7. Try it now.
Get connected now>
Subject to Got It terms and conditions.
Related Guides
- Split text into different columns with the Convert Text to Columns Wizard
- Import or export text (.txt or .csv) files
- Insert or delete cells, rows, and columns
- Collaborate on Word documents with real-time co-authoring
- OneDrive basics
Prefer us to handle this?
We provide fully managed Microsoft 365 setup and support — so you can focus on running your business.
