If you’re like most people, you probably use Microsoft Excel to manage your data. But if you want to add a drop-down list to your spreadsheet, there’s a few things you need to know. First, the drop-down list needs to be created as a table. Then, the cells in the table need to be filled with data. Finally, the list needs to be dragged and dropped into the spreadsheet. Here’s how it works: first, create a table in Microsoft Excel. Then, fill in all of the cells in the table with data. Finally, drag and drop the table into your spreadsheet.
Drop-down lists in Excel make for a simplified way to enter data, but sometimes you may need to edit that list. You can add or delete items from your drop-down list regardless of how you created it.
You may have used a table, named cell range, or manual setup for your drop-down list. We’ll show you how to edit your list accordingly.
Edit a Drop-Down List From a Table
The nice thing about using a table in Excel for your drop-down list is that the list updates automatically when you make changes to your table.
RELATED: How to Create and Use a Table in Microsoft Excel
To add an item, go to the last row in the column, press Enter or Return, enter the new list item, and press Enter or Return again.
When you select the drop-down list, you’ll see the additional item in the selection.
To remove an item, right-click and choose Delete > Table Rows. This removes the item from the table and the list.
If you simply delete the text in the cell, this will leave a space in your drop-down list. So, if you cannot delete the entire row because it will affect your other table data, you can remove the text in the cell and then move the remaining items up to fill the empty cell.
Edit a Drop-Down List From a Cell Range
You might use a cell range or a named range for the items in your drop-down list, which is a handy way to go. Here’s how to add and remove items from your list.
RELATED: How to Assign a Name to a Range of Cells in Excel
Add an Item to a Cell Range
To add a list item, simply enter it below the existing items. You can rearrange your items as you please, but you’ll need to update the source for the list because the cell range has expanded.
Select the cell containing the drop-down list, go to the Data tab, and select “Data Validation” in the Data Tools section of the ribbon.
In the Source box, either update the cell references to include the additions or drag through the new range of cells on the sheet. Click “OK” to apply the change.
Optionally, check the box at the bottom if you use the list in more than one place and want to update it throughout.
Add an Item to a Named Range
If you use a named range for your list items, you can use the above method to add an item to the list. However, this will not update the named range to include the additional item. Instead, update the named range.
Go to the Formulas tab and select “Name Manager” in the Defined Names section of the ribbon.
When the Name Manager opens, select the named range and update the cell references in the Refers To box at the bottom. You can manually adjust the cell references or simply drag through them on your sheet. Click the checkmark to the left of that field to save your changes and hit “Close.”
Your drop-down list automatically updates to include the new list item.
Remove an Item From a Range
Whether you use a named range for your drop-down list or a cell range without a name, removing an item from the list works the same way.
To remove a list item in the cell range, right-click and choose “Delete.”
When prompted, select “Shift Cells Up” and click “OK.”
If you simply select the cell and delete the text in it, you’ll see a blank space in your list as shown below. The above method eliminates that space.
Edit a Drop-Down List Manually
If you entered your list items manually in the Data Validation box rather than referencing a table or cell range, you can simply add or remove list items in that same spot.
RELATED: How To Restrict Data Input In Excel With Data Validation
Select the cell containing the drop-down list, go to the Data tab, and choose “Data Validation” in the Data Tools section of the ribbon.
In the Source box, add new list items to the list or remove the ones you no longer want. Click “OK” and your list will be updated.
Just because you add a drop-down list for data entry in Excel, doesn’t mean you can’t make changes to it when needed. For another option, you can set up custom lists in Excel to use anytime.
