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.
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.
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.
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.
The Data Validation dialogue box will pop up. Select List from the Allow filed box. And the range of data at the Source field.
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.
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.
We will find Yes and No in the drop-down options.
1. Edit Through Data Validation Feature
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 a redundant one. Since we only need to consider Thriller genres.
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 B10 to B14.
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.
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.
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 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.
Notice that, here we have provided Genre range in the Source field. And click OK.
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?”)
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.
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.
3. Edit Drop-Down List Manually
We can edit the drop-down list manually. Let’s see through examples.
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.
Here we have added Maybe to the list. Click OK and you will find the updated drop-down list.
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.
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.
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.