Excel
Enter and format data
Data validation
Data validation
Add or remove items from 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
After you create a drop-down list, you might want to add more items or delete items. In this article, we'll show you how to do that depending on how the list was created.
WindowsmacOSWeb
Edit a drop-down list that's based on an Excel Table
If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you.
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
Select the worksheet that has the named range for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
Go to Formulas > Name Manager.
-
In the Name Manager box, click the named range you want to update.
-
Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
-
Click Close, and then click Yes to save your changes.
Tip:If you don't know what a named range is named, you can select the range and look for its name in the Name Box. To locate a named range, see Find named ranges.
-
Select the worksheet that has the data for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, click Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
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 on the worksheet that has the entries for your drop-down list, select all of the cells containing those entries. You'll see the list range in the Source box change as you select.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
-
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. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated 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 want to delete your drop-down list, see Remove a drop-down list.
To see a video about how to work with drop-down lists, see Create and manage drop-down lists.
Edit a drop-down list that's based on an Excel Table
If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you.
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
Select the worksheet that has the named range for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, press Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
Go to Formulas > Name Manager.
-
In the Name Manager box, click the named range you want to update.
-
Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
-
Click Close, and then click Yes to save your changes.
Tip:If you don't know what a named range is named, you can select the range and look for its name in the Name Box. To locate a named range, see Find named ranges.
-
Select the worksheet that has the data for your drop-down list.
-
Do any of the following:
-
To add an item, go to the end of the list and type the new item.
-
To remove an item, click Delete.
Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
-
-
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 on the worksheet that has the entries for your drop-down list, Select cell contents in Excel containing those entries. You'll see the list range in the Source box change as you select.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
-
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. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.
-
To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.
After you update a drop-down list, make sure it works the way you want. For example, check to see how toChange the column width and row height to show your updated 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 want to delete your drop-down list, see Remove a drop-down list.
To see a video about how to work with drop-down lists, see Create and manage drop-down lists.
In Excel for the web, you can only edit a drop-down list where the source data has been entered manually.
-
Select the cells that have the drop-down list.
-
Go to Data > Data Validation.
-
On the Settings tab, click in the Source box. Then do one of the following:
-
If the Source box contains drop-down entries separated by commas, then type new entries or remove ones you don't need. When you're done, each entry should be separated by a comma, with no spaces. For example: Fruits,Vegetables,Meat,Deli.
-
If the Source box contains a reference to a range of cells (for example, =$A$2:$A$5), click Cancel, and then add or remove entries from those cells. In this example, you'd add or remove entries in cells A2 through A5. If the list of entries ends up being longer or shorter than the original range, go back to the Settings tab and delete what's in the Source box. Then click and drag to select the new range containing the entries.
-
If the Source box contains a named range, like Departments, then you need to change the range itself using a desktop version of Excel.
-
After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries. If you want to delete your 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
Create a drop-down list
Apply Data Validation to cells
Video: Create and manage drop-down lists
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
FAQs
How do I add answers to a drop-down list in Excel? ›
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.
How do I select multiple answers from a drop-down list in Excel? ›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 I add and remove drop downs in Excel? ›Go to Data > Data Validation. On the Settings tab, click in the Source box. Then do one of the following: If the Source box contains drop-down entries separated by commas, then type new entries or remove ones you don't need.
How do I remove items from a drop-down list in Chrome? ›Remove an Entry in Autofill Dropdown
Hover over the saved entry in the dropdown to highlight it. On your keyboard, press Shift and then Delete.
- Drop down list – Content Control. Properties and settings. Add Drop Down choices. Change 'Choose an item' text.
- Another drop down list example.
- Add something to the list – Go Combo.
Drop-downs allow people to pick an item from a list that you create. 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 .
How do I edit a drop-down list in Google Sheets? ›- In Google Sheets, open a spreadsheet.
- Select the cell or cells you want to change, then select an option: Click Data. ...
- Edit the dropdown list: To change the options listed, edit the items under "Criteria." ...
- Click Done.
- Select one or more cells for your dropdown (D3:D7 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Allow drop-down box, select List.
- In the Source box, enter the formula that indirectly refers to Table1's column named Items.
Drop-down list box Like any other list, a drop-down list box offers users a list of choices. However, with drop-down list boxes, the list entries are hidden until the user clicks the control. Users can select only one item from a drop-down list.
How do I select multiple values in a dropdown? ›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 remove blanks from a drop-down list in Excel? ›
Then go to Go To Special.... In Excel 2007 and later go to the Home tab and look to the right and click the Find & Select button and then you can see this option. From the window that opens, select the Blanks option and hit OK. Now, right-click a selected cell, which will be a blank one, and click Delete.
How do I edit a drop down box in Word? ›Open the Developer tab and click the "Drop-Down List Content Control" button to insert a drop-down button. You can customize the drop-down box by clicking "Properties." A drop-down list in a Microsoft Word document lets people select from a predefined items list.
What is drop-down list control? ›The DropDownList control is a web server element that creates the drop-down menu and lets users select a single item from various options. You can add any number of items to the DropDownList. Almost all web pages contain a drop-down list, but it is mostly present in registration forms or sign-in pages.
What is the difference between list and dropdown? ›A standard list box is a box containing a list of multiple items, with multiple items visible. A drop-down list is a list in which the selected item is always visible, and the others are visible on demand by clicking a drop-down button.
How many items can be in a 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 are drop-down list functions? ›An Excel drop down list[1] is a data validation function that allows users to select an option from a list of choices. It can be particularly useful in performing financial modeling and analysis by incorporating scenarios and making a spreadsheet more dynamic.
How do I add a field to a drop-down list in Google Sheets? ›Tap the cell or cells where you want to create a drop-down list. Tap Data Validation. Under "Criteria," choose an option: List of items: To add an item, tap +Add and enter an item.
How do I make multiple selections in a drop-down list in Google Sheets? ›- Select the cell where you want the drop-down list.
- Navigate to Data >Data validation.
- In Criteria, select Dropdown (from a range) and then select the range that has the items that you want to show in the drop-down.
Select the cell or range of cells where you want the dropdown to be on your spreadsheet. Then, on the toolbar above your spreadsheet, click Insert > Dropdown. The “Data validation rules” sidebar opens to the right. Type in each item you want to appear in the dropdown list.
What is the difference between dropdown and multiple choice? ›Multiple Choice Fields: A question with a group of answers choices that lets the user select one choice only. Dropdown Menus: A question with an answer fields that expands into a list of options that lets the user select one choice.
How to select multiple options from dropdown without select class? ›
- Method 1: By storing all the options in List and iterating through it.
- Method 2: By creating Custom Locator and without iterating the List.
- Method 3: By using JavaScriptExecutor class.
- Method 4: By using sendKeys method.
Select select = new Select(driver.findElement(By.id("oldSelectMenu"))); // Get all the options of the dropdown List<WebElement> options = select.getOptions(); Using this method, we can retrieve all the options of a dropdown (be it single-select or multi-select ).
How do you select multiple items at once? ›- Click on one of the files or folders you want to select.
- Hold down the control key (Ctrl).
- Click on the other files or folders that you want to select while holding the control key.
- Continue to hold down the control key until you select all the files you want.
Click the left mouse button on the first item, hold the mouse button, move the cursor to the last item and then release the mouse button. You can also use both SHIFT and CTRL keys together.
How do you select multiple things at once on word? ›To select multiple objects, press and hold Ctrl while you click or tap the objects that you want. To select text with similar formatting, choose Select All Text with Similar Formatting.
Can a dropdown have multiple values? ›Multi select dropdown list is used when a user wants to store multiple values for the same record, whereas dropdown list is used to store a single value for a record. You can create custom categories of either dropdown list or multi select dropdown list and define items in each category.
Which tag is used to select multiple options? ›The multiple attribute on a <select> tag specifies that multiple items can be selected. With this attribute, the control appears like a scrollable list, rather than a dropdown.
Which input type is used for multiple selection? ›Tip: For <input type="file"> : To select multiple files, hold down the CTRL or SHIFT key while selecting.
How do I create a dynamic drop-down list in Excel without blanks? ›For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren't empty because they contain a space. And by doing so, the drop down list now starts from the top again.
How do I create a dynamic Data Validation list in Excel? ›- Master List of Clients.
- Format your list as Table.
- Select the source for Table.
- Name your Table and select Convert to Range.
- Give the cell Range a Name.
- Open the Data Validation window.
How do I edit a fillable text box in Word? ›
In the document, click where you want to add the control. On the Developer tab, under Form Controls, select Text Box, Check Box, or Combo Box. To set specific properties for the control, select Options, and then configure the properties that you want.
How do you add yes or no in Excel? ›- Open the table in Design View.
- In the Field Name column, select the first blank row, and then type a name for the field.
- Select the adjacent cell in the Data Type column, and then select Yes/No from the list.
- Save your changes.
- Click on the cell where you need to enter multiple lines of text.
- Type the first line.
- Press Alt + Enter to add another line to the cell. Tip. ...
- Type the next line of text you would like in the cell.
- Press Enter to finish up.
- Select the cells where you want to include this information. ...
- Select Data Validation from the Data tab on the taskbar.
- Insert the text "yes,no" into the Source field on the popup on the Settings tab.
- Check the boxes next to Ignore blank and In-cell dropdown.
- Press OK.
- Enter IF( between the = and VLOOKUP formula. The formula looks like this: =IF(VLOOKUP(F4, B$4:D$7, 2, FALSE)
- Enter >0 right after the VLOOKUP formula. ...
- Enter "Yes", which defines if the Test value returns TRUE.
- Enter "No", which defines if the Test value returns FALSE.
$D$21:$D$44 is the column of numbers to add, the amount column. $B$21:$B$44 is the first criteria range, the region column. B10 is the first criteria value, the region. $C$21:$C$44 is the second criteria range, the department column.
How do I allow multiple selections in a drop-down list? ›- Select one or more cells for your dropdown (D3:D7 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Allow drop-down box, select List.
- In the Source box, enter the formula that indirectly refers to Table1's column named Items.
- On the worksheet, click on a cell that has a drop down list.
- The VBA listbox pops up automatically, and shows all the choices from the cell's drop down list.
- Add a check mark to one or more of the items in the list box.
- When you're finished selecting items, click the OK button.
- Type the entries for the drop-down lists. ...
- Create named ranges. ...
- Make the first (main) drop-down list. ...
- Create the dependent drop-down list. ...
- Add a third dependent drop-down list (optional)