In our earlier post, we have discussed the methods to create the drop-down list. In case, you need to edit dropdown list i.e. if you want to add more items to the drop-down list or you want to delete the items from the drop-down list you can do it with few pretty simple steps. In this section, we will discuss the methods to edit the dropdown list.
We will edit the dropdown list using the three methods as shown below:
Steps to Edit Dropdown List in Excel 2016:
Edit Dropdown List separated by Comma
Step 1: Select the range of cells to which you have applied the dropdown list, as if you will select only one cell from the applied range then the modification will only be applicable to the selected cell only. So, select the entire range of cells to which you have applied the dropdown list.
Step 2: Then click on Data Validation from the Data Tools group in the Data tab.
Step 3: Data Validation dialog box will open. Under the Setting tab, you have an Allow dropdown list. From the provided options click on ‘List’.
In the Source box edit the list you have created earlier. If you want to add an item to the list add it by separating with a comma or if you want to delete an item, you can delete it with the backspace or delete button.
In the image below you can see that I have added a name ‘Jhona’ in the list separated by a comma. You can add more items at once.
Click OK.
Step 4: Now, click on any cell from the range to which you have applied the dropdown list. Click the dropdown arrow, scroll down and see that the item you have added is appearing in the list or the item you have deleted has vanished from the list.
Edit Dropdown List based on Range of Cells
Instead of manually adding the items to list separated by comma you can simply create a list of items in excel sheet and select that range of cells in the Source box of Data Validation dialog box.
Step 1: Come to the range of cells where you have created the list for the drop-down box it can also be in a separate sheet. Now, edit the list. Like I have added a new name ‘Jhona‘ to the list which is in Sheet2 and I have applied the dropdown list in Sheet 4 of the same document.
The list can also be in the same sheet where you have applied the dropdown list.
Step 2: Now, select the entire range of cells to which you have applied the dropdown list as you need to apply the modification to every cell in the range.
Now, from the Data tab, select the Data Validation option in the Data Tools group. Data Validation dialog box will open. In the Source box, click the selection (2) button to select the range of cells.
Step 3: Select the range of cells which has the modified data for the drop-down list.
Step 4: After the modified list is selected. Click OK.
Step 5: Click on the down arrow of you drop-down list to confirm that the item we added to list appears in the dropdown list.
Edit Dropdown List based on Named Range of Cells
Step 1: Edit the named range of cells which you have added as a source in your dropdown list. Like, I have added Roma to the range of cells which I have named as Staff.
Step 2: Go the Formulas tab, click on Name Manager in the Defined Names group.
Step 3: Name Manager dialog box will open. Under Edit tab click on the named list to which you want to edit. Like, I want to edit the Staff list so, I clicked Staff.
Click on the Refers to box, and click the select button to reselect the named range of cells.
Step 4: Reselect the named range of cells such that the modification appears in the selected range.
Click OK.
Click Close.
Step 5: Now click the down arrow of you drop-down list to see that the modification appears in the drop-down list or not.
So, this was all about the steps to edit the drops down the list. You can find more ways to do so as excel provides you with multiple methods to perform the same task.
Leave a Reply