Apply data validation to cells
Use data validation to restrict the type of data or the values that users enter into a cell, like a dropdown list.
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.
-
Set the other required values based on what you chose for Allow and Data.
-
Select the Input Message tab and customize a message users will see when entering data.
-
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 the Error Alert tab to customize the error message and to choose a Style.
-
Select OK.
Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message.
Download our examples
Download an example workbook with all data validation examples in this article
If you're creating a sheet that requires users to enter data, you might want to restrict entry to a certain range of dates or numbers, or make sure that only positive whole numbers are entered. Excel can restrict data entry to certain cells by using data validation , prompt users to enter valid data when a cell is selected, and display an error message when a user enters invalid data.
Restrict data entry
-
Select the cells where you want to restrict data entry.
-
On the Data tab, select Data Validation.
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
-
In the Allow box, select the type of data you want to allow, and fill in the limiting criteria and values.
Note: The boxes where you enter limiting values are labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you can enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.
Prompt users for valid entries
When users select a cell that has data entry requirements, you can display a message that explains what data is valid.
-
Select the cells where you want to prompt users for valid data entries.
-
On the Data tab, select Data Validation.
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
-
On the Input Message tab, select the Show input message when cell is selected check box.
-
In the Title box, type a title for your message.
-
In the Input message box, type the message that you want to display.
Display an error message when invalid data is entered
If you have data restrictions in place and a user enters invalid data into a cell, you can display a message that explains the error.
-
Select the cells where you want to display your error message.
-
On the Data tab, select Data Validation .
Note: If the validation command is unavailable, the sheet might be protected or the workbook might be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook .
-
On the Error Alert tab, in the Title box, type a title for your message.
-
In the Error message box, type the message that you want to display if invalid data is entered.
-
Do one of the following:
To
On the Style pop-up menu, select
Require users to fix the error before proceeding
Stop
Warn users that data is invalid, and require them to select Yes or No to indicate if they want to continue
Warning
Warn users that data is invalid, but allow them to proceed after dismissing the warning message
Important
Add data validation to a cell or a range
Note: The first two steps in this section are for adding any type of data validation. Steps 3-7 are specifically for creating a drop-down list.
-
Select one or more cells to validate.
-
On the Data tab, in the Data Tools group, select Data Validation.
-
On the Settings tab, in the Allow box, select List.
-
In the Source box, type your list values, separated by commas. For example, type Low,Average,High.
-
Make sure that the In-cell dropdown check box is selected. Otherwise, you won't see the drop-down arrow next to the cell.
-
To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
-
Test the data validation to make sure that it's working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
Notes:
-
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries.
-
Remove data validation - Select the cell or cells that contain the validation you want to delete, go to Data > Data Validation and in the data validation dialog box press Clear All, and then select OK.
The following table lists other types of data validation and shows you ways to add it to your worksheets.
To do this: |
Follow these steps: |
---|---|
Restrict data entry to whole numbers within limits. |
|
Restrict data entry to a decimal number within limits. |
|
Restrict data entry to a date within range of dates. |
|
Restrict data entry to a time within a time frame. |
|
Restrict data entry to text of a specified length. |
|
Calculate what is allowed based on the content of another cell. |
|
Notes:
-
The following examples use the Custom option where you write formulas to set your conditions. You don't need to worry about whatever the Data box shows, as that's disabled with the Custom option.
-
The screen shots in this article were taken in Excel 2016 ; but the functionality is the same in Excel for the web .
To make sure that |
Enter this formula |
---|---|
The cell that contains a product ID (C2) always begins with the standard prefix of "ID-" and is at least 10 (greater than 9) characters long. |
=AND(LEFT(C2,3)="ID-",LEN(C2)>9) |
The cell that contains a product name (D2) only contains text. |
=ISTEXT(D2) |
The cell that contains someone's birthday (B6) has to be greater than the number of years set in cell B4. |
=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE) |
All the data in the cell range A2:A10 contains unique values. |
=COUNTIF($A$2:$A$10,A2)=1 Note: You must enter the data validation formula for cell A2 first, then copy A2 to A3:A10 so that the second argument to the COUNTIF will match the current cell. That is the A2)=1 portion will change to A3)=1, A4)=1 and so on. |
Ensure that an e-mail address entry in cell B4 contains the @ symbol. |
=ISNUMBER(FIND("@",B4)) |
Tip: If you're a small business owner looking for more information on how to get Microsoft 365 set up, visit Small business help & learning.