The fastest way to change what appears in a drop-down list

To change a drop-down list in Excel, you edit the source data that the list pulls from. If your drop-down uses a named range or a direct cell reference, you modify those cells. If it uses a table, you add or remove rows from that table. The drop-down updates automatically once you save the change.

The method depends on how the drop-down was originally set up. Most drop-downs in Excel point to either a range of cells you can see and edit, or a named range that sits somewhere on your sheet. Either way, you are not editing the drop-down itself—you are editing what it displays.

Key Takeaways

  • Drop-down lists in Excel pull their options from a source range of cells, which you can edit directly to change what appears in the list.
  • To find the source range, select the cell with the drop-down, go to Data > Validity (or Data Validation on Windows), and look at the Source field.
  • If the source is a named range, you can edit it by going to Formulas > Name Manager and modifying the cells it refers to.
  • Adding or removing rows from the source range automatically updates the drop-down without needing to re-create it.
  • If you want to change the drop-down's location or delete it entirely, you can do that through Data > Validity without affecting the source data.

Finding where your drop-down list gets its data

Click on any cell that contains a drop-down arrow. On Windows, go to the Data tab and select Validity (called Data Validation on some versions). On Mac, go to Data > Validity. A dialog box opens showing the Source field—this tells you where the list is pulling from.

The source will be one of three things: a range like $A$1:$A$10, a named range like FruitList, or a formula. Most commonly it is a range. Write down or copy exactly what you see in the Source field—you will need it to find the actual cells.

If the source shows a range with a sheet name like Sheet2.$B$2:$B$8, that means the list is pulling from Sheet2. Navigate to that sheet to find and edit those cells. If it shows a named range, you will need to open the Name Manager to see which cells it refers to.

Editing a drop-down that uses a cell range

If your Source field shows something like $A$1:$A$10, navigate to that range on your sheet. You will see the cells the drop-down is reading from. You can now add, remove, or change the text in any of those cells, and the drop-down will update when ready.

To add a new option, type it into the next empty cell in that range. If your range was $A$1:$A$10 and cell A10 was the last entry, type the new option in A11, then go back to the drop-down cell, open Data > Validity, and change the Source to $A$1:$A$11. Save the file.

To remove an option, delete the text from that cell or delete the entire row. The drop-down will no longer show that option. If you delete a row in the middle of your range, the drop-down adjusts automatically as long as you do not change the range reference itself.

Changing a drop-down that uses a named range

If the Source field shows a name like CountryList instead of cell references, the drop-down is using a named range. To edit it, go to the Formulas tab (or Sheet menu on Mac) and select Name Manager. Find the name that matches your Source field.

Click on that name and look at the Refers To field at the bottom of the dialog. This shows you which cells the named range points to—for example, Sheet1.$C$2:$C$15. Navigate to those cells and edit them the same way you would edit a regular range: add, remove, or change text as needed.

If you want to expand the named range to include more cells, click Edit in the Name Manager, change the Refers To field to the new range, and click OK. For example, if it currently refers to $C$2:$C$15 and you want to add more options, change it to $C$2:$C$20. The drop-down will now include any new entries you add to cells C16 through C20.

Using a table as your drop-down source

Some drop-downs pull from an Excel table instead of a plain range. If your Source field shows something like Table1[Options], you are using a table column. To edit it, find that table on your sheet and add or remove rows as needed. The drop-down updates automatically because tables expand and contract dynamically.

To add a new option, click the last row of the table and press Tab to create a new row, then type the new entry. To remove an option, right-click the row number on the left side of the table and select Delete. The drop-down will reflect the change without any additional steps.

Deleting or moving a drop-down without losing the source data

If you want to remove the drop-down from a cell but keep the data it displays, select the cell, go to Data > Validity, and click Clear All. The cell keeps its current value but loses the drop-down arrow. The source data remains untouched.

To move a drop-down to a different cell, select the cell with the drop-down, copy it (Ctrl+C or Cmd+C), click the new cell, and paste. The drop-down moves and still points to the same source. If you want to copy the drop-down to multiple cells at once, select the original cell, copy it, select the range where you want it, and paste.

Troubleshooting when your changes do not show up

If you edited the source cells but the drop-down still shows the old options, save your file and close it, then reopen it. Excel sometimes caches the list in memory. Closing and reopening forces it to read the source again.

If the drop-down shows an error or blank options after you edited the source, check that you did not accidentally delete the entire range or leave blank rows at the top. The drop-down will include blank cells as options. Also verify that the Source field in Data > Validity still points to the correct range—if you moved or deleted rows, the reference may no longer be valid.

If you expanded the range but the drop-down still only shows the old number of options, you may have forgotten to update the Source field. Go back to Data > Validity and manually change the range to include the new cells.

Frequently Asked Questions

Can I change a drop-down list without editing the source cells?

No. The drop-down always reads from its source—either a range, a named range, or a table. To change what appears in the drop-down, you must change what is in that source. There is no way to override it without editing the source itself.

What happens if I delete the source cells after creating the drop-down?

The drop-down will show an error or become blank. If you deleted the cells by accident, undo the deletion when ready. If you intentionally deleted them, you will need to recreate the drop-down with a new source range. The old drop-down cannot function without its source data.

Can I have the same drop-down on multiple cells?

Yes. Copy the cell with the drop-down, select the range where you want it, and paste. All cells will point to the same source and update together when you change the source data. This is the fastest way to add the same drop-down to many cells at once.

How do I add a drop-down option without editing the source range directly?

You cannot. The drop-down displays only what is in its source. If you want to add an option, you must add it to the source cells. If the source is a named range, you can expand the range in the Name Manager to include more cells, then type the new option into one of those cells.

What if my source range is on a different sheet?

Navigate to that sheet and edit the cells there the same way you would on any other sheet. The drop-down will update automatically. You can see which sheet the source is on by looking at the Source field in Data > Validity—it will show the sheet name before the range, like Sheet2.$A$1:$A$10.