Excel drop-down list not working: 7 fixes

1. The arrow doesn't appear

Excel only shows the drop-down arrow on the cell you've selected. Click the cell first. If the arrow still isn't there, work through three settings.

1. Select the cell and open Data > Data Validation. If Allow says Any value, the cell has no list at all; maybe someone pasted over it (section 4).
2. On the Settings tab, make sure In-cell dropdown is ticked. With it unticked, the rule still checks entries, but there's no arrow.
3. If the box is ticked and you still see no arrow, go to File > Options > Advanced, scroll to Display options for this workbook and set For objects, show to All. Pressing Ctrl+6 by accident can switch objects off, and drop-down arrows count as objects.

2. New items don't show up in the list

If the source is a fixed range like =$E$2:$E$6, an item typed into E7 isn't in the list, because the rule still stops at row 6. Excel doesn't stretch it for you.

Fix it once: turn the source list into a table (Ctrl+T) and set the source to the table column's cells; as the table grows, the reference grows with it. In Excel 365, you can also put a spill formula next to the source and point the rule at the spill range with #, like =$G$2#, which updates on its own.

3. Blank lines in the list

Every empty cell in the source range becomes an empty line in the drop-down. This status list was set to E2:E7 "to leave room", and it has a gap at E4 and an empty E7, so the drop-down shows two blank lines. The Ignore blank box doesn't remove them; it only controls whether an empty cell counts as valid.

Delete the gaps, or build a clean list with FILTER and point the rule at the spill range. Wrap it in SORT if you want the items in order.

4. Wrong values got in anyway

Data validation only checks what people type. A normal paste replaces the target cell's validation rule along with its value, so the cell stops having a drop-down at all. Paste Special > Values keeps the rule but doesn't check the pasted value. Values that were already in the cells before you added the rule aren't checked either.

Also check the Error Alert tab. If Style is Warning or Information, people can click Yes or OK and enter anything. Only Stop blocks invalid entries.

To find what slipped through, use Data > Data Validation (the arrow under the button) > Circle Invalid Data. Or list the bad rows with a formula, which also catches trailing spaces you can't see.

5. A typed list splits in the wrong places

If you type the items into the Source box instead of using a range, separate them with your system's list separator. That's a comma in English-language settings and a semicolon in many European ones, so Open;In progress;Closed may be needed on a German or French PC. A typed list is also limited to 255 characters, and anyone editing it has to open the rule. A range on a hidden sheet is easier to maintain.

The source can be on another sheet. It can't reliably come from another workbook: the list only works while that file is open.

6. Data Validation is greyed out

The button is unavailable when the sheet is protected (Review > Unprotect Sheet), when several sheets are grouped ([Group] in the title bar; right-click a tab > Ungroup Sheets), when a cell is still being edited (press Enter or Esc), or in an old shared workbook (Review > Share Workbook (Legacy), then stop sharing).

7. Small annoyances

The drop-down shows eight items at a time and the font size can't be changed; zoom in if it's too small to read. In Excel for Microsoft 365, typing into the cell suggests matching items from the list. If the list is a dependent drop-down built with INDIRECT, a first-level item with a space (In progress) won't match a named range, because names can't contain spaces; see the dependent list guide for the SUBSTITUTE fix.