Last Updated on
Microsoft Excel is an incredibly versatile program that can be used to create anything from charts to surveys.
One of the things that you can do in this program is create interactive spreadsheets, including features such as dynamic inputs or cells with drop-down lists.
Adding a drop-down list to a cell is a great way to make your worksheet more interactive when creating things like forms or surveys.
However, this feature is only useful if you can edit the options that a drop-down list contains.
In this guide we will show you a few different methods for editing drop down lists in Excel.
Editing A List Based Of A Table
If your list is based on an Excel table, then it is very simple to add new options or remove existing ones.
Step
Select The Cell Containing Your Drop-down List
Select the cell containing your drop-down list, and navigate to the ‘Data’ tab on the main ribbon, and then look for the ‘Data Validation’ section.
Here, click on the option for ‘Data Validation’ to open up a dialogue box.
Step
Add New Entries To The List/Remove Entries From The Source Box
In this box, you will be able to add new entries to the list, or remove entries from the source box. This is done by adding or removing cells to only include that you want in your list.
Step
Check The Box Marked ‘Apply These Changes To All Other Cells With the same settings’
If you want the changes you make to have an effect on all the cells that contribute to the same drop-down list, then check the box marked ‘Apply these changes to all other cells with the same settings’.
Step
You Can Follow This Process For Altering Lists Based On A Certain Range Of Cells As Well
You can follow this process for altering lists based on a certain range of cells as well.
It is important before making changes to your list that you alter the data the list is based on.
If the data for your list is on another worksheet, then you will need to navigate to this sheet and delete, or add, any entries that you want to.
Editing A List Based Off A Named Range
If you want to change a list that is based on a named range, then you will have to use a slightly different method to the one described above.
Step
You Need To Make Your Adjustments To The Cells Containing The Data For Your List First
As with the last method, you need to make your adjustments to the cells containing the data for your list first. This means deleting any entries you want to remove, or inserting new entries.
Step
You Can Edit The List To Match The Adjusted Data
After this, you can edit the list to match the adjusted data. To do this, open the ‘Formulas’ tab on the main ribbon and navigate to the ‘Name Manager’.
Step
Click On The Named Range That Your List Is Based Off
Once the dialogue box for the ‘Name Manager’ opens, click on the named range that your list is based off. Click in the ‘Refers To’ box and type in the amended range of cells for your list.
Close the dialogue box and click ‘Yes’ when prompted to save your changes.
Editing A List Containing Items You Enter Manually
If you want to manually add or remove items to a drop-down list, then you will need to get to the ‘Data Validation’ window we saw in our first method.
Step
Open The ‘Data’ Tab In The Main Ribbon
To reach this window, open the ‘Data’ tab in the main ribbon, and click on ‘Data Validation’ option after highlighting the cell containing your list.
Step
Enter The Items You Want To Add To The List
In the ‘Source’ box, enter the items you want to add to the list, making sure that each item is separated by a comma and nothing else.
Click the ‘Ok’ button and press ‘Yes’ when asked if you want to save your changes.
Conclusion
Using the instructions above, you should have no problem editing drop-down lists in Excel.
If you found this guide helpful, then consider sharing it and checking out some of our other useful guides for working with Microsoft Office.
