Apply data validation to cells

Web:

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. 

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
  1. On the Settings tab, in the Allow box, select List.
  1. In the Source box, type your list values, separated by commas. For example, type Low,Average,High.
  1. Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  1. Test the data validation to make sure that it is 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, then go to Data > Data Validation and in the data validation dialog press the Clear All button, then click OK.

Adding other types of data validation

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.Follow steps 1-2 above.From the Allow list, select Whole number.In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.Enter the minimum, maximum, or specific value to allow.You can also enter a formula that returns a number value.For example, say you’re validating data in cell F1. To set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.
Restrict data entry to a decimal number within limits.Follow steps 1-2 above.In the Allow box, select Decimal.In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.Enter the minimum, maximum, or specific value to allow.You can also enter a formula that returns a number value. For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson’s salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.Note: To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style in the Number group on the Home tab.
Restrict data entry to a date within range of dates.Follow steps 1-2 above.In the Allow box, select Date.In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.Enter the start, end, or specific date to allow.You can also enter a formula that returns a date. For example, to set a time frame between today’s date and 3 days from today’s date, select between in the Data box, enter =TODAY() in the Start date box, and enter =TODAY()+3 in the End date box.
Restrict data entry to a time within a time frame.Follow steps 1-2 above.In the Allow box, select Time.In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.Enter the start, end, or specific time to allow. If you want to enter specific times, use the hh:mm time format.For example, say you have cell E2 set up with a start time (8:00 AM), and cell F2 with an end time (5:00 PM), and you want to limit meeting times between those times then select between in the Data box, enter =E2 in the Start time box, and then enter =F2 in the End time box.
Restrict data entry to text of a specified length.Follow steps 1-2 above.In the Allow box, select Text Length.In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.In this case we want to limit entry to 25 characters, so select less than or equal to in the Data box and enter 25 in the Maximum box.
Calculate what is allowed based on the content of another cell.Follow steps 1-2 above.In the Allow box, select the type of data that you want.In the Data box, select the type of restriction that you want.In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.For example, to allow entries for an account only if the result won’t go over the budget in cell E1, select Allow >Whole number, Data, less than or equal to, and Maximum >= =E1.

Examples of formulas in data validation

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 thatEnter 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.For more information
Ensure that an e-mail address entry in cell B4 contains the @ symbol.=ISNUMBER(FIND(“@”,B4))

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

More on data validation

Add or remove items from a drop-down list

Remove a drop-down list

Leave a Reply

%d bloggers like this: