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 Create a Drop-Down List With Multiple Options in Excel : Computers & Tech Tips -
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
Add or remove items from a drop-down list
Video: Create and manage drop-down lists
Overview of Excel tables
Apply data validation to cells
Lock or unlock specific areas of a protected worksheet
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 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.
- 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.
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.
What is a dynamic drop-down list? ›It's a list that we can select from in order to filter attendance data by department. The source of this dynamic data validation list has been created using the UNIQUE function. UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. In other words, it removes duplicates from a data set.
How do I create a cell drop-down Data Validation rule? ›- Open the Data Validation dialog box (Data tab > Data Validation).
- On the Settings tab, select List in the Allow box.
- In the Source box, type the items of your Excel validation list, separated by commas.
- 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.
Which action will allow a user to select multiple items? ›To select multiple items in a list, hold down the Ctrl (PC) or Command (Mac) key. Then click on your desired items to select. All of the items you have selected should be highlighted with a different-colored background. Note: Be sure to hold the Ctrl (PC) or Command (Mac) key down while selecting multiple items.
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.
Once you have saved your template, insert a drop-down box. Click the "Developer" tab that now appears in the Ribbon above your Word page. Click "Design Mode" in the Controls group to turn on Design Mode. This allows you to make changes to the drop-down box.
How do I enter data in one place in a document and have it automatically repeated in other places? ›Under Insert controls, click Repeating Section. In the Repeating Section Binding dialog box, select the repeating group in which you want to store repeating section data, and then click OK. Add controls to the repeating section, and then bind them to the appropriate fields in the data source.
How do I create a drop-down list in Word 365? ›To add a drop-down list to your Microsoft Word document, click the Developer tab at the top and then select Drop-Down List Content Control. This drop-down list will be empty by default and you'll have to customize it to add useful options to the text box.
What are different methods for dropdown? ›- 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.
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.
How do I create multiple Data Validation in Excel? ›- 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. ...
- Make sure that the In-cell dropdown check box is selected.
- Type any item from your custom list in a cell.
- Select the cell, and point to the Fill handle.
- Drag down, up, left or right, to AutoFill the list.
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 create a dependent drop-down list? ›
...
To create each list, you will do three things:
- Type the items on the Lists sheet.
- Format the lists as an Excel table.
- Name the lists.
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 you add a drop-down list to multiple cells? ›- Type the items in separate cells.
- Select the cell where you want the drop-down list to appear.
- On the Data tab, click Data Validation.
- Place the cursor in the Source box or click the Collapse Dialog icon, and select the range of cells to include in your drop-down list.
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.
Which displays a list of items user can select one or more items? ›List boxes are used to present a scrollable list of choices to a user. To select something from a list box, a user clicks the item that they want, which highlights their choice.
Which tool is used to list items that user can select? ›The ListBox represents a Windows control to display a list of items to a user. A user can select an item from the list.
What allow the user to select one or more options from a set? ›Checkboxes allow the user to select one or more options from a set. It is usually best to present checkboxes in a vertical 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 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.
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 create a drop-down selection sheet? ›...
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.
- 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.
- Place your cursor in the heading.
- On the Home tab, click the arrow in the Paragraph group.
- In the Paragraph dialog box, click the checkbox next to Collapsed by default.
- 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.
Once you have saved your template, insert a drop-down box. Click the "Developer" tab that now appears in the Ribbon above your Word page. Click "Design Mode" in the Controls group to turn on Design Mode. This allows you to make changes to the drop-down box.
How do I create a drop down text? ›Example Explained
HTML) Use any element to open the dropdown content, e.g. a <span>, or a <button> element. Use a container element (like <div>) to create the dropdown content and add whatever you want inside of it. Wrap a <div> element around the elements to position the dropdown content correctly with CSS.