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

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.

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.

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 are great for five or six items. Past that, they get ugly to maintain — and Excel has a hard limit (the Source string maxes out around 255 characters in older versions, longer in 365). Once you cross fifteen or twenty items, convert it to a range reference instead. We will show how to do that in a moment.

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.

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:

1. Go to Formulas > Name Manager, or hit Ctrl+F3.
2. In the list, find the name that matches what's in the Source field (without the equals sign).
3. Click it. The Refers to box at the bottom shows the actual cell range — something like =Sheet1!$A$2:$A$8.
4. 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.

Convert Any Drop-Down to a Dynamic Table Source

Here is the trick that solves most editing headaches before they start. Instead of a fixed range or a typed list, point your drop-down at an Excel Table. Tables auto-grow when you add a row, so the drop-down grows with them. No more reopening Data Validation every time you want to add an option.

The setup, step by step:

1. Put your list items in a column on any sheet — one item per cell, with a header at the top. Say A1 says Region and A2:A5 contain North, South, East, West.
2. Click anywhere in that data, then hit Ctrl+T. The Create Table dialog opens. Make sure My table has headers is ticked, and click OK.
3. Excel converts the data into a Table — you'll see banded rows and a filter on the header. The Table gets an auto-name like Table1. Rename it from the Table Design ribbon (the Table Name box on the far left) to something meaningful like RegionList.
4. Now, here's the wrinkle. Excel will not accept a Table reference directly in the Data Validation Source box. You have to wrap it with INDIRECT. So in Data Validation, type:

=INDIRECT("RegionList[Region]")

That tells the drop-down to read the Region column of the RegionList Table — and because Tables expand automatically, anything you add to the bottom of the Table becomes a new drop-down option without you touching Data Validation again.

Why the INDIRECT?

Excel's Data Validation engine, for historical reasons, does not parse structured Table references natively. INDIRECT converts the string into a live reference at evaluation time, and that's the workaround that has been in use since Tables shipped in Excel 2007. There is no version of Excel — including 365 — where you can skip the INDIRECT for a Table-sourced drop-down. So the formula is the formula.

One caveat: INDIRECT is a volatile function. On a workbook with hundreds of these drop-downs, you can see a small recalculation hit. For a handful, the cost is invisible.

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:

1. 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.
2. 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: cross-sheet references in Data Validation, particularly in older Excel versions (2010 and below), required a named range as a workaround. From 2013 on, you can reference another sheet directly. If you're maintaining a workbook that has been passed through old versions, you may see a layer of named ranges that exist purely to bridge the cross-sheet validation limit — those names are now redundant in modern Excel, but they do no harm.

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.

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.

Forgetting INDIRECT on a Table source. You try =Table1[Region] in the Source field, click OK, and Excel rejects it with a You may not use references to other worksheets or workbooks for Data Validation criteria error. The fix is INDIRECT: =INDIRECT("Table1[Region]"). The quotes turn the structured reference into a string 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.

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? Convert the source to an Excel Table and reference it via =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, Online, and Mobile

The Data Validation dialog looks slightly different on Mac — the Settings tab is the only one shown by default, and the keyboard shortcut path differs. But the editing workflow is identical: pick the cell, open Data Validation, edit the Source. Named Range editing happens under the same Formulas > Name Manager menu.

Excel Online supports viewing and using drop-downs but does not let you edit Data Validation rules — that has to happen in the desktop client. If your team mostly works in the browser, expect to occasionally bounce into desktop Excel to update sources. Excel for Android and iOS are read/write for drop-downs but the Data Validation editor is hidden behind several menus, and most pros do the edit on a laptop instead.

And one last reminder: there is no version of Excel where Data Validation accepts a structured Table reference without INDIRECT. Even the latest 365 build still requires the wrapper. If you see a tutorial that claims otherwise, it's testing an experimental build or, more often, just wrong.