How to Edit a Drop-Down List in Excel: Three Cases, Three Fixes

TL;DR: To edit a drop-down list in Excel, first find out which of the three kinds you have. A list typed directly in the data validation dialog is edited in that dialog. A list pointing at a range is edited by changing the cells in that range. A list driven by a Table or named range grows automatically and needs no dialog at all. Editing the wrong one is why people “change the list” and see nothing happen.

Step one: find the source

Before changing anything, select the cell that has the drop-down and open Data → Data Validation → Data Validation. The Settings tab shows the current source, and the answer tells you which case you are in.

If the Source box contains literal text separated by commas — Red,Amber,Green — the list is typed in. If it contains a formula such as =Sheet2!$A$1:$A$5 or a name like =StatusList, it points elsewhere. If the cell is part of a table and the validation references a structured name, it is table-driven.

Checking first saves the common wasted effort of editing a range that was never the source.

Case one: a typed-in list

This is the simplest case and the most common in small personal workbooks.

Open Data Validation on the cell, go to Settings, and edit the text in the Source box directly. Add items separated by commas, remove them, reorder them. There is a practical limit of 255 characters in this box, which is the main reason to move to a range once the list grows.

Two things to watch. If you edit only one cell, other cells with the same validation keep the old list — use Paste Special → Validation, or select all the cells first and apply once. And items with commas inside them cannot be represented at all in this format, because the comma is the separator.

Case two: a list pointing at a range

Here the dialog is a pointer and the values live in cells, which is better for anything beyond a handful of items.

Find the referenced range — the Source box tells you the sheet and address — then edit the cells there. The drop-down updates immediately, no dialog needed.

The trap is blank cells inside the range. A validation range of A1:A10 with only five values shows five items and then five blank entries in the drop-down. Either keep the range tight or make it dynamic.

The other trap is deleting a referenced row. If the range shrinks, the validation reference can become invalid and Excel may report an error when the dialog is reopened.

Case three: Table or named range (the approach that scales)

This is what you want for any list that will grow, because it removes the maintenance entirely.

Put the values in a Table (Ctrl+T), then point validation at the table column. Adding a row to the table extends the list automatically; deleting one shrinks it. No dialog, no range editing, no blanks.

The named-range alternative works the same way if you use a dynamic formula — a name defined as =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) grows as values are added. It is more fragile than a Table and harder to debug, but it works in older files that cannot use tables.

The named range is also what you need for dependent drop-downs, where the second list depends on the first. Those are built by naming each sub-list and using INDIRECT in the second validation — which is more complex than it sounds and worth doing only when the dependency is genuinely needed.

Renaming and deleting entries

Renaming is a data question rather than a validation question, and it has a consequence most people do not anticipate.

If you rename an item in the source range, every cell that already contains the old value becomes invalid — Excel does not update existing selections to match. The cells keep the old text, the drop-down no longer offers it, and validation may flag them with a circle.

The clean sequence is: change the source, then use Find & Replace across the data range to update existing values to the new name, then re-check validation.

Deleting an entry has the same issue. Existing cells keep the value, so purge it from the data first if the value should no longer appear anywhere.

Why the drop-down sometimes stops appearing

Three causes account for almost every report of a missing drop-down.

The Source box is empty or the reference is broken. The cell still shows a small arrow, but clicking it offers nothing. Reopen the dialog and confirm the reference resolves to cells that exist.

The source range is on a deleted sheet. A hidden sheet is usually fine, but a deleted one breaks the reference silently, and Excel keeps the validation setting while showing no list.

A named range is scoped to one sheet only. If the name was defined with local scope on a different sheet, validation on the current sheet cannot resolve it, even though the name exists.

In each case the fix is in the dialog: reopen it, look at the Source box, and confirm the reference resolves to cells that exist. If the box is filled and the reference resolves, the validation is fine and the problem is a cell-level override or a merged cell.

Editing a list inside a game or interactive sheet

If the drop-down is part of something more elaborate — a scoring sheet, a quiz, a lookup-driven board — validation is usually one layer of a stack, and the list may also feed formulas that key off the exact text.

That is the case to check the dependent formulas before renaming anything. A renamed item that breaks three lookups is a worse outcome than a stale list. Interactive Excel game templates show the pattern clearly, because the wrong value in a validation list visibly breaks the board rather than quietly producing a wrong number.

The short version

Open Data Validation to see the source, then edit in the right place: the dialog for typed lists, the cells for range lists, the Table for dynamic ones. Watch for blanks in ranges, expect broken references when sheets are deleted, and remember that renaming an item does not update cells that already contain the old value.