Editing a Drop Down List in Excel (2022)

BySharyn Baines

Posted:July 5, 2021

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.

Editing a Drop Down List in Excel (1)

On the Data tab, select Data Validation.

Editing a Drop Down List in Excel (2)

TheData Validation dialogue box will appear.

Editing a Drop Down List in Excel (3)

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.

Editing a Drop Down List in Excel (4)

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.

Editing a Drop Down List in Excel (5)

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

Editing a Drop Down List in Excel (6)

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

Editing a Drop Down List in Excel (7)

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

(Video) How to Edit a Drop-Down List in Excel

Editing a Drop Down List in Excel (8)

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 boxdoes not look like a range. In the example below, we can only see =SalesTeam in the Source information, and no range.

Editing a Drop Down List in Excel (9)

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.

Editing a Drop Down List in Excel (10)

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

Editing a Drop Down List in Excel (11)

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.

Editing a Drop Down List in Excel (13)

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.

Editing a Drop Down List in Excel (14)

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.

Editing a Drop Down List in Excel (15)

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.

Editing a Drop Down List in Excel (16)

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

Editing a Drop Down List in Excel (17)

Click OK.

Editing a Drop Down List in Excel (18)

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.

(Video) How to edit drop down list in Microsoft excel

Editing a Drop Down List in Excel (19)

Select the entire row you want to remove.

Editing a Drop Down List in Excel (20)

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

Editing a Drop Down List in Excel (21)

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.

Editing a Drop Down List in Excel (22)

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.

Editing a Drop Down List in Excel (23)

Go to the Data tab and select Data Validation.

Editing a Drop Down List in Excel (24)

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

Editing a Drop Down List in Excel (25)

Editing a Drop Down List in Excel (26)

Click OK.

Editing a Drop Down List in Excel (27)

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

Editing a Drop Down List in Excel (28)

Editing drop down items held in a Named range

Editing a Drop Down List in Excel (29)

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.

Editing a Drop Down List in Excel (30)

Find the named list and select it.

Editing a Drop Down List in Excel (31)

Select Edit.

Editing a Drop Down List in Excel (32)

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.

Editing a Drop Down List in Excel (33)

Editing a Drop Down List in Excel (34)

(Video) HOW TO Edit Drop Down List in Excel (find, add and remove items)

Click OK.

Editing a Drop Down List in Excel (35)

Close the Name Manager dialog box.

Editing a Drop Down List in Excel (36)

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

Editing a Drop Down List in Excel (37)

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

Editing a Drop Down List in Excel (38)

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 items in a drop-down list

  1. Click onto the cell that holds the drop-down list.
  2. 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 aNamedrange. In this example, theNamedrange is calledSalesTeam. To source this information:

  1. Go to theNamebox which is on the left of the formula bar.
  2. Click the drop-down arrow at the end of the Name box.
  3. 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.

  1. Go to the source list.
  2. Select the entire row you want to remove.
  3. Right-click and select Delete to remove the entire row.

Add items to drop down list

  1. 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.
  2. To extend the range of the drop down list to include the new item to the list, return to the worksheet with the drop-down list and select the cell that contains the drop down list.
  3. Go to the Data tab and then select Data Validation.
  4. In the Source box extend your range to include the new item.
  5. ClickOK.

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.

  1. Go to theFormulastab and then selectNamed Manager.
  2. Find the named list and select it.
  3. Select Edit. The Edit Name dialog box will open.
  4. Click into the Refers to box and edit the list range.
  5. Click OK.
  6. 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.

Excel Organise, Analyse Data

Remove Drop Down List in Excel

Read More

(Video) Excel Drop Down List Tutorial

Excel Organise, Analyse Data

Excel – name a range of cells for quick referencing

Read More

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

Yes Please!

Videos

1. How to edit drop down list in Excel
(Vishap TVplus)
2. How to Edit a Drop Down List in Excel step by step
(MJ ONLINE ACADEMY )
3. Excel Drop Down List Including Cell Colour Change: Colour Fill
(Chester Tugwell)
4. Creating & Editing Data Drop Down List in Excel
(Techy Gyan)
5. 3 Ways to Automatically Update Drop Down Lists in Excel - Data Validation
(TeachExcel)
6. How to edit and delete drop down list in Excel (Mac)
(Lets Excel)

Top Articles

Latest Posts

Article information

Author: Terence Hammes MD

Last Updated: 10/30/2022

Views: 5415

Rating: 4.9 / 5 (69 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Terence Hammes MD

Birthday: 1992-04-11

Address: Suite 408 9446 Mercy Mews, West Roxie, CT 04904

Phone: +50312511349175

Job: Product Consulting Liaison

Hobby: Jogging, Motor sports, Nordic skating, Jigsaw puzzles, Bird watching, Nordic skating, Sculpting

Introduction: My name is Terence Hammes MD, I am a inexpensive, energetic, jolly, faithful, cheerful, proud, rich person who loves writing and wants to share my knowledge and understanding with you.