Learn how to edit a drop-down list in Excel.
In this blog you will learn how to find, remove and add items to the drop-down list. An extra step is also included for those using older versions of Excel.
How to find items in a drop-down list
Click onto the cell that holds the drop-down list. In this example it is in cell B3.
On the Data tab, select Data Validation.
The Data Validation dialogue box will appear.
The Source information will tell you the source or location of the list. In this example, the drop-down list is coming from the Sales Team worksheet, cells A2 to A13. If at this point the Source information displays just a name, e.g. =salesteam, and not a range, please check the instruction 'When the drop-down list items are held in a Named Range' below.
Note: If you can see the list location in the Source box, but cannot see the worksheet where the information is located, it may be because someone has hidden the worksheet. For example, in the image below we cannot see the Sales Team worksheet tab. However we know that the list is in the Sales Team worksheet, therefore it must be hidden.
To check this, right-click the worksheet tabs area and select Unhide.
An Unhide dialogue box will appear, and you will see any hidden worksheets in there.
Select the worksheet you want to unhide an then click OK.
Note: if the worksheet has been protected, you may not be able to unhide it.
When the drop-down list items are held in a Named Range
Sometimes the information shown in the Source box does not look like a range. In the example below, we can only see =SalesTeam in the Source information, and no range.
This is because the list has been saved as a Named range. In this example, the Named range is called SalesTeam.
In order to find its source information, go to the Name box which is on the left of the formula bar.
Click the drop-down arrow at the end of the Name box.
Any Named ranges within the workbook will now be displayed. Click onto the named range you require, e.g. SalesTeam.
And Excel will take you to the location of the Named range. This will be the location of the drop down list items.
Note: If you have not been taken to the list it may be because the worksheet this named range lives in is hidden. If this is the case, follow these steps.
Select the Formulas tab and then Name Manager.
You will see the Named range in the list. The Refers To column will display the location of the Named range. In the example below you can see the Named range is located on the Sales Team worksheet.
Close the Name Manager dialog box and then click on to the worksheet that contains the drop down list menu items.
If the worksheet is hidden, right-click the worksheet tabs and select Unhide to unhide the worksheet and get to the list.
An Unhide dialog box should appear, and you will see any hidden worksheets in there. Select the worksheet you want to unhide.
Click OK.
Note: if the worksheet has been protected, you may not be able to unhide the worksheet.
You can now update information in the drop down items list.
Note: This range is dynamic which means if you update or change names on the original list, this will instantly update the drop-down list. For example, if we changed Able’s name to Abel, this will be updated over in the drop-down list in cell B3.
Remove items from a drop-down list.
Go to the source list. In this example, it is the Sales Team list.
Select the entire row you want to remove.
Right-click and select Delete to remove the entire row.
Note: Make sure to delete the entire row rather than deleting the information in the cells as it prevents you from having a lot of blank spaces in your drop-down list menu.
Add items to drop down list
To add an item to a drop down list, just add it to the list. If you wish to add it between existing items, insert a new row where you would like it included in the list, and then type the item name. For this example, Zander was added to the bottom of the list.
We now need to extend the range for the drop down list items in order to include this new item into the list.
To do this, return to the worksheet with the drop-down list. In this example, this is the ‘Drop down list’ worksheet. Select the cell that contains the drop down list.
Go to the Data tab and select Data Validation.
In the Source box extend your range to include the new item. In this example, we are extending the range from A13 to A14.
Click OK.
You should now have that extra item added to the drop-down list.
Editing drop down items held in a Named range
If your list is held in a Named range, you will not be able to extend the range in the Data Validation box.
Instead, try the following.
Go to the Formulas tab and select Named Manager.
Find the named list and select it.
Select Edit.
The Edit Name dialog box will open. Click into the Refers to box and edit the list range. In the example below we have extended the range from A13 to A14 to include the new list item.
Click OK.
Close the Name Manager dialog box.
You should now have the new item added to the list.
Was this blog helpful? Let us know in the Comments below.
Editing a Drop Down List in Excel
Wanting to learn how to edit a drop-down list in Excel? Follow this step by step guide to lean how to find, remove and add items to the drop down list.
Instructions
How to find the source items in a drop-down list
- Click onto the cell that holds the drop-down list.
- On the Data tab, select Data Validation. The Data Validation dialogue box will appear. The Source information will tell you the source or location of the list.
When the drop-down list items are held in a Named Range
Sometimes the information shown in the Source box does not look like a range. This is because the list has been saved as a Named range.
- Click the drop-down arrow at the end of the Name box (the Name box is located to the left of the Formula Bar. It will display the cell address of the cell that is currently selected).
- Any Named ranges within the workbook will now be displayed. Click onto the Named Range you require. Excel will take you to the location of the Named range. This will be the location of the drop down list items.
Remove items from a drop-down list.
- Go to the source list.
- Select the entire row you want to remove.
- Right-click and select Delete to remove the entire row.
Add items to drop down list
To add an item to a drop down list, just add it to the list. If you wish to add it between existing items, insert a new row where you would like it included in the list, and then type the item name.
To extend the range of the drop down list to include the new item:
- Return to the worksheet that contains the drop-down list. Select the cell or cells that contain the drop down list.
- Go to the Data tab and then select Data Validation.
- In the Source box extend your range to include the new item from the source list.
- Click OK.
Editing drop down items held in a Named range
If your list is held in a Named range, you will not be able to extend the range in the Data Validation box. Instead, try the following.
- Go to the Formulas tab and then select Named Manager.
- Find the Named Range and select it.
- Select Edit. The Edit Name dialog box will open.
- Click into the Refers to box and edit the list range.
- Click OK.
- Close the Name Manager dialog box.
Notes
Note: If you can see the list location in the Source box, but cannot see the worksheet where the information is located, it may be because someone has hidden the worksheet.
Note: This range is dynamic which means if you update or change names on the original list, this will instantly update the drop-down list.
If you enjoyed this post check out the related posts below.