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

An Excel dropdown list lets the user streamline the data by selecting values from list items rather than typing them in manually.

Creating the list to make the input technique efficient makes you feel like a superhero – until you have to edit that drop-down list 😬

But that’s not a problem anymore, in the tutorial below will walk through all the instances where you might need to use and edit the drop-down list and how to handle that.

Download our free sample workbook here to practice along with this tutorial. That being said, let’s get right into it 😀

Table of Contents

How to Edit a Dropdown List Based on a Microsoft Excel Table

Editing a dropdown list created using a table is straightforward. To update any dropdown list, all you need to do is add or remove data from that list and Excel automatically handles the rest. Let’s see that below 👇

Add a New Value to a List

To add a new cell,

Step 1) Select the cell at the bottom of the list.

Step 2) Type in the value or text you want to add to the dropdown menu.

Step 3) The text will appear in the same order in the menu as:

Easy, right?

Delete a Cell From the List

To delete a cell,

Step 1) Select the cell you want to delete, and press the delete button on your keyboard.

The selected cell will be deleted and rows below it will be shifted upwards.

Cool, no?

How to Modify Comma Separated Dropdown List

You can also fill in values in your dropdown list using commas and modify those values too. Let’s see how to add, remove and change these values below.

To modify a comma-separated dropdown list,

Step 1) Select the range of cells containing the dropdown values.

Step 2) Go to the Data tab on the ribbon and select Data Validation under the Data Tools section.

Step 3) A Data Validation dialog box will appear on the screen.

Step 4) Update the range in the source box to include the new cell by selecting the range.

Step 5) Then, in the Source box, type in a new value to add, select and press backspace to remove a value or manually edit a value to change it.

Pro tip

If you want to apply the same setting to all other dropdown lists in your worksheet, check the Apply these changes box at the bottom of the dialog box.

Step 5) Once done, press Ok.

Step 6) The change in values will appear in the dropdown list as:

How to Edit List Based on a Range of Cells

A simple way of creating a dropdown list is to base it on a range of cells, i.e., a collection of cells containing your dropdown list 📋

Say, we have the following sample data.

To edit your dropdown list based on a range of cells,

Step 1) Add new values at the end of the list, middle of the list or wherever you like – we will add Sales at the bottom.

Step 2) Remove an item from the list by selecting and pressing the Delete button – we will delete HR.

Step 3) Once done, select the range of cells containing the list of items.

Step 4) Go to the Data tab on the ribbon and select Data Validation under the Data Tools sections right below the Remove Duplicates option at the bottom.

Step 5) In the Data Validation dialog box, select List under the Allow box, then change the range of cells in the Source box to include the newly added or removed item – you can either manually type it in or select the range from the worksheet using your cursor.

Pro tip

Make sure the in-cell dropdown box is checked, otherwise the arrowhead box will not appear on the top right corner of your drop-down list. It is checked by default, but if you see the arrowhead not appearing, this could be the key to debugging the error ⚠️

Step 6) Once done, press OK and close the dialog box.

Step 7) The dropdown list will now reflect the previously made changes on the menu.

How to Edit List Based on a Named Range

Using a Named Range is slightly different from using a range of cells. Editing a dropdown list based on a named range will automatically be reflected in all boxes related to this named range. However, you will need to update the range of the cells in the Name Manager reference box.

In that Name Box, enter a name for the range of the selected cells (for example – Dept) and it’s done. Now when you press CTRL + F3, your named range will appear in the dialog box.

To edit a dropdown list based on a named range,

Step 1) In your named range dropdown menu, edit your list by adding or removing items in the same order as you want them to appear.

Step 2) Once edited, change the reference of Named Range.

Step 3) Select any cell from the dropdown list go to the Formulas tab on the ribbon and select Name Manager under the Defined Names section or use the shortcut CTRL + F3.

Step 4) The Name Manager dialog box will appear on the screen.

Step 5) Click on the named range to be edited and click Edit.

Step 6) A small Edit Name window will appear on the screen 💻

Step 7) Under the Refers to box, update the reference of your named range to include the new items added or removed from the dropdown list.

Step 8) Once done, press Ok.

Step 9) Now click on Close on the Name Manager window and your dropdown list has been edited successfully.

Step 10) It appears with the same values as given in the dropdown list.

How to Copy the Dropdown Menu

There are two ways you can copy the dropdown menu and paste it to create a new dropdown list. Let’s see these below.

Using Paste Special

Step 1) Select the dropdown menu and press CTRL + C to copy.

Step 2) Right-click the selection where you want to paste it and select Paste Special.

Step 3) A Paste Special dialog box will appear on the screen.

Step 4) Select the Validation option under Paste and right above Operation.

Step 5) Press Ok.

Step 6) Your new dropdown list is all ready to select values from.

Using Dropdown List

Step 1) Select the column containing dropdown list and press CTRL+ C to copy.

Step 2) Click the selection where you want to paste the dropdown list and press CTRL+ V.

Step 3) Your new dropdown list is all ready to select values from.

How to Delete the Dropdown Menu

Excel offers the flexibility to delete the entire dropdown list from a range of cells in one go in addition to letting users delete individual cells. There are two ways you can delete the dropdown menu in a worksheet, these are explained below.

Delete the Drop-down Menu From Selected Cells

If you don’t want the dropdown menu to appear for certain cells only, you can choose to delete the dropdown menu from them. Let’s see how to do that below.

Step 1) Select the cells from which you want to remove the dropdown menu – we chose Mutual Funds.

Step 2) Go to the Data tab on the ribbon and select Data Validation from under the Data Tools section.

Step 3) A Clear All button can be seen at the bottom of the Settings tab in the Data Validation window.

Step 4) Click the button and press Ok.

Step 5) The dropdown menu will be deleted from the selected cell as:

The value will remain in the cell as seen above but that particular cell is now no longer linked to the dropdown list as there is no arrow head on its top left corner as seen earlier..

You can do the same for multiple selections in your worksheet.

Delete the Drop-down Menu From All Cells in the Current Worksheet

Similar to deleting the dropdown menu from various cells, there is also an option to delete the dropdown menu from all cells present in your worksheet. The method is as below:

Step 1) Select any cell from your dropdown list.

Step 2) Go to the Data tab on the ribbon and select Data Validation from under the Data Tools section.

Step 3) The Data Validation dialog box will appear.

Step 4) Check mark the Apply these changes to all other cells with the same settings on the default Settings tab that appears on the dialog box.

Step 5) Then press the Clear All button at the bottom left of the Data Validation window.

Step 6) Press Ok and close the window 🗔

Step 7) All dropdown menu references will be deleted from your current worksheet as:

The values will remain in the cells but if you hover over them, you will see that no arrowheads appear over the cells anymore which indicates the successful removal of the dropdown list 🔽

How to Add or Remove Items From a Dropdown List (Best Method)

As promised, we saved the best for the last. This method is rather beginner-friendly and is more relevant to those who prefer editing their dropdown lists without using the Data Validation feature of Excel. That being said, let’s cover all the details step-by-step below.

Add an Item to the Drop-down Menu

Here’s the easiest hack you’re going to find on the Internet to add values to a dropdown list.

Step 1: In the list of items, select the position where you want to insert a new value or the order in which you want it to appear.

To do that,

Step 1) Right-click the cell below the position where you want to insert the new cell.

Step 2) From the dropdown menu that appears, select Insert.

Step 3) Select Shift cells down from the Insert window.

Step 4) Press Ok.

Step 5) Now add the new value you want to store in that cell.

Step 6) And it’s done! The new value has been added to the dropdown list and the reference of the dropdown menu is automatically updated in the Data Validation dialog box to accommodate the new value.

Pretty cool no?

Remove an Item From Drop-down Menu

The technique for removing an item from the dropdown list is the same as adding a new item to the list. Let’s look at the steps below.

Step 1) Select the cell you want to delete from the dropdown list.

Step 2) Right-click the cell and select Delete from the menu.

Step 3) A Delete Cells dialogue box will appear on the screen, select Shift cells up from the options.

Step 4) Press Ok.

Step 5) The value will be deleted from the cell range as well as the dropdown list in one go.

That was easy, no?

Conclusion

Editing dropdown lists in Excel can feel like a chore, but that’s happening no more – thanks to this guide. It takes you through all the ins and outs and tricks of editing and updating dropdown menus using the quickest ways possible.

Regardless of how your list is set up, you can now easily change, add and remove values without fretting over the data validation settings. Not only will you now be able to keep your data streamlined but it will also be more clean and accurate.

Check out my other articles related to dropdown lists and related concepts.

• How to Create Dropdown Lists in Excel for Data Entry
• 3 Methods to Remove Drop-Down Lists
• How to Clear All Filters in Microsoft Office Excel
• How to Use Data Validation in Excel

After reading these blogs, your understanding and working of dropdown lists will be at its peak. That being said, I hope you enjoyed reading this piece as much as we did creating it 😀