- WindowsTips.net - Windows Tips and Tricks with Geek

Thursday, February 24, 2022


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.

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.

Add an item to a table in Excel

When you select the drop-down list, you’ll see the additional item in the selection.

Table item added to drop-down list

To remove an item, right-click and choose Delete > Table Rows. This removes the item from the table and the list.

Select Delete, Table Rows

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.

Deleted table item from list

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.

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.

Data Validation on the Data tab in Excel

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.

Data Validation with updated cell references

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.

Name Manager on the Formulas tab

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.”

Updated named range

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.

Note: If you use a named range, you may want to update the cell references as described above.

To remove a list item in the cell range, right-click and choose “Delete.”

Delete in shortcut menu

When prompted, select “Shift Cells Up” and click “OK.”

Shift Cells Up selected

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.

Blank item in drop-down list

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.

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.

Data Validation on the Data tab in Excel

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.

Updated Data Validation list

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.

No comments:

Post a Comment