How do I add an item to an existing drop-down list in Excel?

Open Data > Data Validation and check the Source. If it is a typed list, add a comma and the new item. If it is a range such as =$A$2:$A$5, type the item in the cell below and extend the range to include it, or keep the items in an Excel Table so the list grows by itself.

Why is my new item not showing in the Excel drop-down?

The Source range probably ends above the new item. A source like =$A$2:$A$5 ignores a value typed in A6. Open Data > Data Validation and widen the range, or put the list in an Excel Table instead. Microsoft says drop-downs based on a Table update drop-downs automatically when you add or remove items, so no further editing is needed.

How do I make an Excel drop-down list update automatically?

Put the list items in an Excel Table (select them and press Ctrl+T), then set the Data Validation Source by selecting the Table cells without the header. Microsoft states that drop-downs based on a Table update automatically as items are added or removed, so you never reopen the dialog. Another option is to use =INDIRECT("TableName[Column]") as the Source formula.

How do I remove an item from an Excel drop-down list?

Open Data > Data Validation. For a typed list, delete the item and one neighbouring comma in the Source box. For a range, delete the item from its cell and shrink the range so no blank entry remains. If the Source is a Table, delete that Table row. Click OK to save.

How do I apply an edit to every cell that has the same drop-down?

In the Windows Data Validation dialog, tick the checkbox named Apply these changes to all other cells with the same settings before you click OK. Without it, only the cell you opened changes. Alternatively, edit the shared source, such as the named range or Table, once so that every drop-down using it updates.

Why is Data Validation greyed out when I try to edit a drop-down?

Microsoft says the Data Validation command is unavailable when the worksheet is protected or the workbook is shared. Remove sheet protection with Review > Unprotect Sheet, or stop sharing the workbook, then try Data > Data Validation again. Excel for Mac and the web use the same Data tab command.

How do I delete a drop-down list in Excel?

Select the cell or cells, go to Data > Data Validation, click Clear All, then click OK. Microsoft documents this as the way to remove validation. The arrow disappears and the cell accepts any entry again. To keep the arrow but allow other values, change the Error Alert style from Stop to Warning or Information.

How do I add an item to an existing drop-down list in Excel?

Open Data > Data Validation and check the Source. If it is a typed list, add a comma and the new item. If it is a range such as =$A$2:$A$5, type the item in the cell below and extend the range to include it, or keep the items in an Excel Table so the list grows by itself.

Why is my new item not showing in the Excel drop-down?

The Source range probably ends above the new item. A source like =$A$2:$A$5 ignores a value typed in A6. Open Data > Data Validation and widen the range, or put the list in an Excel Table instead. Microsoft says drop-downs based on a Table update drop-downs automatically when you add or remove items, so no further editing is needed.

How do I make an Excel drop-down list update automatically?

Put the list items in an Excel Table (select them and press Ctrl+T), then set the Data Validation Source by selecting the Table cells without the header. Microsoft states that drop-downs based on a Table update automatically as items are added or removed, so you never reopen the dialog. Another option is to use =INDIRECT("TableName[Column]") as the Source formula.

How do I remove an item from an Excel drop-down list?

Open Data > Data Validation. For a typed list, delete the item and one neighbouring comma in the Source box. For a range, delete the item from its cell and shrink the range so no blank entry remains. If the Source is a Table, delete that Table row. Click OK to save.

How do I apply an edit to every cell that has the same drop-down?

In the Windows Data Validation dialog, tick the checkbox named Apply these changes to all other cells with the same settings before you click OK. Without it, only the cell you opened changes. Alternatively, edit the shared source, such as the named range or Table, once so that every drop-down using it updates.

Why is Data Validation greyed out when I try to edit a drop-down?

Microsoft says the Data Validation command is unavailable when the worksheet is protected or the workbook is shared. Remove sheet protection with Review > Unprotect Sheet, or stop sharing the workbook, then try Data > Data Validation again. Excel for Mac and the web use the same Data tab command.

How do I delete a drop-down list in Excel?

Select the cell or cells, go to Data > Data Validation, click Clear All, then click OK. Microsoft documents this as the way to remove validation. The arrow disappears and the cell accepts any entry again. To keep the arrow but allow other values, change the Error Alert style from Stop to Warning or Information.

Editing a Drop Down List in Excel: Add, Remove, and Update Items 2026 October

Q: How do I edit a drop-down list in Excel?

Select the cell with the drop-down, then go to Data > Data Validation. On the Settings tab, change the Source box and click OK. What you change depends on the source: edit the text for a typed list, edit the cells or widen the range for a range, or edit the named range or Table that the Source points to.

Edit a drop-down list in Excel via Data Validation. Update typed lists, range references, named ranges, or convert to a dynamic Table source. ✍🏼

Microsoft Excel By Katherine LeeOct 4, 202616 min read

Editing a Drop Down List in Excel: Add, Remove, and Update Items 2026 October

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.

3Drop-down source types

Alt+A+V+VOpen Data Validation

1Click to convert to Table

0Macros required

How to Edit a Dropdown List in Excel - Microsoft Excel certification study resource

First, Identify the Source Type

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:

A comma-separated list of items like North,South,East,West. That's a typed list. Edit the text directly in the Source box.
A range reference like =$A$2:$A$5 or =Sheet2!$A$2:$A$10. The list comes from cells. Change the cells, or expand the range.
A name preceded by an equals sign like =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.

Drop-Down Source Types Compared: How to Edit Each and Whether It Updates Itself

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.

The Single Most Useful Shortcut

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.

Scenario 1: Editing a Typed Comma List

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.

When the typed list gets too long

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.

Edit by Source Type — Cheat Sheet

Typed Comma List

Source looks like Yes,No,Maybe. Open Data Validation and edit the text directly in the Source box. Save with OK.

Range Reference

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.

Named Range

Source looks like =Regions. Open Formulas > Name Manager, click the name, edit the Refers to reference.

Excel Table Source

Source looks like =INDIRECT("Table1[Region]"). Add a row to the Table — the drop-down expands automatically. No dialog needed.

Scenario 2: Editing a Range Reference

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.

Editing a range that points to another sheet

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.

Microsoft Excel - Microsoft Excel certification study resource

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.

Scenario 3: Editing a Named Range

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:

Go to Formulas > Name Manager, or hit Ctrl+F3.
In the list, find the name that matches what's in the Source field (without the equals sign).
Click it. The Refers to box at the bottom shows the actual cell range — something like =Sheet1!$A$2:$A$8.
Click Edit to change the reference, or just adjust the cells the reference points to. Then click Close.

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.

What if I can't find the named range?

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.

📄Free Microsoft Excel Practice Test PDF

⚠️Applying Changes to Every Cell That Uses This Drop-Down

When you edit the Data Validation source, you change it for the single cell you opened the dialog on. If the same drop-down is used across hundreds of cells, you need to push the change to all of them.

In the Data Validation dialog, look for the checkbox at the bottom of the Settings tab labeled Apply these changes to all other cells with the same settings. Tick it before clicking OK. The new Source applies everywhere the old one was in force.

This checkbox appears in the Windows Data Validation dialog when other cells share the same validation rule.

If you forget to tick that box, you'll have one cell with the new list and the rest unchanged — which is a great way to confuse yourself an hour later when half the column shows old values.

Test Your Excel Skills

Make the Drop-Down Grow Automatically With an Excel Table

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:

Put your list items in one column with a header at the top. For example, A1 says Region and A2:A5 contain North, South, East, West.
Click anywhere in that data and press Ctrl+T. Make sure My table has headers is ticked, and click OK.
Optionally rename the Table on the Table Design tab (Table Name box) to something meaningful such as RegionList.
Select the drop-down cell, open Data > Data Validation, set Allow to List, click in Source, and drag-select the Table cells that hold the items. Leave out the header cell. This is the method in Microsoft's guide.

Now a new row typed directly under the Table joins the Table, and the drop-down picks it up.

Using INDIRECT with a Table name

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.

✓Confirm the drop-down arrow appears on the cell — if no arrow, validation is missing or disabled.
✓Open the drop-down and verify all expected items show, in the order you want.
✓If many cells share the source, tick <em>Apply these changes to all other cells with the same settings</em>.
✓For range-based sources, check the range still covers every row of list items.
✓For named ranges, verify the Name Manager points at the right cells with no stray rows.
✓If you want auto-growth, keep the list in an Excel Table and select its cells (without the header) as the source.
✓Test a deliberate bad entry — Excel should reject it or show your warning message.

Excel Spreadsheet - Microsoft Excel certification study resource

Pulling List Items From Another Sheet

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:

Edit the data on the other sheet. Unhide the source sheet (right-click any visible tab, choose Unhide, pick the sheet). Change the cells. Re-hide if you want it out of the way.
Edit the reference itself. In the Source box, you can change the range or even point it at a different sheet. =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.

Removing a Drop-Down Entirely

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.

✅Pros

+Fixed range: simple to set up, no Table or INDIRECT involved.
+Fixed range: easy to inspect — just look at the cells in the reference.
+Fixed range: zero performance overhead on large workbooks.
+Table source: auto-grows when you add a row, no Data Validation edit needed.
+Table source: cleaner workbooks — list management is a normal data-entry task, not a dialog tour.
+Table source: tables can be sorted and filtered without breaking the drop-down.

❌Cons

−Fixed range: every new item requires expanding the range in Data Validation.
−Fixed range: blank cells inside the range show up as empty options in the drop-down.
−Fixed range: easy to miss adding rows beyond the range — silent failures.
−Table source: typing a structured reference directly is rejected, so you select the cells or use INDIRECT (volatile).
−Table source: not all legacy spreadsheets play nicely if the Table is converted back to a range.
−Table source: marginal recalc cost on workbooks with hundreds of drop-downs.

Common Editing Mistakes — and How to Dodge Them

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.

Practice More Excel

Quick Reference: How to Edit a Drop Down List in Excel

Need to make a change right now? Here's the 30-second decision tree.

Just want to add or remove one item? Alt+A+V+V, edit Source, OK.
Source is a typed list (e.g. Yes,No,Maybe)? Edit the text directly in the Source box.
Source is a range (e.g. =$A$2:$A$5)? Either edit the cells, or widen the range in Source.
Source is a named range (e.g. =Regions)? Ctrl+F3 > pick the name > adjust Refers to.
Want it to auto-grow? Keep the items in an Excel Table and select the Table cells as the Source (or use =INDIRECT("TableName[ColumnName]")).
Apply the same edit to every cell with the same rule? Tick Apply these changes to all other cells with the same settings.
Remove the drop-down entirely? Open Data Validation, click Clear All, OK.

A Note on Excel for Mac and Excel for the Web

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.

Sample Excel Practice Questions

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?
- A. Deletes the current cell
- B. Toggles absolute/relative cell references
- C. Opens the function wizard
- D. Saves the workbook

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?

A. F11
B. F10
C. F9
D. F8

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?

A. Home tab → Freeze Panes → Freeze First Row
B. View tab → Freeze Panes → Freeze Top Row
C. Page Layout tab → Freeze Panes → Freeze First Row
D. Data tab → Freeze Row

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?

A. Toggles between absolute and relative cell reference modes for the selected reference
B. Repeats the last action
C. Opens the Find and Replace dialog
D. Closes the formula bar

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.

Take the full Excel practice test

Excel Questions and Answers

About the Author

Katherine LeeMBA, CPA, PHR, PMP

Business Consultant & Professional Certification Advisor

Wharton School, University of Pennsylvania

Katherine Lee earned her MBA from the Wharton School at the University of Pennsylvania and holds CPA, PHR, and PMP certifications. With a background spanning corporate finance, human resources, and project management, she has coached professionals preparing for CPA, CMA, PHR/SPHR, PMP, and financial services licensing exams.