![Making Making](/uploads/1/2/5/1/125130711/592910308.gif)
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.
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel Online More. Less You can help people work more efficiently in worksheets by using drop-down lists in cells where they can pick an item from a list you create.
WindowsmacOSWeb
Download our examples
- 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.”
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.
- Which version are you using?
Restrict data entry
- Select the cells where you want to restrict data entry.
- On the Data tab, click Data Validation > 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 will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.
Prompt users for valid entries
When users click in 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, click Data Validation > 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.
![Excel for mac 2011 making a named list for a drop down listen Excel for mac 2011 making a named list for a drop down listen](http://www.get-digital-help.com/wp-content/uploads/2010/07/dependent-drop-down-list2.png)
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, click Data Validation > 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:ToOn the Style pop-up menu, selectRequire users to fix the error before proceedingStopWarn users that data is invalid, and require them to select Yes or No to indicate if they want to continueWarningWarn users that data is invalid, but allow them to proceed after dismissing the warning messageImportant
Restrict data entry
- Select the cells where you want to restrict data entry.
- On the Data tab, under Tools, click Validate.Note: If the validation command is unavailable, the sheet might be protected or the workbook may 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 Allow pop-up menu, select the type of data you want to allow.
- On the Data pop-up menu, select the type of limiting criteria that you want, and then enter limiting values.Note: The boxes where you enter limiting values will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.
Prompt users for valid entries
When users click in 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, under Tools, click Validate.Note: If the validation command is unavailable, the sheet might be protected or the workbook may 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, under Tools, click Validate.Note: If the validation command is unavailable, the sheet might be protected or the workbook may 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:ToOn the Style pop-up menu, selectRequire users to fix the error before proceedingStopWarn users that data is invalid, and require them to select Yes or No to indicate if they want to continueWarningWarn users that data is invalid, but allow them to proceed after dismissing the warning messageImportant
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, click 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 be able to 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 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. |
|
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. |
|
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 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. 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
There is not a sane person alive who would refuse advice on how to make data entry easier. If time is money, then data entry is an invasive species of inchworm that eats away at your money tree. In Excel, you can save gobs of time with drop-down lists. Even if you have time to waste and money coming out of your ears, drop-down lists allow you to restrain entries to certain items that you define; in other words, they give you power. I’ll teach you how to create a drop-down list in Excel 2010 (screenshots are from 2011, but I will note relevant differences when applicable), tell you everything you need to know about customizing, and then harness the power of letting go by learning how to safely delete them forever.
The main function of a drop-down list is to create shortcuts to valid entries that exist elsewhere in your workbook; namely, those that would save a lot of time if centrally located.
The first thing we need is a list. Perhaps you are a coffee aficionado and have a well-documented list of Arabica coffees (in which case, you’d probably enjoy the chance to get privy to the secrets of espresso machines). Anyway:
A couple things to note: the list must be in a single column or row, make sure there are no blank cells, and structure the items in the order you wish to appear in the drop-down list. The cell width will determine the width of your list; entries that are too long will be truncated with ellipses (. . .), so take this into consideration.
Also, if you want to use a list from another worksheet, just define a name for it first. This can be done by selecting the range of cells, clicking the Name box at the left side of the formula bar, and entering a name of your choosing.
Ok. Go ahead and pick a cell for your drop-down list (I’m going to name C1 “Today’s Cup” and then use C2 for my drop-down list) and click Data Validation, which will be located under the Data tab. In Excel 2010, Data Validation will be its own option; in 2011, it is located in the same place, but within Validate:
The Data Validation dialogue box will launch. Under the Settings tab you will find an Allow option; select List. This will automatically prompt you to the Source box:
We have to specify the location of our list, for which we have two options. If our list resides in a different worksheet, this step is actually easier; just type the list name in the Source box. But if our list is in our current worksheet, we have to either enter a reference to our list or manually select a range from the current spreadsheet. To be fair, both methods are quite easy. It almost goes without saying, but make sure that you enter your reference or list name as you would a formula and precede it with an equal sign (=):
Check out the new drop-down list in C2:
Not working? Select your list cell again and re-open the Data Validation dialogue box. Make sure In-Cell dropdown is selected; that’s an obvious prerequisite. While we have the dialogue box open, let’s look at some of the ways you can customize your drop-down list.
- Maybe you want to ensure the cell cannot be left blank. In this case, select the Ignore blank checkbox. If not, leave it unchecked.
- If you want a reminder or warning message to display, select the Input Message tab. Check the box beside Show input message when cell is selected. Now all you have to do is think of a message and give it a title:
- The last thing you can do is specify a response when invalid data is entered or selected. Select the Error Alert tab. This will look almost identical to the Input Message tab and it works the same way; click the box beside Show error alert after invalid data is entered and choose an appropriate title and message. You can also pick a logo style: Stop, Warning, or Information:
If you don’t want to fuss over all that and will be satisfied with a default error message, just click the Show error alert box and let Excel do the rest (FYI, the title will be “Microsoft Excel” and the message “The value you entered is not valid. A user has restricted values that can be entered into this cell.”). Boring but to the point.
Unfortunately, all good things must come to an end. Maybe it’s a stomach ulcer. Maybe all the caffeine you’re consuming is making you anxious. Whatever the scenario, you need to delete your drop-down list and rid your brain of its delicious memory. Waste no time in selecting the cell that contains the list. Open the Data Validation dialogue box. Select the Settings tab and click Clear All. You can still click Cancel at this point if you aren’t quite ready to quit, but I recommend you reclaim your health and finalize it by clicking Ok.
I had some fun with my example, but anyone even mildly proficient in Excel can foresee huge advantages of the drop-down list; in finance, sales, IT, etc. The applications are endless; get more ideas from these Excel for business tutorials. Before I sign out, I want to leave you with some relevant food for thought: the maximum number of entries that you can have in a drop-down list is 32,767. I’m sure you could Google the answer, but frankly, the world could use more mysteries like that.