Intro to Excel
- Create a new workbookArticle
- Insert or delete a worksheetArticle
- Move or copy worksheets or worksheet dataArticle
- Print a worksheet or workbookArticle
- Use Excel as your calculatorArticle
- Fill data automatically in worksheet cellsArticle
- Create a drop-down listArticle
Next:
Excel
Enter and format data
Data validation
Data validation
Create a drop-down list
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More...Less
You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.
Windows macOS Web
-
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table . If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T .
Notes:
-
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list , any drop-downs you based on that table will automatically update. You don't need to do anything else.
-
Now is a good time to Sort data in a range or table in your drop-down list.
-
-
Select the cell in the worksheet where you want the drop-down list.
-
Go to the Data tab on the Ribbon, then Data Validation .
Note: If you can’t click Data Validation , the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.
(Video) How to Add a Drop-Down List in Word | Create a Drop-Down Box | Insert a Drop-Down Menu (UPDATED) -
On the Settings tab, in the Allow box, click List .
-
Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:
-
If it’s OK for people to leave the cell empty, check the Ignore blank box.
-
Check the In-cell dropdown box.
-
Click the Input Message tab.
-
If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
-
-
Click the Error Alert tab.
-
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
-
-
Not sure which option to pick in the Style box?
-
To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon
and Warning will show a message with this icon . -
To stop people from entering data that isn’t in the drop-down list, click Stop .
Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
-
and Warning will show a message with this icon 
. 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 Change the column width and row height to show all your entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them .
If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list .
To delete a drop-down list, see Remove a drop-down list .
You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples .
Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.
-
Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.
-
Select the cells that you want to restrict data entry in.
-
On the Data tab, under Tools , click Data Validation or 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 .
-
Click the Settings tab, and then in the Allow pop-up menu, click List .
-
Click in the Source box, and then on your sheet, select your list of valid entries.
The dialog box minimizes to make the sheet easier to see.
-
Press RETURN or click the Expand
button to restore the dialog box, and then click OK . Tips:
-
You can also type values directly into the Source box, separated by a comma.
-
To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.
-
You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate , and then click the Error Alert tab.
-
button to restore the dialog box, and then click OK . See also
Apply data validation to cells
-
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table .
Notes:
-
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list , any drop-downs you based on that table will automatically update. You don't need to do anything else.
-
Now is a good time to Sort your data in the order you want it to appear in your drop-down list.
-
-
Select the cell in the worksheet where you want the drop-down list.
-
Go to the Data tab on the Ribbon, then click Data Validation .
(Video) Dependent Drop Down List in Excel Tutorial -
On the Settings tab, in the Allow box, click List .
-
If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:
Fruit,Vegetables,Grains,Dairy,Snacks
-
If it’s OK for people to leave the cell empty, check the Ignore blank box.
-
Check the In-cell dropdown box.
-
Click the Input Message tab.
-
If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
-
-
Click the Error Alert tab.
-
If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type , and type a title and message. If you don’t want a message to show up, clear the check box.
-
-
Click OK .
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 Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list . To delete a drop-down list, see Remove a drop-down list .
Need more help?
You can always ask an expert in the Excel Tech Communityor get support in the Answers community.
See also
Apply data validation to cells
All Excel functions (by category)
Creating drop-down lists (free preview)
Need more help?
Expand your skills
Explore Training
Get new features first
FAQs
How do I create a drop-down list in Microsoft? ›
Select + (insert) from the left pane. Select Drop down.
How do I limit a drop-down list in Excel based on a cell value? ›- Select the cells where you want to restrict data entry.
- On the Data tab, click Data Validation > Data Validation. ...
- In the Allow box, select the type of data you want to allow, and fill in the limiting criteria and values.
- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.
How do you create a drop-down list with multiple selections in Word? ›Under Insert controls, click Multiple-Selection List Box. If you cleared the Automatically create data source check box in step 3, select a repeating field in the Multiple-Selection List Box Binding dialog box to which you want to bind the multiple-selection list box.
What is a dynamic drop down list? ›Limit choices in one Excel drop down list, based on the value in another cell. This example uses dynamic arrays and spilling to create the lists used in the drop downs. Select a region name, and then select an employee from that region.
How do I create a cell drop down Data Validation rule? ›- Step 1 - Select The Cell For Validation. Select the cell you want to validate. ...
- Step 2 - Specify Validation Criteria. ...
- Step 3 - Under Allow, Select The Criteria. ...
- Step 4 - Select Condition. ...
- Step 5 - Input Message. ...
- Step 6 - Custom Error Message. ...
- Step 7 - Click Ok.
- Get items for the main drop down list. ...
- Create the main drop down. ...
- Get items for the dependent drop down list. ...
- Make the dependent drop down. ...
- Set up the first drop down. ...
- Configure the second drop down. ...
- Set up the third drop down. ...
- Formula for the dependent dropdown.
A cascading drop-down list is a series of dependent DropDownList controls in which one DropDownList control depends on the parent or previous DropDownList controls. The items in the DropDownList control are populated based on an item that is selected by the user from another DropDownList control.
How do you make Data Validation dynamic? ›How do you validate dynamic data? In Excel, you can validate dynamic data using a dynamic drop down list. This is done by selecting the source for the drop down list as the range where new dynamic data will be added later.
What control allows multiple selections? ›
To select multiple options, hold down the Control (Ctrl) key if you are using a PC system or the Apple key if you are using a Macintosh system, while you click on the several options that you wish to select.
How do I create a fillable drop-down list in Word? ›- Go to Developer > Combo Box Content Control or Drop-Down List Content Control .
- Select the content control, and then select Properties.
- To create a list of choices, select Add under Drop-Down List Properties.
- Type a choice in Display Name, such as Yes, No, or Maybe.
- If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
- Under Insert controls in the Controls task pane, click Drop-Down List Box. ...
- Click List Box. ...
- Double-click the drop-down list box that you inserted in your form template in step 2.
- Select the cell where you want the first (main) drop down list.
- Go to Data –> Data Validation. ...
- In the data validation dialog box, within the settings tab, select List.
- In Source field, specify the range that contains the items that are to be shown in the first drop down list.
- selectByVisibleText: selectByVisibleText(String arg0): void. ...
- selectByIndex: selectByIndex(int arg0) : void. ...
- selectByValue: selectByValue(String arg0) : void. ...
- getOptions: getOptions( ) : List<WebElement> ...
- deselectAll()
In General, Avoid Drop-Downs When There Are More Than 10 or Fewer Than 5 Options. Drop-downs quickly become difficult for users when they are presented with an overwhelming number of options to choose from. Take, for instance, a commonly included input in checkout forms, the “Country Selection” drop-down.
How many types of dropdowns are there? ›I also want to clarify that there are two main types of dropdowns: those used for navigation and those used in forms.
What are the 3 types of Data Validation? ›The following are the common Data Validation Types:
Range Check. Format Check. Consistency Check.
- Click in cell B15.
- Click the Data tab.
- From the Data Tools group, click Data Validation.
- Select List in the Allow drop-down.
- In the Source, type =INDIRECT($B$14)
Create a custom Data Validation rule for multiple criteria
Finally, select the cells you want to apply the rules to, then go to your Data tab and select Data Validation. From the “Allow” drop-down menu, select “Custom.” In the Formula field box, type an “ = “ and use Ctrl+V to paste your nested formula in the field.
What is the maximum limit for drop-down list in Excel? ›
There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.
What is the difference between dropdown and accordion? ›The accordion will push down the content, while the dropdown lays over the content.
What is the purpose of dropdown? ›A drop-down list (abbreviated drop-down, or DDL; also known as a drop-down menu, drop menu, pull-down list, picklist) is a graphical control element, similar to a list box, that allows the user to choose one value from a list. When a drop-down list is inactive, it displays a single value.
What are Dropdowns used for? ›Offering drop-down menus can help users avoid scrolling and can quickly get them access to your site's content. For large websites, drop-down menus can save users time by allowing them to jump down a level or two to get to the content they seek.
Which are good methods of validation of data? ›- Data Type Check. A data type check confirms that the data entered has the correct data type. ...
- Code Check. A code check ensures that a field is selected from a valid list of values or follows certain formatting rules. ...
- Range Check. ...
- Format Check. ...
- Consistency Check. ...
- Uniqueness Check.
The best way to ensure the high data quality of your datasets is to perform up-front data validation. Check the accuracy and completeness of collected data before you add it to your data warehouse. This will increase the time you need to integrate new data sources into your data warehouse.
Can you make Data Validation conditional? ›You can also combine multiple conditions to create a data entry rule. This type of conditional data entry in excel can be done using the data validation feature in Excel. It can enable data entry in the specified cells only when the specified conditions are met, else it shows an error.
How do I make multiple selections in a drop-down list in HTML? ›For windows: Hold down the control (ctrl) button to select multiple options. For Mac: Hold down the command button to select multiple options.
How do I make multiple selections from a drop-down list in Excel without duplicates? ›- Select the column range you want to use in the table, the press Ctrl + C to copy it, and place it to another position by pressing Ctrl + V.
- Then keep selecting the list, click Data > Remove Duplicates.
- On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
- Go to Data > Data Validation.
- On the Settings tab, click in the Source box, and then change your list items as needed.
What are the two different ways to implement multi selections? ›
Multiway selection chooses among several alternatives. C has two different ways to implement multiway selection: the switch statement and else-if construct.
How do you select multiple items at once? ›Press and hold CTRL. Select the next item that you want. Important Be sure to press and hold CTRL while you select the next item that you want to include in the selection.
How do you multi select? ›Multiple items are selected by holding down SHIFT and clicking them with the mouse or by holding down SHIFT and pressing an arrow key to extend the selection from the previously selected item to the current item.
How do I create a drop-down list in Word 2022? ›Place your cursor in the document where you want to add the drop-down list. Go to the Developer tab and click the Drop-Down List Content Control button. You'll see the control pop into your document as a box with “Choose an item.” inside. You can then move on to setting up the list.
How do I create a drop down box in Microsoft teams? ›Select a cell where you want a drop-down list. Click the DATA tab, and click Data Validation. In the Data Validation dialog, set Allow to List; this enables a list in the cell. Leave In-cell drop-down selected; this enables a drop-down list in the cell.
Can you create dropdowns in Outlook? ›With the control selected, click the Properties button in the Controls group. In the Content Control Properties window, click the Add button, then type the first item in the Value box (the Display Name box will get filled automatically), and click OK. Repeat for all other items you wish to include in the list.
How do I create a drop down bullet in Word? ›- Select the text or numbered list you want to change.
- On the Home tab, in the Paragraph group, click the arrow next to Multilevel List. ...
- Specify a name for your new list style.
- Choose the number to start the list at. ...
- Choose a level in the list to apply your formatting.
Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu. In the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box.
How do I use drop-down list content control in Word? ›- Go to Developer > Combo Box Content Control or Drop-Down List Content Control .
- Select the content control, and then select Properties.
- To create a list of choices, select Add under Drop-Down List Properties.
- Type a choice in Display Name, such as Yes, No, or Maybe.
...
Create a dropdown list
- Enter “@.” In the Menu, under the components section, click “Dropdowns."
- At the top, click Insert. Dropdown.
- Click Data. Data validation. Add rule .
- Right click on a cell. Dropdown.
How do I create a drop-down list in Office 365? ›
- Select the cells that you want to contain the lists.
- On the ribbon, click DATA > Data Validation.
- In the dialog, set Allow to List.
- Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.
- To show the Developer tab in Word, go to Files > Options > Customize Ribbon. ...
- Open a new blank document and type the text used for the email template.
- Select the content where you want to insert the drop-down menu.
In Word, Drop down list is a good content control for users to select one of preset values from pull-down menu. This article will show you the detailed steps to insert a drop down list in your Word document.