Wondering how to edit a drop-down list in Excel? We have got you covered.
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, some people can’t seem to figure out how to edit their list after creating one.
This is where we come in. To help you out, we’re going to talk about how to edit a drop-down list in Excel.
Editing a drop-down list in Excel
Scenario on hand: We have a dataset with student marks and names.
What we want to accomplish: Explore how to edit drop-down lists in Excel under five scenarios:
- Editing a drop-down list based on a table
- Editing a drop-down list based on a named range
- Editing a drop-down list based on manually entered items
- Editing the color of a drop-down list
- Sorting a drop-down list
Scenario 1 – Editing a drop-down list based on a table
The first scenario we explore for editing drop-down lists is with a list based on a table.
In our demonstration, we have three student names and three assessment types. Both these columns have drop-down menus that are based on a table on the right.
We wish to add two other assessment types, namely “Test 3” and “Final project.”
We add these two names to the table on the right, but it doesn’t affect the drop-down menu.
To edit the drop-down list, here are the steps we need to follow:
Step 1: Select the cell containing your drop-down list
The first step is to select the cell containing your drop-down list and navigate to the ‘Data’ tab on the main ribbon.
In this menu, look for the ‘Data Tools’ section.
Here, click on the option for ‘Data Validation’ to open up a dialogue box.
Step 2: Add new entries to the list
In this dialog box, you will be able to add new entries to the list or remove entries from the source box.
In this scenario, our list is based on a table. We have to change the table array to include all we want in the drop-down menu.
Use the arrow button in front of the “Source” box to select the entire table range.
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.’
Here’s a look at how the table range is selected:
Alternatively, you can also manually edit the table array’s range.
Scenario 2 – Editing a drop-down list based on a named range
Named ranges are a great way to simplify work in Excel, especially if there are a lot of options
If you want to change a list that is based on a named range, then you will have to use a slightly different method than the one described above.
Step 1: Edit the data
Named ranges are also based on tables. Instead of inputting a table range like A2:A33, we dedicate a name to the range so it is easier.
As with the last method, you need to make adjustments to the cells containing the data for your list first. This means deleting any entries you want to remove or inserting new entries.
In our case, the first three cells make up the named range “Assessments.” We have added the two new assessment types beneath the named range.
Step 2: Change the named list
To change the named list, open the ‘Formulas’ tab on the main ribbon and navigate to the ‘Name Manager.’
Once the dialogue box for the ‘Name Manager’ opens, click on the named range on which your list is based. 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.
You will now see the two new items in the drop-down menu.
Scenario 3 – Editing a drop-down list based on manually entered items
If your drop-down list has options listed manually, then you will need to get to the ‘Data Validation’ window we saw in our first method.
To reach this window, open the ‘Data’ tab in the main ribbon and click on the ‘Data Validation’ option after highlighting the cell containing your list.
In the ‘Source’ box, enter the items you want to add to the list, ensuring that each item is separated by a comma and nothing else.
Make sure to check the option to apply the changes to all other cells with the same settings.
Click the ‘OK’ button and press ‘Yes’ when asked if you want to save your changes.
Scenario 4 – Editing the color of a drop-down list
Step 1: Highlight cell rules
To edit the color of each option in the drop-down list, go to “Styles” under the Home tab and select “Conditional Formatting.”
Create cell highlight rules by selecting the entire column, then head to “Highlight cells rules.” Next, select “Text that contains…”
Step 2: Define the rules
We define the highlighting rules for each option like this:
Here’s how our table looks with the cell highlight rules defined:
Scenario 5 – Sorting a drop-down list
To sort a drop-down list, you must change the data’s order at the source.
If your drop-down list has been created by comma-separated entries, you can manually change the order of the list under the “Data Validation” option.
Step 1: Using the “Sort and Filter” option
If you have a table or a named range with the drop-down options, sort it using the “Sort and Filter” option:
Step 2: Define a custom list
Using this option, you can either sort alphabetically or define a custom list like this:
Alternatively, you can manually change the order of the table list. This works if you have a short list of options.
Wrapping up
Using the instructions above, you should have no problem editing drop-down lists in Excel. As you can see for yourself, the process is pretty simple and straightforward, and it will hardly take you a minute to edit a list.
If you want to learn more about Excel, give these guides a read:
