How to Edit or Replace a Dropdown List
To change a dropdown list in Excel, you select the cell or range containing the list, then open the Data Validation dialog and modify the source data or settings. The steps differ slightly depending on whether you want to edit the list items themselves, change where the list pulls from, or alter how the dropdown behaves.
The fastest route is to go to the Data tab, click Data Validation (in the Data Tools group), and then edit the Source field. If your dropdown currently pulls from a named range or a cell range, you can change that range. If it uses a typed list, you can edit the items directly in the Source box.
Key Takeaways
- Open Data Validation from the Data tab, select the cells with the dropdown, and edit the Source field to change what items appear in the list.
- If your dropdown pulls from a range of cells, you can change that range to point to a different set of cells without rebuilding the dropdown.
- You can switch between a typed list (items separated by commas or line breaks) and a cell range source without deleting and recreating the dropdown.
- Changes to the source range or typed list explore when ready to all cells using that dropdown rule.
- If you delete the source range that a dropdown points to, the dropdown will show an error; you must update the Data Validation rule to fix it.
Editing a Typed List in Your Dropdown
If you created your dropdown by typing items directly into the Source field (separated by commas or line breaks), you can edit those items without leaving the Data Validation dialog. Select the cell or range with the dropdown, go to Data > Data Validation, and look at the Source box.
The items will appear as text, usually separated by commas or on separate lines depending on how you entered them. You can add new items, remove old ones, or change the spelling of existing items. After you make your changes, click OK. The dropdown will update when ready for all cells in that range.
If the list is long or you plan to change it often, consider moving it to a separate range of cells instead. That way you can edit the list by straightforward changing the cells, without opening Data Validation each time.
Changing the Source Range Your Dropdown Points To
If your dropdown pulls from a range of cells—for example, cells A1:A10 contain your list items—you can point it to a different range instead. Open Data Validation on the cells with the dropdown, and in the Source field, change the range reference.
For example, if the Source currently shows =A1:A10, you can change it to =B1:B15 to pull from a different set of cells. You can also use a named range if you have created one. After you update the range, click OK. The dropdown will now show items from the new source.
Be careful when you delete or move the source range. If you delete the cells the dropdown points to, the dropdown will display an error. You will need to update the Data Validation rule to point to a new range, or recreate the dropdown entirely.
Switching Between a Typed List and a Cell Range
You can change a dropdown from using a typed list to using a cell range, or vice versa, without deleting the dropdown. Open Data Validation on the cells with the dropdown. In the Source field, replace the current content with either a new range reference (like =Sheet2!C1:C20) or a typed list (like Red, Blue, Green).
This is useful if your list started small and typed, but grew large enough that a cell range makes more sense. Or if your list was in cells but you want to simplify by typing the items directly. The dropdown will work the same way to the user—they will still see a list to choose from—but the source of that list changes.
Updating Dropdown Settings Without Changing Items
You may want to keep the same list items but change how the dropdown behaves. Open Data Validation on the cells with the dropdown. You can adjust settings like:
- Allow: Change from "List" to another type (like "Whole Number" or "Date") if you want different validation rules.
- In-cell dropdown: Check or uncheck this box to show or hide the dropdown arrow in the cell.
- Show error alert: Turn this on to display a message if someone enters a value not on the list.
- Error title and message: Customize what the error message says.
These changes affect how the dropdown works but do not change the list items themselves. After you adjust the settings, click OK.
Removing or Clearing a Dropdown List
If you want to remove the dropdown entirely, select the cells, go to Data > Data Validation, and click Clear All. This deletes the validation rule and the dropdown will no longer appear in those cells.
If you want to keep the dropdown but clear the list items, you would need to delete the source range (if it is in cells) or edit the Source field to be empty. However, an empty dropdown is usually not useful, so most people straightforward remove the validation rule instead.
Troubleshooting Common Dropdown Problems
If your dropdown shows an error or stops working, the most common cause is that the source range was deleted or moved. Open Data Validation and check the Source field. If it refers to cells that no longer exist, update the range to point to valid cells.
If the dropdown arrow does not appear in the cell, check that In-cell dropdown is checked in the Data Validation dialog. If someone has entered a value that is not on the list, the dropdown may not display that value in the list, but the cell will still contain the text. You can edit the cell to remove the invalid entry, or update the list to include it.
If you copy cells with a dropdown to a new location, the dropdown rule copies with them. If the source range was relative (like =A1:A10), it will adjust based on where you paste. If it was absolute (like =$A$1:$A$10), it will point to the same cells in both locations.
Frequently Asked Questions
Can I change a dropdown list without opening Data Validation?
If your dropdown pulls from a cell range, you can edit the items by straightforward changing the cells in that range. You do not need to open Data Validation. However, if your dropdown uses a typed list (items entered directly in the Source field), you must open Data Validation to edit it.
What happens if I delete the cells my dropdown points to?
The dropdown will show an error and stop working. You must open Data Validation and update the Source field to point to a new range, or delete the validation rule entirely. The cells that had the dropdown will no longer function as a dropdown until you fix the source.
Can I use a dropdown from a different worksheet?
Yes. In the Source field, use a range reference that includes the sheet name, like =Sheet2!A1:A10. Make sure the sheet name is spelled correctly and use an exclamation mark to separate the sheet name from the range.
How do I add items to a dropdown without editing Data Validation?
If your dropdown pulls from a cell range, straightforward add the new items to the end of that range. If the range is =A1:A10 and you want to add more items, you can extend it to =A1:A15 by opening Data Validation and updating the Source field. Alternatively, use a named range that you can expand without changing the dropdown rule.
Can I have different dropdown lists in different cells?
Yes. Create a dropdown in one cell or range, then create a separate dropdown in another cell or range with different source data. Each dropdown rule is independent. You can also use a formula-based approach where the dropdown in one cell changes based on what is selected in another cell, though that requires more advanced setup.