Edit a Drop-Down List by Opening Data Validation

To change what options appear in an Excel drop-down list, you need to open the Data Validation dialog for that cell or range. Select the cell or cells that contain the drop-down, then go to the Data tab on the ribbon and click Data Validation (in older Excel versions, this may be called Validity). The dialog box that opens shows you the current source of the list — whether it pulls from a range of cells, a typed list, or another worksheet.

Once the Data Validation dialog is open, you are looking at the Settings tab. The Allow field should already be set to List if this is a drop-down. Below that, the Source field shows where the options come from. This is where you make your changes.

Key Takeaways

  • Open Data Validation from the Data tab, select the cell with the drop-down, and look at the Source field to see where the current options live.
  • If options are typed directly into the Source field separated by commas, edit them there and click OK to update the list when ready.
  • If options come from a cell range (like A1:A10), you can add new rows to that range or change which cells the drop-down points to.
  • After you change the source, existing cells keep their current value even if that value is no longer in the new list — you may need to clear old entries manually.
  • Use named ranges to make drop-down sources easier to manage, especially if you have many drop-downs or the list changes often.

Change a Typed List of Options

If the options were entered directly into the Source field as text, you will see them separated by commas or line breaks. For example: Red, Blue, Green, Yellow. To edit this list, click in the Source field and change the text directly. You can add new options, remove old ones, or rearrange the order.

After you finish editing, click OK. The drop-down in that cell (or all cells in the range) will now show your new options. Any cells that already had a value will keep that value, even if it is no longer on the list — this does not cause an error, but it can look odd if someone opens the drop-down and does not see what is currently selected.

Change a Drop-Down That Points to a Cell Range

Many drop-downs pull their options from a range of cells elsewhere on the sheet or on another worksheet. The Source field will show something like $A$1:$A$10 or Sheet2!$B$2:$B$20. To change the options, you have two choices: edit the cells in that range, or change which range the drop-down points to.

To edit the options themselves, go to the cells in the range and change the values there. The drop-down updates automatically — you do not need to open Data Validation again. To point the drop-down to a different range entirely, open Data Validation again, click in the Source field, and type the new range address or click and drag to select the new cells on the sheet.

If you want to add more options to the list without changing the range address, you can expand the range. For example, if the Source is $A$1:$A$10 and you want to add three more options, change it to $A$1:$A$13 and enter the new values in cells A11, A12, and A13.

Use a Named Range to Manage Drop-Downs More Easily

If you have many drop-downs or the list changes often, a named range makes updates much simpler. Instead of pointing each drop-down to a cell address like $A$1:$A$10, you create a name for that range — for example, ColorOptions — and point all your drop-downs to that name.

To create a named range, select the cells that hold your options, go to the Formulas tab (or Sheet tab in some versions), and click Define Name. Type a name with no spaces, then click OK. Now open Data Validation on your drop-down cells and enter that name in the Source field instead of a cell address. Later, if you need to add or remove options, you can edit the named range definition, and every drop-down using that name updates automatically.

Clear Old Values That Are No Longer on the List

When you change a drop-down's options, cells that already contain a value keep that value even if it is no longer in the new list. For example, if a cell shows Purple but you removed Purple from the list, the cell still displays Purple — it just will not appear as an option if someone clicks the drop-down arrow.

To clean this up, you have two options. You can manually select each cell with an outdated value and delete it, or you can use Find & Replace to find and remove old values across the sheet at once. Go to Home > Find & Replace, search for the old value, and replace it with nothing (leave the Replace field blank). This removes the value from all cells at once, though it will also remove that text from anywhere else on the sheet, so be careful with common words.

Prevent Invalid Entries After You Change the List

By default, Excel allows someone to type any value into a cell with a drop-down, even if it is not on the list. If you want to lock the cell so only listed options are allowed, open Data Validation again and look at the Settings tab. Find the option that says In-cell dropdown and make sure it is checked. Then look for Show error alert if invalid data is entered and check that box too.

When you check the error alert box, you can choose what kind of message appears. Set Style to Stop to prevent invalid entries entirely, or Warning to allow them but show a message. Type a title and message that explain what values are allowed. Now if someone tries to type something not on the list, they will see your message and have to either pick from the drop-down or cancel.

Frequently Asked Questions

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

Yes. Select all the cells that contain the drop-down (they must all have the same source), open Data Validation, and make your changes. Click OK and the update applies to every selected cell. If the cells have different sources, you must change them one at a time or in separate groups.

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

The drop-down will show an error or become empty. To fix it, open Data Validation and point the drop-down to a new range, or recreate the cells it was pointing to. This is why named ranges are helpful — if you delete the original cells but keep the named range definition, the drop-down keeps working.

Can I sort the options in a drop-down list?

If the options come from a cell range, sort those cells the way you want them, and the drop-down will reflect the new order. If the options are typed directly into the Source field, you must retype them in the order you want. Excel does not have a built-in sort function for typed lists.

How do I change a drop-down to allow multiple selections?

Excel's standard Data Validation does not support multiple selections from one drop-down. You would need to either create separate drop-downs for each selection, or use a more advanced method like a helper column with formulas or a macro. For most situations, multiple single-selection drop-downs work better.