To edit an Excel drop-down list, select the cell, choose Data > Data Validation, and change the Source box on the Settings tab. What you change depends on the source: edit the text for a typed list, edit the cells (or widen the range) for a cell range, or edit the Table or name the Source points to.
You inherit a spreadsheet. Cell D2 has a tidy little drop-down: North, South, East, West. Now somebody asks you to add Central. You click the cell. You scroll the list. Central isn't there, and there's no obvious place to add it.
That's the question we're answering: how do you edit a drop-down list in Excel once it already exists? The answer depends on how the original list was built. Three flavors. Each one is edited differently. The trick is figuring out which flavor you're looking at, then opening the right dialog and changing the right thing.
This guide walks through all three editing scenarios, the typed comma-list, the range reference, and the named range, plus how to convert any of them to a dynamic Table source so the list grows by itself when you add a new row. We will also cover pulling items from another sheet, and how to remove a drop-down entirely when you no longer want it.
If you came here just to create a new drop-down from scratch, you probably want the sibling guide on adding a drop-down list. This one assumes the drop-down already exists and you need to change what's in it.
Before you change anything, you need to know what kind of source the drop-down is reading from. Click the cell that has the drop-down. Then go to Data > Data Validation (or use Alt+A+V+V, the legacy ribbon shortcut). The Data Validation dialog opens. Look at the Source field on the Settings tab.
You will see one of three things — and each one tells you exactly what kind of edit you need to make:
North,South,East,West. That's a typed list. Edit the text directly in the Source box.=$A$2:$A$5 or =Sheet2!$A$2:$A$10. The list comes from cells. Change the cells, or expand the range.=Regions. That's a named range. Edit it under Formulas > Name Manager.One quick heuristic: if the Source field starts with = and a letter (not a sheet name or dollar sign), it's almost certainly a named range. If it starts with = and a cell reference, it's a range. Anything else is a typed list.
Got it? Good. Now jump to the section that matches your case.
| Source type | What the Source box shows | How to edit it | Updates automatically? |
|---|---|---|---|
| Typed list | North,South,East,West | Edit the text in Source (Data > Data Validation) | No, edit Source each time |
| Cell range | =$A$2:$A$5 | Change the cells, or widen the range in Source | No, items outside the range are ignored |
| Named range | =Regions | Formulas > Name Manager > Edit > Refers to | Only if the name points at a Table column or dynamic formula |
| Excel Table column | Table cells selected as the range, or =INDIRECT("RegionList[Region]") | Add or remove rows in the Table | Yes (per Microsoft, drop-downs based on a Table update automatically) |
| Another sheet | =Lists!$A$2:$A$15 | Edit the cells on that sheet, or the reference | No, unless the source is a Table |
Tip: tick Apply these changes to all other cells with the same settings (Windows) when the same drop-down is used in many cells.
On Windows, select the cell with the drop-down, then press Alt+A+V+V. The Data Validation dialog opens on the Settings tab, the only tab you need for editing.
If you forget the shortcut, the ribbon path is Data > Data Validation. On Excel for Mac and Excel for the web, use the same Data tab > Data Validation command.
If Data Validation is greyed out, the worksheet is protected or the workbook is shared. Microsoft documents that validation settings cannot be changed in either case.
This is the simplest case. The Source field shows something like North,South,East,West — a flat string with commas separating the items. To add Central, click into the Source box and just type it in:
North,South,East,West,Central
Click OK. Open the cell's drop-down arrow. Central is there. Done.
A couple of small things to know. The separator Excel expects in the Source box is whatever your regional list separator is — comma in the US and most English locales, semicolon in much of Europe. If you paste a US-format list into a European Excel, the items will all jam together as one entry. Match the separator your install uses.
The order in the box is the order shown in the drop-down. So if you want Central to appear at the top, edit the string to Central,North,South,East,West. Excel does not alphabetize for you. You sort manually.
To remove an item from a typed list, just delete it from the Source string (along with one of the surrounding commas), then click OK. To rename one, change the text in place. To reorder, retype the list in the order you want.
Typed lists suit five or six stable items. Past that they are hard to maintain, and every edit means retyping the string. Once you reach fifteen or twenty items, move the items into cells and point the Source at that range, as described next.
Source looks like Yes,No,Maybe. Open Data Validation and edit the text directly in the Source box. Save with OK.
Source looks like =$A$2:$A$5. Either edit the cells in that range, or expand the range in the Source field to cover more rows.
Source looks like =Regions. Open Formulas > Name Manager, click the name, edit the Refers to reference.
Source looks like =INDIRECT("Table1[Region]"). Add a row to the Table — the drop-down expands automatically. No dialog needed.
If the Source field shows a cell reference like =$A$2:$A$5, the drop-down is reading from a column of cells somewhere in the workbook. You have two editing paths:
Path A: change what's in the cells. Go to the referenced range (in this example, A2:A5 on the active sheet). Type over the values. Add new ones inside the range. When you close and reopen the drop-down, the changes are reflected. Quick, but the range size is fixed. If you type something into A6, the drop-down won't see it because the range still stops at A5.
Path B: expand the range. Open Data Validation again. Edit the Source field from =$A$2:$A$5 to =$A$2:$A$10 (or whatever new end row you need). Click OK. Now you have room to add more items by typing into A6, A7, and so on. The drop-down picks them up.
The frustration most people hit: they add an item to A6, then wonder why it doesn't show up. The answer is almost always that the source range still ends at A5. Either expand it manually, or use the Table trick below so the range stretches itself.
A source like =Sheet2!$A$2:$A$10 means the list lives on Sheet2. Same editing rules — go to Sheet2, change the values, or come back to Data Validation and adjust the range. Excel will not break the reference if you rename Sheet2, but if you delete the sheet entirely, every drop-down pointing at it goes blank.
If your list lives on a sheet you want to hide from end users, that works fine. Hidden sheets still serve as valid Data Validation sources. Right-click the sheet tab and choose Hide — the drop-downs keep working.
What to do: Open Data Validation, edit the comma string in the Source field.
Add an item: Type a comma then the new item at the end of the string.
Remove an item: Delete the item and one of its surrounding commas.
Best for: Short, stable lists like Yes/No or status labels.
What to do: Either edit the cells in the referenced range, or widen the range in the Source field.
Add an item: Type into an empty cell inside the range, or extend the range to include new rows.
Remove an item: Delete the cell content. The drop-down may still show a blank slot — clear it cleanly by adjusting the range.
Best for: Medium lists where you want to see and sort the items on a sheet.
What to do: Formulas > Name Manager > select the name > click Edit > adjust Refers to.
Add an item: Type into the next empty cell in the underlying range. If the name's reference is fixed, widen it.
Remove an item: Delete the cell or shrink the named range.
Best for: Lists reused by multiple drop-downs or formulas across the workbook.
If the Source field shows something like =Regions or =ProductList, that's a named range — a label that points to a set of cells. To change what's in the drop-down, you change what the name points to.
Step by step:
=Sheet1!$A$2:$A$8.Most of the time you do not need to touch the Edit dialog at all. The cleaner approach is to go to the cells the named range covers and either add or remove items there. If you want the named range to grow with new items, see the Table conversion section coming up — it's the cleanest fix.
Two reasons that happens. Either it's hidden (rare, but possible — names can be marked hidden by VBA), or it's a workbook-level name and you're filtering by sheet. In the Name Manager, set the Filter dropdown in the top right to All and the name should appear.
If it still isn't there but the Source field references it, you have a broken reference. That happens when a workbook is merged or when a sheet is deleted. Recreate the name from scratch — give it the same label and point it at the right cells.
The most reliable way to avoid re-editing Data Validation is to keep the list items in an Excel Table. Microsoft's own drop-down guide recommends this: when the list lives in a Table, drop-downs based on it update automatically as you add or remove items.
The setup, step by step:
Now a new row typed directly under the Table joins the Table, and the drop-down picks it up.
Typing a structured reference such as =RegionList[Region] straight into the Source box is not accepted by Data Validation. A widely used workaround is to wrap it in INDIRECT:
=INDIRECT("RegionList[Region]")
INDIRECT turns the text into a live reference when Excel evaluates it. Note that INDIRECT is a volatile function, so it recalculates often; on a workbook with hundreds of such drop-downs, selecting the Table cells directly (step 4) avoids that cost.
A common pattern: your drop-down lives on a user-facing sheet, but the list of options lives on a hidden admin sheet. Editing this kind of drop-down is no harder than the in-sheet version, but the path is slightly different.
Open Data Validation on the cell with the drop-down. The Source field shows something like =Lists!$A$2:$A$15 or =AdminData!Regions. You have two options:
=Lists!$A$2:$A$15 becomes =Lists!$A$2:$A$30 to include more rows.One detail that trips people up: very old Excel versions could not point Data Validation at another sheet directly and needed a named range as a bridge. Modern Excel accepts a direct reference like =Lists!$A$2:$A$15. If you inherit an old workbook, you may see names that exist only for that purpose; they are harmless.
If you ever rename the source sheet, Excel updates the Source reference automatically. If you delete the sheet, the drop-down breaks silently — the arrow stays but no items appear. Worth checking after any sheet cleanup.
To kill a drop-down on a cell — say the field is no longer constrained to a list — select the cell (or the whole column), open Data Validation, and click Clear All in the bottom left of the dialog. Then OK. The drop-down arrow disappears, and the cell accepts free-form input again.
If you want to remove a drop-down from many cells at once, select all of them first, then Clear All. The single click wipes them all.
Want to keep the drop-down but allow values outside the list? Open Data Validation, go to the Error Alert tab, and change the Style from Stop to Warning or Information. A Warning lets the user override after a prompt. Information just shows a message but allows anything. The drop-down arrow stays, the list still appears, but users can type anything they want.
If you want more targeted practice, the excel latest version page covers the same material with additional questions and explanations.
Editing one cell, expecting all cells to update. The single most common stumble. If your workbook has the same drop-down across thirty cells and you change Data Validation on cell D2, only D2 updates. The Apply these changes to all other cells with the same settings checkbox at the bottom of the Settings tab is the fix. Tick it before you click OK.
Adding items beyond the source range. The source is =$A$2:$A$10. You type into A11 and wonder why the new item doesn't appear in the drop-down. The range hasn't been extended. Either expand it (back to Data Validation, edit the Source) or convert the source to a Table so it auto-extends.
Typing a Table reference directly in Source. Entering =Table1[Region] in the Source box does not work. Either drag-select the Table cells (without the header) or use =INDIRECT("Table1[Region]"). The quotation marks turn the structured reference into text that INDIRECT then evaluates.
Renaming the source sheet or name and missing a reference. Excel updates most references when you rename a sheet, but custom validation messages or formulas that include sheet names as text strings won't auto-update. After any rename, click into a couple of drop-downs and confirm they still populate.
Deleting the source data without removing the validation. The drop-down arrow stays. The list appears empty. Users see a useless arrow and no options. Either delete the validation rule (Clear All in the Data Validation dialog) or rebuild the source.
Mixing typed lists and range references on the same column. Some cells have one, some have the other. The Apply these changes checkbox can't merge them. Easier to clear validation across the whole column, then reapply uniformly.
Need to make a change right now? Here's the 30-second decision tree.
=INDIRECT("TableName[ColumnName]")).Microsoft documents the same drop-down workflow for Windows, macOS and the web: select the cell, go to Data > Data Validation, and change the Source (typed entries, or the source range). Keyboard shortcuts differ. Alt+A+V+V and Ctrl+F3 are Windows shortcuts, so on Mac use the ribbon commands.
If Data Validation is unavailable, check whether the sheet is protected or the workbook is shared. Both block validation changes.
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
What does the F4 key do when editing a cell formula in Excel?
Answer: B. Toggles absolute/relative cell references
F4 cycles through the four reference types: relative (A1), absolute ($A$1), mixed ($A1), and mixed (A$1).
If Excel's Auto Calculate option is disabled, how can you update the values of formula cells?
Answer: C. F9
Manual calculation mode means that Excel will only recalculate all open workbooks when we request it by pressing F9. It allows to choose whether we want to update formulas in worksheets or entire workbook.
How do you freeze the top row in Excel so it remains visible while scrolling down?
Answer: B. View tab → Freeze Panes → Freeze Top Row
On the View tab, clicking Freeze Panes and then Freeze Top Row locks row 1 in place during vertical scrolling.
What does pressing F4 do when editing a cell formula in Excel?
Answer: A. Toggles between absolute and relative cell reference modes for the selected reference
While your cursor is on a cell reference in a formula, F4 cycles through all four reference types: relative, absolute, mixed row, and mixed column.