In Excel: open Data ▸ Data Validation on the cell and check it still says Allow: List, that "In-cell dropdown" is ticked and that the Source still points at the right range; if the arrow is missing everywhere, set File ▸ Options ▸ Advanced ▸ "For objects, show" to All.
On this page7
Select a cell that should show the list and open Data ▸ Data Validation.
If Allow is not List, the rule was overwritten — copy a working cell and use Paste Special ▸ Validation on the broken ones.
If Allow is List but no arrow shows, tick In-cell dropdown.
Check the Source: a #REF! or a name that no longer exists means the source range was deleted or renamed — point it at the current range.
If no drop-down arrows show anywhere in the workbook, set File ▸ Options ▸ Advanced ▸ For objects, show: All.
On a protected sheet, unprotect it, unlock the drop-down cells (Format Cells ▸ Protection), then protect it again.
What this does
A drop-down list is a Data Validation rule on the cell, so almost every failure is the rule being gone, switched off, or pointing somewhere wrong. Pasting over a cell replaces its validation with the source cell's; unticking In-cell dropdown keeps the rule but hides the arrow; deleting or renaming the source range leaves a broken reference; and the workbook option that hides objects also hides every drop-down arrow. Protection matters too: on a protected sheet, a locked cell cannot be changed even from its list. Most of these problems have a short list of causes. Check them in order, from the most common to the least, and stop at the first one that explains what you see. For “excel drop down list not working”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected dropdown change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Column D used to offer Open, In Progress and Done, but rows 20–40 now accept any text. Select D20 and open Data ▸ Data Validation: Allow shows Any value, because a block of cells was pasted over those rows. Select D2 (which still works), copy it, select D20:D40 and use Paste Special ▸ Validation — the list and its arrow come back without touching the values already typed. Drop-downs keep data consistent, so a broken one quietly lets bad entries in. The rule has only a few settings, and checking them in order finds the fault quickly. Work on a copy while you troubleshoot; a repair or cleanup step can change more of the file than the symptom suggests.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “excel drop down list not working”: identify the exact symptom, check the listed causes one at a time, and keep a copy of the file until the fix is confirmed.
Common mistakes
- Pasting normal values into list cells, which silently removes the validation rule.
- Typing a list with the wrong separator: the typed Source uses your regional list separator, which is a semicolon in many locales.
- Expecting the list to reject pasted values — validation only checks typed entries.
- Pointing Source at a range that later has rows deleted, so the list shrinks or breaks.
Frequently asked questions
Why did my drop-down arrow disappear?
Either In-cell dropdown was unticked in the Data Validation dialog, or the workbook is set to hide objects (File ▸ Options ▸ Advanced ▸ For objects, show).
Why does the list accept values that are not in it?
Data Validation checks only what is typed. Values pasted into the cell bypass it, and pasting can also remove the rule entirely.
Why is my list empty or showing the wrong items?
The Source range moved, was deleted or points at the wrong sheet. Reopen Data Validation and correct the Source reference.
Other ways people ask this
People reach this page typing “macros drop down list excel not working” and “drop down not working in excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “macros drop down list excel not working”, “drop down not working in excel” all point at the one operation explained on this page, which is why they all lead here.