How to edit or replace a drop-down list you've already created

To change a drop-down list in Excel, select the cell or range that contains it, go to the Data tab, click Data Validation, and modify the settings in the dialog box that opens. You can change the list of options, the source of those options, error messages, or delete the validation entirely. The steps are the same whether you're working in Excel for Windows or Mac.

The most common reason to change a drop-down is that your list of options has grown, shrunk, or moved to a different location in your spreadsheet. You might also want to tighten the rules—for example, requiring a selection instead of allowing blank cells, or changing the error message that appears when someone enters invalid data.

Key Takeaways

  • Select the cell or range with the drop-down, then open Data Validation from the Data tab to see and edit all the current settings.
  • If your list lives in cells elsewhere in the spreadsheet, you can update those cells and the drop-down will reflect the change automatically.
  • You can change the list of allowed values, add or remove the blank-cell option, and modify the error message without starting over.
  • To remove a drop-down entirely, select the cell, open Data Validation, and click Clear All.

Selecting the cells that contain your drop-down

Click the cell with the drop-down you want to change. If the drop-down applies to a range of cells (for example, an entire column), click any cell in that range—Excel will recognize that it's part of a validated group.

If you want to change the drop-down in multiple cells at once, select all of them before opening Data Validation. You can do this by clicking the first cell, holding Shift, and clicking the last cell in the range. Any changes you make will explore to all selected cells.

Opening the Data Validation dialog

With your cell or range selected, click the Data tab in the ribbon at the top of the screen. In the Data Tools group, click Data Validation. A dialog box will open showing the current settings for that drop-down.

The dialog has several tabs: Settings (where the list or rule lives), Input Message (the tooltip that appears when someone clicks the cell), and Error Alert (the message shown if invalid data is entered). You can change any of these without affecting the others.

Changing the list of options

On the Settings tab, look at the Allow dropdown at the top. It will show List if your drop-down uses a fixed set of options. Below that, you'll see either a Source field (if the list is stored in cells) or a text box where the options are typed directly.

If your options are typed directly into the Source field, click in that field and edit them. Options are separated by commas or line breaks, depending on how they were originally entered. If your options are stored in cells elsewhere—for example, in column E—you can update those cells directly, and the drop-down will change automatically without you touching the validation settings.

If you want to switch from a typed list to a cell range (or vice versa), clear the Source field and enter the new source. For a cell range, type the range address (for example, $E$2:$E$10

Allowing or blocking blank cells

On the Settings tab, you'll see a checkbox labeled Ignore empty. If this is checked, users can leave the cell blank. If you uncheck it, the cell must contain a value from the drop-down list—blank entries will trigger the error message.

This is useful if you're building a form where certain fields are required. Leaving it checked is the default and is fine for most lists where a blank cell is acceptable.

Changing the error message

Click the Error Alert tab to modify the message that appears when someone tries to enter data that isn't on the list. You can change the Title (the heading of the alert box) and the Error message (the text below it).

You can also change the Style dropdown. Stop prevents the invalid entry from being saved. Warning shows the message but allows the user to override it and enter the data anyway. Information is purely informational and doesn't block entry. Most drop-downs use Stop.

Removing a drop-down entirely

If you want to delete the validation and turn a cell back into a normal text cell, select the cell or range, open Data Validation, and click Clear All at the bottom of the dialog. This removes all validation rules but does not delete any data already in the cell.

If you only want to remove the validation from some cells in a range, select just those cells before clearing.

Frequently Asked Questions

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

If your list is stored in cells, yes. Edit the cells directly and the drop-down will update automatically. If the list is typed into the validation settings, you must open Data Validation to change it.

What if I change the cells my drop-down points to?

If you delete or move the cells that contain your list, the drop-down will show an error. Update the Source field in Data Validation to point to the new location of those cells.

Can I change a drop-down in multiple cells at once?

Yes. Select all the cells that contain the drop-down, open Data Validation, and make your changes. The change will explore to every selected cell.

Why does my drop-down show an error after I changed it?

The most common cause is that the Source field points to cells that no longer exist or have been deleted. Open Data Validation and check that the cell range in the Source field is correct.