How to Edit Drop Down List in Excel? Read This Simple Guide

A drop down list is a list that contains several options from which a user can choose one option. Known by many other names as well, such as a pull down list or a drop down menu, a drop down list is a graphical control element. The list appears whenever a user clicks on a button or a piece of text.

Microsoft Excel, which is a spreadsheet program developed by Windows, also has the feature of a drop down list. It has a plenty of other features too, such as making a histogram, creating pivot tables, removing duplicates, making calculations, and creating macros.

Users can make a drop down list for a variety of purposes in the MS Excel program. MS Excel runs on Windows, macOS, Android, and iOS (https://stopie.com/how-to-clear-instagram-cache-from-android-and-iphone-find-it-out-here/) operating systems. So, the ability to make a drop down list in cells is also available on all the devices that run on these operating systems.

A lot of users remain confused about how to edit drop down list in Excel. Editing the list is necessary to add or remove items. The process required to be followed in order to add or remove items from a drop down list in the Microsoft Excel program varies from one scenario to another. Let’s have a look at the different scenarios and the steps to be followed.

How to Edit Drop Down Lists Created in Different Ways?

Users can create drop down lists in different ways. The method you need to use to edit a drop down list depends on how you have created your drop down list in MS Excel. Drop down lists can be created based on an Excel table, a named range, a range of cells, and the data entered manually.

We have explained how to edit drop down list in Excel based on how the list was created. Continue reading…

How to Edit a Drop Down List Based on an Excel Table?

If the drop down list in your Excel worksheet is based on an Excel table, the process of adding or removing items from the list is quite simple. Whenever you add or remove an item from the table, the drop down list will get updated automatically.

1. In order to add an item, you need to go to the last row in the column. Now, press the Enter key and enter the name of the new item. Press the Enter key again.
2. In order to remove an item, you need to right-click it. A list of options will appear. You need to select the option ‘Delete’ and then select the option ‘Table Rows’. The item will be removed from both the table and the drop down list.

It’s worth mentioning here that if the item you want to remove is in the middle of the table, you should move the remaining items up to fill the empty cell, or else the removed item will leave a space in your drop down list. We are sure that you must have understood how to edit drop down list in Excel if the list is based on an Excel table.

How to Edit a Drop Down List Based on a Named Range?

Editing a drop down list that has been created based on a named range is different from editing a drop down list. You need to follow the steps mentioned below to edit a drop down list based on a named range in the Microsoft Excel program.

1. Type the new item at the end of the list to add an item. If you want to remove an item, select it and press the Delete key. If the item you want to delete is in the middle of the list, you should shift the remaining items up, or else your drop down list will have a blank space.
2. Go to the ‘Formulas’ tab in your Excel worksheet. Then, choose the ‘Name Manager’ option from the ‘Defined Names’ section.
3. The Name Manager box will open. You need to click the named range you want to edit. Now, go to the ‘Refers to’ box at the bottom and update the cell references. You can also update the cell references manually by selecting all the cells containing the entries for your drop down list.
4. Then, click the checkmark located to the left of the field that contains cell references under the Refers to box. It will save the changes you have made. Click on the ‘Close’ button.

Along with learning how to edit drop down list in Excel in case of the drop down list based on a named range, you should also learn how to find out the name of the range. You can check the ‘Name Box’ to look for the name of the range of cells.

How to Edit a Drop Down List Based on a Range of Cells?

You should follow the steps listed below in order to edit a drop down list that has been created based on a range of cells.

1. Type the new item at the end of the list to add an item. If you want to remove an item, select it and press the Delete key. If the item you want to delete is in the middle of the list, you should shift the remaining items up, or else your drop down list will have a blank space.
2. Select the cell that has the drop down list in your Excel worksheet.
3. Now, go to the ‘Data’ tab in the worksheet. Select the ‘Data Validation’ option under the ‘Data Tools’ section.
4. The Data Validation box will open. Go to the ‘Settings’ tab in this box. Go to the ‘Source’ box and then go to the worksheet that has the entries for your drop down list.
5. Select all the cells that contain the entries. The range of the cells will change accordingly in the Source box.
6. A box titled ‘Apply these changes to all other cells with the same settings’ is located at the bottom of the Settings tab. Check this box if you want to update all the cells that have the same drop-down list applied.

How to Edit a Drop Down List Based on the Data Entered Manually

We are sure that you would have understood how to edit drop down list in Excel in three different scenarios, namely, a drop down list created based on an Excel table, a drop down list based on a named range, and a drop down list based on a range of cells. Now, it’s time to see how to edit a drop down list that has been created based on the data entered manually.

1. Open the worksheet that has the drop down list. Now, select the cell that has the drop down list.
2. Go to the ‘Data’ tab and then choose the ‘Data Validation’ option under the ‘Data Tools’ section.
3. Once the Data Validation box opens, go to the Settings tab. You will see a box titled ‘Source’ in the Settings tab.
4.  Change the items in your list as required. Enter the name of the new items you want to add. Remove the names of the items you want to remove. Keep a comma between each item without keeping any spaces in between. An example is “Apples, Oranges, Mangoes”.
5. Check the box titled ‘‘Apply these changes to all other cells with the same settings’ located at the bottom in the Settings tab.

If you have created your drop down list properly, you will be able to edit it without any problem. If you are new to the subject of using drop down lists, you should first learn how to create them using different methods. You can hide or protect the worksheet that contains the list of entries based on which you have created your drop down list if you don’t want others to edit your drop down list.

Conclusion

Microsoft Excel is a useful program that helps you to accomplish various tasks. Using the drop down lists in Excel can enable you to make questionnaires, prepare test papers, and do much more. We hope you would have liked reading this blog post on how to edit drop down list in Excel.