How to Edit Drop-Down List in Excel (4 Basic Approaches)

In Excel, a drop-down list is a helpful feature to fill the right data in a field (cell or column) swiftly. Once you build the drop-down list you will feel the ease of using it. Circumstances may arise when you need to edit your drop-down list.

Today we are going to show you how to edit a drop-down list. First things first, let’s get to know about today’s datasheet.

Data - How To Edit Drop Down List In Excel

Here we have listed several genres. Using this list we will create a drop-down list and edit that.

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Edit Drop-Down List in Excel

Before editing, we need to create the list. For a reminder here we are creating the list.

Here, for example, we have introduced a couple of questions.

Pre questions - How To Edit Drop Down List In Excel

The answers to these questions will be from drop-down lists. We will create two drop-down lists for the two questions.

Select the answer cell for the first one and then from the Data tab select the Data Tools option, you will find the Data Validation tool there. Click that.

Data Validation - How To Edit Drop Down List In Excel

The Data Validation dialogue box will pop up. Select List from the Allow filed box. And the range of data at the Source field.

List - Range - How To Edit Drop Down List In Excel

Here we have inserted the list using its cell reference (B4:B14) in the source field. Click OK.

You will find the drop-down list.

Drop Down list - How To Edit Drop Down List In Excel

Here we have found the genre list at the drop-down box.

Now, for the second answer, on that cell build a drop-down list inserting Yes and No at the Source field.

Drop Down list 2 - How To Edit Drop Down List In Excel

We will find Yes and No in the drop-down options.

Drop Down List 2 - How To Edit Drop Down List In Excel

1. Edit Through Data Validation Feature

For our earlier set of questions, we have set the drop-down lists.

Data set -How To Edit Drop Down List In Excel

Now let we have modified the question. The first question was “Your Favourite Genre?”, which we have modified to “Your Favourite Thriller Genre?”

Modified Question - How To Edit Drop Down List In Excel

Now the drop-down list which has all the genres as its options may seem a redundant one. Since we only need to consider Thriller genres.

Error in list - How To Edit Drop Down List In Excel

We may also need to edit the list. To edit, click the Data Validation option in Data Tools from the Data tab.

Data Validation tools - How To Edit Drop Down List In Excel

Now at the Source field change the cell reference. Here the types of Thriller genres were stored within B10 to B14.

Modify Range - How To Edit Drop Down List In Excel

So, we have changed the range to B10:B14. And click OK.

Now the drop-down list will only have the types of Thriller genres.

Modified drop down list - How To Edit Drop Down List In Excel

2. Edit Based on Name Range

We can edit the drop-down list based on the name range. Before start editing let’s create the list based on name range.

Select the cells you want into the range, and then from the Formulas tab, you will find the Defined Names option. Select the Define Name from there.

Define Name - How To Edit Drop Down List In Excel

A New Name dialogue box will be presented to you. Provide the name of the range in the Name field. And click OK (don’t forget to check the range before clicking).

Name a range - How To Edit Drop Down List In Excel

Here we have set Genre as the name of the range. Now using the Data Validation tool create the drop-down list. But this time we will use the Name Range in the Source field.

Named range in Source field - How To Edit Drop Down List In Excel

Notice that, here we have provided Genre range in the Source field. And click OK.

Drop Down list - How To Edit Drop Down List In Excel

You will find all the genres inside the drop-down list. Now if we change the question like the earlier one (“Your Favourite Genre?” to “Your Favourite Thriller Genre?”)

Change in questions - How To Edit Drop Down List In Excel

Now to edit the list, we need to modify the Genre range which we have created earlier. To modify the range, explore the Defined Names from the Formulas tab and click Name Manager.

Name Manager - How To Edit Drop Down List In Excel

You will find the Name Manager dialogue box. Edit the range there.

Change in range - How To Edit Drop Down List In Excel

Here we have updated the range, now the range is B10:B14 (earlier it was B4:B14). Now you will find only the Thriller genres.

Updated Drop Down list - How To Edit Drop Down List In Excel

3. Edit Drop-Down List Manually

We can edit the drop-down list manually. Let’s see through examples.

Manual change data - How To Edit Drop Down List In Excel

For the question, “Going to watch this weekend?” we have set Yes and No in the drop-down list. Let’s modify the list.

Using the Data Validation tool open the Data Validation dialogue box and add or delete the element from the Source field there.  

Manually change in list - How To Edit Drop Down List In Excel

Here we have added Maybe to the list. Click OK and you will find the updated drop-down list.

Manual Change list - How To Edit Drop Down List In Excel

4. Make Drop-Down List Automatically Editable

So far, we have seen how to edit the drop-down list manually after every change. If you want the list to be updated automatically, then this section will be helpful for you.

To make the list automatically editable, first of all, we need to format the list as a table. So, select the entire list and click Format as Table from the Home tab.

Format as table - How To Edit Drop Down List In Excel

Select any of the table formats from the options. And make sure to check the My table has headers option. And click OK.

Format as table - How To Edit Drop Down List In Excel

Now name the range. One way to name the range is, select the range of values and provide the name in the Name Box field.

Name Box - How To Edit Drop Down List In Excel

Here we have named the range as Genres. Now using the Data Validation tool create the drop-down list.

Name in range - How To Edit Drop Down List In Excel

Here we have used Genres as the value in the Source field. You will find the drop-down list.

Drop Down list - How To Edit Drop Down List In Excel

If we add a new genre to the table list, it will automatically be within the drop-down list.

Automatically updated list - How To Edit Drop Down List In Excel

Here we have added the Documentary genre to the list and the drop-down list automatically detects the new entry and inserts it into the list.

If we remove an item from the original list, it will automatically be removed from the drop-down list.

Automatically updated list 2- How To Edit Drop Down List In Excel

Here we have removed the Legal Thriller genre from the list and the drop-down list automatically erases that.

Conclusion

That’s all for today. We have listed several ways to edit the drop-down list in excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other approaches that we might have missed here.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo