In Excel, a drop down list is a helpful feature to swiftly fill in the right data in a field (cell or column). 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 drop down list in Excel.
How to Create Drop Down List in Excel
Before editing, we need to make the drop-down list. First things first, let’s get to know about today’s datasheet.
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. You may encounter a much larger and more complex dataset
in a practical scenario.
Here, for example, we have introduced a couple of questions.
The answers to these questions will be from drop-down lists. We will create two drop-down lists for the two questions.
- First, select the answer cell for the first one and then from the Data tab select the Data Tools option > click Data Validation.
- Then, the Data Validation dialogue box will pop up. Select List from the Allow field box. And the range of data at the Source field.
- Here, we have inserted the list using its cell reference (B5:B15) in the source field. Click OK.
- You will find the drop-down list. We have found the genre list in 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.
- We will find Yes and No in the drop-down options.
Read More: How to Create Drop Down List with Filter in Excel
How to Edit Drop Down List in Excel: 3 Methods
Now, we will move to the next part. We want to edit the dropdown list that we created a little bit ago.
This section will find 4 basic approaches to edit drop down list in Excel. Let’s demonstrate them one by one here.
1. Use Data Validation Feature to Edit Drop Down List
For our earlier set of questions, we have set the drop-down lists.
Now let we have modified the question. The first question was “Your Favourite Genre?”, which we have modified to “Your Favourite Thriller Genre?”
- Now the drop-down list which has all the genres as its options may seem redundant. Since we only need to consider Thriller genres.
- Here, we may also need to edit the list. To edit, click the Data Validation option in Data Tools from the Data tab.
- Now at the Source field change the cell reference. Here the types of Thriller genres were stored within B11 to B15.
So, we have changed the range to B11:B15. And click OK.
- Now the drop-down list will only have the types of Thriller genres.
- Using the Data Validation tool open the Data Validation dialogue box and add or delete the element from the Source field there.
- You can see the list has been edited.
2. Editing Drop Down List with Named Range
We can edit the drop-down list based on the name range. Before starting editing let’s create the list based on name range.
- First, 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.
- Here, 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).
- Here we have set Type 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.
- Notice that here we have provided the Genre range in the Source field. And click OK.
- Now to edit the list, we need to modify the Genre range we created earlier. To modify the range, explore the Defined Names from the Formulas tab and click Name Manager.
- Hence, you will find the Name Manager dialogue box. Edit the range there.
- 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.
Read More: How to Link a Cell Value with a Drop Down List in Excel
3. 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.
- Then, select any of the table formats from the options. And make sure to check the My table has headers option. And click OK.
- 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.
- Here we have named the range as Genres. Now using the Data Validation tool create the drop-down list.
- Here we have used Genres as the value in the Source field. You will find the drop-down list.
- If we add a new genre to the table list, it will automatically be within the drop-down list.
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.
Here we have removed the Legal Thriller genre from the list and the drop-down list automatically erases that.
Read More: How to Add Item to Drop-Down List in Excel
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
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.
Related Articles
- How to Create a Drop Down List from Another Sheet in Excel
- Create Excel Drop Down List from Table
- How to Remove Drop Down List in Excel
- How to Create Excel Drop Down List with Color
- How to Create a Drop Down List with Unique Values in Excel
- How to Copy Filter Drop-Down List in Excel
- Excel Drop Down List Not Working
