How to Change a Drop-Down List in Excel

The fastest way to change a drop-down list

To change an existing drop-down list in Excel, select the cell or range containing the drop-down, go to the Data tab, click Data Validation, and edit the list in the dialog box that opens. You can change the source list, add or remove items, or switch to a different validation type entirely. The change applies when ready to that cell or range.

If you want to change the list for multiple cells at once, select all of them before opening Data Validation. If the drop-down pulls from a named range or a cell range elsewhere in the workbook, you can edit the source cells directly and the drop-down will update automatically.

Key Takeaways

Select the cell with the drop-down, open the Data tab, and click Data Validation to edit the list items or source.
You can type a new list directly into the Source field, or point to a range of cells that contains your list.
Changes to a named range or source cells update the drop-down when ready without reopening Data Validation.
If you delete the source range that a drop-down points to, the drop-down will show an error until you fix the source.

Editing the list directly in Data Validation

Open the workbook and click the cell containing the drop-down you want to change. Go to the Data tab in the ribbon and select Data Validation. The Data Validation dialog opens with the current settings visible.

In the Source field, you will see either a list of items separated by commas or a cell range reference (like $A$1:$A$10). If the list is typed directly, you can edit it here: add new items, remove old ones, or reorder them. Separate each item with a comma and a space. Click OK when you are done, and the drop-down will reflect your changes when ready.

This method works best for short, static lists that do not change often. If your list is long or changes frequently, using a source range is more efficient.

Changing the source range

If your drop-down points to a range of cells (you will see something like $Sheet1.$A$1:$A$20 in the Source field), you have two options: edit the range reference itself, or edit the cells the range points to.

To change which cells the drop-down reads from, open Data Validation and modify the range in the Source field. For example, if it currently points to $A$1:$A$10 and you want it to include more items, change it to $A$1:$A$15. Click OK to save the change.

To change the actual items in the list without changing the range, straightforward edit the cells themselves. If your drop-down points to cells A1 through A10, click on cell A5 and type a new value. The drop-down will show the new value the next time you click it. This is the easiest way to maintain a drop-down list over time.

Using a named range for easier updates

A named range is a label you assign to a group of cells. Instead of typing $A$1:$A$10 in the Source field, you can type the name you gave those cells. This makes it easier to update the list later because you only have to edit the cells, not the Data Validation settings.

To create a named range, select the cells containing your list items. Go to the Formulas tab and click Define Name. Type a name (like "CountryList" or "ProductOptions") and click OK. Now, when you set up a drop-down, use that name in the Source field instead of a cell range.

If you later need to expand the list, you can edit the named range to include more cells. Go to Formulas > Name Manager, select the name, and click Edit. Change the range to include your new items. Every drop-down using that named range will update automatically.

Changing the drop-down type or adding restrictions

The Data Validation dialog also controls what type of input the cell accepts. By default, a drop-down is set to List. You can change this to Whole Number, Decimal, Date, Time, or Text Length if you want to restrict input to a specific data type instead of showing a list.

To change the validation type, open Data Validation, click the Allow dropdown, and select a different option. For example, if you want a cell to accept only dates between January 1, 2024 and December 31, 2024, select Date, set the condition to between, and enter your start and end dates. Click OK to explore the restriction.

You can also add an error message that appears when someone enters invalid data. In the Data Validation dialog, click the Error Alert tab, check the Show error alert when invalid data is entered box, and type your message. This helps users understand what input the cell expects.

Removing or clearing a drop-down

To remove a drop-down entirely, select the cell or range, open the Data tab, click Data Validation, and click the Clear All button. The cell will no longer have a drop-down arrow, and users can type any value.

If you want to keep the drop-down but clear the current list and start over, open Data Validation, delete everything in the Source field, and click OK. The drop-down will disappear until you add a new source.

Troubleshooting common drop-down problems

If a drop-down shows an error or stops working, the most common cause is a broken source range. If you deleted the cells the drop-down was pointing to, or if someone moved them to a different sheet, the drop-down will display an error. Open Data Validation and check the Source field. If the range no longer exists, update it to point to the correct cells or type a new list.

If a drop-down is not showing all the items you expect, check whether the source range includes empty cells. A drop-down will display blank entries if the range contains them. Edit the source range to exclude empty rows, or delete the blank cells from your list.

If you copied a cell with a drop-down to another location and the drop-down is not working, the source range may have shifted. When you copy a drop-down, Excel adjusts the cell references unless you used absolute references (with dollar signs). Open Data Validation in the copied cell and verify the Source field points to the correct range.

Frequently Asked Questions

Can I change a drop-down list without opening Data Validation?

Yes, if the drop-down points to a range of cells. straightforward edit the cells in that range directly, and the drop-down will show the updated values. You only need to open Data Validation if you want to change the range itself, add restrictions, or edit a list that was typed directly into the Source field.

What happens if I delete a cell that a drop-down points to?

The drop-down will show an error. Open Data Validation and update the Source field to point to a different range, or type a new list. If you accidentally deleted the wrong cells, undo the deletion with Ctrl+Z.

Can I use a drop-down list from a different sheet?

Yes. In the Data Validation Source field, type the sheet name followed by the range, like Sheet2.$A$1:$A$10. If the sheet name has spaces, put it in single quotes: 'Sheet 2'.$A$1:$A$10. The drop-down will pull items from that sheet.

How do I change a drop-down to allow both a list and typed entries?

Data Validation does not support this directly. A drop-down restricts input to the list items only. If you want users to choose from a list or type something new, you will need to remove the drop-down and allow free text entry instead.

Can I copy a drop-down to multiple cells at once?

Yes. Select the cell with the drop-down, copy it with Ctrl+C, select the range where you want the drop-down to appear, and paste with Ctrl+V. Excel will copy the drop-down and adjust the source range references if needed. If you want all cells to use the same source range, use absolute references (with dollar signs) in the original drop-down.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.