Edit Drop Down List in Excel (Table of Content)
How to Edit Drop Down List in Excel?
Excel drop down list are very useful to view a large amount of data in a small area. They also allow you to add limitations for users so that they can’t make any additional changes other than recommended. There might be some situations where you, being a source wanted to update the contents of drop-down lists. You may get in trouble if you don’t understand from where Excel gets the values for drop-down list. If you know the source of the Excel list, you can update it and quickly as per requirements. This article focuses mainly on how you can edit/update the Excel drop-down lists using Excel’s Data Validation tools.
Examples to Edit Drop Down List in Excel
Here are the following Examples which will help you to Edit Drop Down list in excel
Example #1 – Editing a Comma Separated List
A comma-separated drop-down list is the one under which you have defined list variables manually using comma to separate them. Suppose you have some questions to the person who came for an interview in your company, those questions are having polar answers (Yes/NO/May Be). You can see the image below for better generalization.
When we say comma separated values, we mean that these values are manually entered as an input to the drop-down list while creating the same. As you can see in the below image, the values (Yes, No, May Be) are updated manually under source with comma as a separator.
You have preferences noted as below image shows:
Suppose we wanted to change the source value from “May Be” to “Not Sure”. You can do the same by following the below steps:
- In your excel spreadsheet, select all the cells which have reference to your drop-down list (In this example, select all rows from column Answers) i.e. all cells which have drop-down box is applied and you wanted to edit.
- From Excel Ribbon, click Data tab > Data Validation.
- A Data Validation window will pop up.
- Add or remove list values under source. You can delete all of them and define a new set of values. In this case, you have replaced list value “May Be” with “Not Sure” and Click OK. It will save the changes under list values and close the Data Validation window.
- You can see the changes updated in the list under below image.
By this way, we can edit the comma-separated drop-down list.
Example #2 – Editing Named Range Drop Down List
What if, you have created a drop-down list using named ranges? Suppose you have employee names recorded in alphabetical order in one column of excel. The range of Employee Names is named as Employees by you. Now, whenever, you’ll be creating a drop-down list based on this range, you’ll use the range name “Employees” instead of using whole cell references. It makes your life easy. Suppose below is my example of Named Ranges.
If you want to edit drop-down lists with named ranges, then you just need to update the items under your range and change the reference of the named range under Name Manager (If some new entries are added). The drop-down list associated with named range will automatically be updated based on the changes you made.
Follow the steps below:
- Go to the spreadsheet which has your named ranges. Updated the range data (either delete some entries or add some entries instead of the previous ones).
- From excel ribbon, go to Formula tab > Name Manager (or press Ctrl + F3 simultaneously).
- The Name Manager window will open. Under Name Manager Window, select the named range which you wanted to update. Change the reference under Refers to the box by selecting all the entries you want under your drop-down list through Collapse Dialog icon. Once done updating as per your requirements, you can close the Name Manager by clicking Close
- You can see the drop-down list associated with your named range (Employee Names Column) is updated (Kofi is replaced by Lalit and Scharlet is replaced by Suhana respectively).
Example #3 – Editing a Drop Down List based on the Table Range
What if the data you are using as a range to create a drop-down list? Is it possible to update/edit such data?
The answer is yes!
However, there is no conventional way to edit the drop-down list based on a Table Range. The reason behind it is, when we use Table as a source/range to a drop-down list, the range gets fixed under Name Manager.
You can’t edit the range there. However, as it is a table, whenever you add entries to the table, the range will be automatically extended to the last row of the table.
In this way, editing drop-down list with the table as a source is more user-friendly to edit. Just add the elements to the table and the range will automatically be updated.
See the example below:
Column A contains a table which has employee names as an input.
You can see the drop-down list, which we have created giving table Employee Names as a source. When you add an entry at the last of the table (for example, Mohan), the drop-down list automatically gets updated and you don’t need to update it manually (Like it was the case in Comma Separated and Named Range drop-down list). This is an added benefit of the drop-down list generated using the table as a source.
See the output below:
This was it from this article.
Things to Remember About Drop Down List in Excel
- Excel drop-down lists based on Named Ranges provide a name to the drop-down range, which can be used while creating the drop-down list as a reference instead of giving a full cell reference (which contains your ranged data). However, it is also possible to create a drop-down list without providing the name to the range.
- Drop-down list based on table ranges is the best source to create a drop-down list as it allows the drop-down list to update automatically as soon as we add the entries to the data table. Which advantage is not seen in any other method.
Recommended Articles
This has been a guide to Drop Down List in Excel. Here we discussed How to Edit Drop Down List in Excel along with Examples and downloadable excel template. You may also look at the following articles to learn more –
