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.
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, remove the current refers to data, 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 to Change 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 Community or get support in Communities.
