July 5

0 comments

How to Edit Drop Down List in Excel (find, add and remove items)

By Sharyn Baines

July 5, 2021

Dropdown, Modify, Named Range

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.

Excel drop down list

On the Data tab, select Data Validation.

Excel drop down list

The Data Validation dialogue box will appear.

Excel drop down list

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.

Excel drop down list

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.

Excel drop down list

To check this, right-click the worksheet tabs area and select Unhide.

Excel drop down list

An Unhide dialogue box will appear, and you will see any hidden worksheets in there.

Excel drop down list

Select the worksheet you want to unhide an then click OK.

Excel drop down list

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.

Excel drop down list

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.

Excel drop down list

Click the drop-down arrow at the end of the Name box.

Excel drop down list

Any Named ranges within the workbook will now be displayed. Click onto the named range you require, e.g. SalesTeam.

Excel edit drop down list 12

And Excel will take you to the location of the Named range. This will be the location of the drop down list items.

Excel drop down list

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.

Excel drop down list

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.

Excel drop down list

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. 

Excel drop down list

An Unhide dialog box should appear, and you will see any hidden worksheets in there. Select the worksheet you want to unhide.

Excel drop down list

Click OK.

Excel drop down list

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.

Excel drop down list

Select the entire row you want to remove.

Excel drop down list

Right-click and select Delete to remove the entire row.

Excel drop down list

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.

Excel drop down 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.

Excel drop down list

Go to the Data tab and select Data Validation.

Excel drop down list

In the Source box extend your range to include the new item. In this example, we are extending the range from A13 to A14.

Excel drop down list
Excel drop down list

Click OK.

Excel drop down list

You should now have that extra item added to the drop-down list.

Excel drop down list

Editing drop down items held in a Named range

Excel drop down list

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.

Excel drop down list

Find the named list and select it.

Excel drop down list

Select Edit.

Excel drop down list

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.

Excel drop down list
Excel drop down list

Click OK.

Excel drop down list

Close the Name Manager dialog box.

Excel drop down list

You should now have the new item added to the list.

Excel drop down list

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>