The fastest way to edit a drop-down list

To change what options appear in an Excel drop-down list, go to the Data tab on the ribbon, click Data Validation, and edit the list in the dialog box that opens. If you created the list by typing options directly into the validation box, you can edit them there. If you pointed to a range of cells, you can change which cells the list pulls from, or edit the values in those cells themselves.

The method you use depends on how the original list was built. A list created from typed values behaves differently from one built from a cell range, and knowing which you have saves time.

Key Takeaways

  • Open the cell with the drop-down, go to Data > Data Validation, and the dialog shows exactly how the list was created.
  • If the list was typed directly into the validation box, edit the text there; if it points to a range, edit the cells in that range or change the range address.
  • You can add new options, remove old ones, or reorder them without deleting and recreating the drop-down.
  • Changes to a cell range update automatically in all drop-downs that use it, but changes typed directly only affect that one cell.

Finding the drop-down and opening Data Validation

Click the cell that contains the drop-down list you want to change. The cell will show a small arrow on the right side when selected. Go to the Data tab on the ribbon at the top of the screen, then click Data Validation (in Excel for Mac, this is under the Data menu).

A dialog box opens with three tabs: Settings, Input Message, and Error Alert. The Settings tab is where the list itself lives. Look at the Allow dropdown at the top—it should say List. If it says something else, you are looking at a different kind of validation rule, not a straightforward list.

Editing a list built from typed values

If the Source field in the Settings tab contains text with commas or line breaks (you will see the actual options listed there), the drop-down was created by typing the values directly. To change it, click in the Source field and edit the text.

Each option should be separated by a comma and a space, or by pressing Enter to put each on its own line. If you want to add "New Option" to a list that currently reads "Option A, Option B, Option C", change it to "Option A, Option B, Option C, New Option". To remove an option, delete its text and the comma or line break next to it. When you are done, click OK.

This method works well for short, stable lists that do not change often. The downside is that if you use the same list in multiple cells, you have to edit each one separately.

Editing a list built from a cell range

If the Source field shows a cell range like $A$1:$A$10 or Sheet2!$B$2:$B$20, the drop-down pulls its options from those cells. You have two choices: edit the values in those cells, or change which range the drop-down points to.

To edit the values: Click OK to close the dialog, then go to the cells in the range and change the text directly. Any drop-down that uses this range will update automatically. This is the easiest route if you just need to fix a typo or update one value.

To change the range itself: Stay in the Data Validation dialog and click in the Source field. Clear the current range and type or select the new one. For example, if the current source is $A$1:$A$5 but you want to include more options in cells A1 through A10, change it to $A$1:$A$10. Click OK when done.

Adding or removing options without recreating the list

If your list is built from a cell range and you want to add new options, straightforward add them to the end of that range. If the range is A1:A10 and you want to add a new option, type it in cell A11, then edit the Data Validation source to $A$1:$A$11. The drop-down will now include the new option.

To remove an option, delete the text from its cell. The drop-down will no longer show that option, but the cell itself remains in the range. If you want to clean up empty cells, you can edit the range to exclude them—for example, changing $A$1:$A$10 to $A$1:$A$8 if cells A9 and A10 are now blank.

Reordering options is straightforward: just rearrange the values in the cells. If your list is in A1:A5 and reads "Red, Blue, Green, Yellow, Purple", you can move "Yellow" to the top by cutting and pasting, and the drop-down will reflect the new order when ready.

Copying a drop-down to other cells

Once you have edited a drop-down the way you want it, you can copy it to other cells. Select the cell with the drop-down, copy it (Ctrl+C or Cmd+C), then select the range where you want the same list and paste (Ctrl+V or Cmd+V). Excel will copy the validation rule along with the cell contents.

If the original drop-down uses a cell range as its source, the range reference will adjust automatically when you paste to a different location—unless you used absolute references (dollar signs). If you want the copied drop-downs to all use the exact same range, make sure the source uses absolute references like $A$1:$A$10 rather than relative ones.

Frequently Asked Questions

Can I change a drop-down list without deleting it and starting over?

Yes. Click the cell, go to Data > Data Validation, and edit the Source field. You can add options, remove them, reorder them, or change the range the list points to—all without recreating the validation rule.

What if I edit the cells that the drop-down uses but the list does not update?

Make sure the cells you edited are actually inside the range listed in the Data Validation Source field. If you added a new value outside that range, the drop-down will not see it. Edit the Source field to include the new cells, or move the new value into the existing range.

Can I use the same drop-down list in multiple cells?

Yes, especially if you build it from a cell range. Create the list in one set of cells, then use Data Validation to point multiple cells to that same range. Any change to the source cells updates all the drop-downs at once. If you typed the list directly into the validation box, you have to edit each cell separately.

How do I change the order of options in a drop-down?

If the list comes from a cell range, rearrange the values in those cells and the drop-down order changes automatically. If the list was typed directly into the validation box, edit the Source field and move the text around—separate each option with a comma or line break.

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

The drop-down will show a blank or error. To fix it, edit the Data Validation source to point to a different range, or rebuild the list by typing values directly into the Source field.