Excel Drop-Down List Practice: 6 Problems About Why It Breaks After You Build It

Six problems about drop-down lists that were built correctly and stopped working anyway. Each group opens with the cause, then gives you a situation to judge.

Building a drop-down list takes about thirty seconds and there is little to get wrong. Keeping one working in a file that other people edit is a different problem, and it is the one worth practising, because every failure mode here is silent — the cell looks normal, the sheet looks normal, and the constraint you relied on is simply gone.

Three causes cover almost all of it. An ordinary paste overwrites validation along with formatting, so a single Ctrl+V from an email removes the drop-down without any warning. A source written as a fixed range never grows, so items added later never appear. And dependent drop-downs built on INDIRECT break on any category name containing a space, because defined names may not contain spaces.

Everything below behaves the same in Microsoft 365, Excel 2021 and recent versions. Work each problem before opening the answer.

Three causes, six problems

Why the drop-down disappears after someone pastes

2 problems

Data validation is a property of the cell, not a lock on it. An ordinary paste carries the source cell's entire formatting payload — including its validation, or its lack of one — and overwrites whatever was there. So the moment a colleague copies a value from Notepad or from another sheet and pastes it in, the drop-down is gone, silently, with no warning and no visual change until someone clicks the cell and finds no arrow. This is the single most common reason a validated sheet degrades over a few weeks of real use.

How it shows up: "My data validation disappeared" · "Drop-down stopped working after copy paste"

Q1

Cell B2 has a drop-down list of three regions. A colleague copies the text "North" from an email and pastes it into B2. The value is valid. What happens to the drop-down?

  1. ANothing — the value is valid, so validation is preserved
  2. BThe drop-down is removed, because a normal paste overwrites validation along with formatting
  3. CExcel blocks the paste and shows a warning
  4. DThe drop-down stays but stops filtering
▶Show answer & explanation

Answer: B. The drop-down is removed, because a normal paste overwrites validation along with formatting

🐱 Validity of the pasted value is irrelevant — the paste does not go through validation at all. It replaces the cell's contents and its properties, and since an email has no validation to bring, the cell ends up with none. Two defences are worth knowing: tell people to paste with Ctrl+Shift+V (values only), which leaves validation intact, and protect the sheet so validation cannot be overwritten in the first place. Neither is automatic, which is why this keeps happening.

Q2

You want to stop this from happening across a shared file. Which measure actually prevents validation from being overwritten by paste?

  1. ASetting the validation error style to Stop instead of Warning
  2. BProtecting the worksheet, so paste cannot modify the protected cells at all
  3. CTicking 'Apply these changes to all other cells with the same settings'
  4. DUsing a named range as the list source
▶Show answer & explanation

Answer: B. Protecting the worksheet, so paste cannot modify the protected cells at all

🐱 The Stop error style only governs what happens when someone types an invalid entry; it has no effect on paste, because paste bypasses validation entirely. Named ranges and the 'apply to all other cells' checkbox solve different problems — maintaining the source list and propagating settings, respectively. Sheet protection is the only one of the four that stops the overwrite, because it operates at a level above the cell's properties. Worth knowing that this is a real trade-off: protection also blocks the ordinary edits people legitimately need, so most teams protect only the validated columns.

The list that does not grow when you add items

1 problem

A validation source written as a fixed range like =Lists!$A$2:$A$10 means exactly those nine cells, forever. Type an item into A11 and the drop-down will not show it, because nothing told the range to extend. There are two fixes and they are not equivalent: converting the source into a Table (Ctrl+T) makes the range grow automatically as rows are added, and it is the option to reach for by default. A spill reference like =Lists!$A$2# works too when the list is itself produced by a dynamic-array formula such as UNIQUE or FILTER, which is the case worth recognising because it also keeps the list deduplicated and sorted without maintenance.

How it shows up: "New items don't show in my drop-down" · "How do I make a dynamic drop-down list?"

Q3

The validation source is =Lists!$A$2:$A$10. You add a tenth region in Lists!A11. Why does it not appear in the drop-down, and what is the standard fix?

  1. AThe reference is absolute; removing the dollar signs makes it dynamic
  2. BThe range is fixed at nine cells; convert the source into a Table so it grows as rows are added
  3. CThe list needs to be re-sorted before Excel picks up new entries
  4. DValidation caches its list and needs the file to be reopened
▶Show answer & explanation

Answer: B. The range is fixed at nine cells; convert the source into a Table so it grows as rows are added

🐱 Dollar signs control what happens when a formula is copied — they have nothing to do with whether a range grows. Removing them would make the reference relative and, if anything, more fragile. Converting the source to a Table is the fix because a Table's column reference means 'this column, however long it currently is'. After conversion, point the validation at the Table column and new rows appear in every drop-down that uses it, with no further maintenance.

Dependent drop-downs and why INDIRECT breaks

1 problem

The classic dependent drop-down works by naming one range per category and having the second list read =INDIRECT(A2) — Excel looks up a defined name whose text matches what the first cell says. It is elegant when it works and it fails in one specific way that is worth memorising: defined names cannot contain spaces, so a category called "North America" can never have a matching name, and INDIRECT returns an error. The usual patch is to replace spaces with underscores in the names and wrap the reference as =INDIRECT(SUBSTITUTE(A2," ","_")), which keeps the readable label on screen while pointing at a legal name underneath.

How it shows up: "Dependent drop-down list" · "INDIRECT returns an error in data validation"

Q4

A2 is a country drop-down; B2 should list that country's cities via =INDIRECT(A2). It works for "France" and "Japan" but errors for "United States". Why?

  1. AThe United States list has too many entries for validation
  2. BDefined names cannot contain spaces, so no name matches the text "United States"
  3. CINDIRECT only works with single-word ranges by design
  4. DThe name needs to be scoped to the worksheet rather than the workbook
▶Show answer & explanation

Answer: B. Defined names cannot contain spaces, so no name matches the text "United States"

🐱 INDIRECT converts the text in A2 into a reference by looking for a defined name spelled exactly that way. "France" is a legal name; "United States" cannot be, because names may not contain spaces. The fix is to name the range United_States and read it as =INDIRECT(SUBSTITUTE(A2," ","_")). Recognising this failure by its signature — works for some categories, errors for others, and the broken ones all have two words — saves a lot of time, because the formula itself looks completely correct.

Keep going

SUMIF, COUNTIF and COUNTIFS practice (8 problems)Conditional aggregation that returns a silent zero instead of an error.Excel formulas practice (8 problems)VLOOKUP failures, dollar signs when dragging, and reading error values.INDEX MATCH vs VLOOKUP vs XLOOKUPWhich lookup to use, and what still breaks after you switch.

Excel drop-down lists — FAQ

Why did my drop-down list disappear?

Almost always because someone pasted into the cell. Data validation is a cell property, and an ordinary paste replaces the cell's properties along with its contents — so pasting from a source that has no validation leaves the cell with none. The value pasted does not have to be invalid; paste bypasses validation entirely.

How do I stop people from overwriting a drop-down?

Protect the worksheet. Changing the error style to Stop does not help, because that only governs typed entries. Telling people to paste with Ctrl+Shift+V (values only) preserves validation, but relies on everyone remembering, so most teams protect the validated columns and leave the rest editable.

How do I make a drop-down list that grows automatically?

Convert the source range into a Table with Ctrl+T and point the validation at the Table column. A Table column reference means 'this column, however long it currently is', so rows added later appear in every drop-down using it. If the list comes from a dynamic-array formula like UNIQUE or FILTER, a spill reference such as =Lists!$A$2# does the same job.

Why does INDIRECT fail in a dependent drop-down?

Because the category name contains a space. INDIRECT looks for a defined name spelled exactly like the text in the first cell, and defined names cannot contain spaces — so "United States" can never match. Name the range United_States and use =INDIRECT(SUBSTITUTE(A2," ","_")).

Can a drop-down list show items from another workbook?

Not reliably. Validation sources pointing at a different workbook only resolve while that workbook is open, so the list appears empty the rest of the time. Copy the list into the same workbook — on a hidden sheet if you want it out of the way — and point the validation there.