You create a drop-down list in Excel with the Data Validation tool: select the cells, open Data > Data Validation, choose "List" under Allow, then point the Source box at a typed list or a cell range. The whole job takes about 30 seconds. The part that breaks is what happens afterward — when the list lives on another sheet, when someone pastes over it, or when the list changes and you don't want to redo the validation.
This guide runs through three methods (typed list, range reference, named range) with exact menu paths for Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016 on Windows, plus notes for Mac where the menus differ. Then it covers the two failure modes that catch most people: entries longer than 255 characters and lists that won't refresh.
Type Entries Directly Into Data Validation for a Quick In-Cell List
The fastest route works when you have a short, fixed set of options — five or six items you'll never change. The list lives inside the validation rule itself, so nothing else on the sheet needs to exist.
1: Select the cell or cells where the list should appear. Drag to select a whole column range like D2:D200 if you want the list in every row. 2: Go to the Data tab on the ribbon and click Data Validation in the Data Tools group. On a Mac, it's Data > Data Validation. 3: In the Settings tab, open the Allow dropdown and choose List. 4: Click into the Source box and type your entries separated by commas with no spaces: Draft,Review,Approved,Rejected. 5: Check In-cell dropdown is ticked, then click OK.
Excel accepts up to 255 characters in the Source box in this mode, which caps how many entries you can type. The comma is the delimiter on Windows with US regional settings; on machines set to a locale that uses the comma as a decimal separator, Excel expects a semicolon instead. If your typed list produces a single long entry rather than separate options, that regional setting is the cause — switch delimiters.
Failure handling: if you type Draft, Review, Approved with spaces after the commas, Excel keeps the spaces, and the resulting entries won't match a lookup formula that expects "Review". Strip the spaces. If someone later pastes data into the validated cells, Excel usually strips the validation — more on that below.
Reference a Cell Range So the List Can Grow Without Rebuilding It
A range-based list points at cells on the worksheet, so adding a new option means typing it into the source range — no reopening Data Validation. This is the method to use for anything you'll maintain over time.
1: Type your options in a single column on the same sheet, one per cell, with no blank cells in between — say A2:A6 holding the five statuses. 2: Select the target cells, open Data > Data Validation > Allow: List. 3: Click the Source box, then click and drag to select A2:A6. Excel inserts the absolute reference =$A$2:$A$6. 4: Click OK. The dropdown now shows every non-blank entry in that range.
The blank-cell rule matters. If A2:A6 contains a gap at A4, Excel rejects the range with the message "You may not use unions, intersections or array constants for Data Validation criteria." Fill gaps or reference a contiguous block. A named range avoids this entirely.
One advantage range references carry over typed lists: Excel reads the current contents each time the dropdown opens, so changing "Approved" to "Signed Off" in A3 instantly updates every dropdown pointing at that range. Nothing needs re-saving or refreshing. That behavior is what makes range references the right default for shared workbooks.
Point the Source at a Named Range to Cross Sheets Cleanly
Excel's Data Validation dialog refuses a direct reference to another worksheet in the Source box — you'll get an error if you type =Sheet2!$A$2:$A$6. The workaround is a named range, and it also makes your formulas easier to read.
1: On the sheet holding your options (say Sheet2, cells A2:A50), select the range including some spare blank rows for future additions. 2: Click the Name Box to the left of the formula bar, type a name like StatusList, and press Enter. Names can't contain spaces. 3: Go back to the sheet with your dropdown target, select the cells, and open Data > Data Validation > Allow: List. 4: In Source, type =StatusList and click OK.
The named range updates automatically as you add entries to A2:A50, provided the additions fall inside the original selection. If you selected only A2:A6 and later type a seventh option in A7, it won't appear — name a generous block from the start. Excel 365 also offers a dynamic alternative: format the source list as an Excel Table (Ctrl+T), name it, and reference the table name, since structured table references expand automatically when you add a row.
Cross-sheet dropdowns are the single most common stumbling block for people who try to keep their lists on a hidden "Lists" tab. The named range is the supported way to do it. Any tutorial that asks you to type a sheet reference directly into the Source box is describing behavior Excel hasn't allowed for years.
Choose Between Typed, Range, and Named-Range Lists
Each method has a distinct job. Pick by how often the list changes and whether it needs to live on another sheet.
| Method | Characters or entries allowed | Updates when source changes | Works across sheets | Best for |
|---|---|---|---|---|
| Typed list in Source | 255 characters total | No — edit the rule each time | No | Fixed 3–10 item lists |
| Same-sheet range reference | Contiguous cells, no blanks | Yes, instantly | No | Lists you'll maintain |
| Named range or table | Up to 32,767 items per Microsoft's documented limit | Yes | Yes | Shared workbooks, lists on a Lists tab |
The 32,767-entry ceiling comes from the Data Validation documentation's list length limit, a far higher bound than the 255-character cap on typed lists. In practice you'll never approach it, but the gap explains why a typed 40-item list fails while the same 40 items in a range work fine.
Verdict: use the typed list only for something truly static, such as Yes/No or a set of four quarter labels. Default to a same-sheet range for anything else, and graduate to a named range the moment the list lives on another sheet or gets reused in more than one place. If you're building a form others will fill in, the named range is the only one that survives a sheet reorganization without breaking.
Set Input Messages and Error Alerts So Users Know the Rule
The Data Validation dialog has two more tabs most people never open, and they do the work of explaining the dropdown to anyone who didn't build it. The Input Message tab shows a tooltip when a user selects the cell. The Error Alert tab controls what happens when someone types something outside the list.
On the Input Message tab, tick "Show input message when cell is selected," then add a title up to 32 characters and a message up to 225 characters. A line like "Pick a status from the list. Don't type free text." prevents most bad entries before they happen. On the Error Alert tab, choose one of three styles: Stop (blocks the entry outright), Warning (offers Yes/No/Cancel), or Information (shows a message but accepts the entry).
Stop is the right choice for a controlled field like a status column. Warning suits fields where exceptions occasionally make sense. Information is nearly useless — it tells the user something is off but lets them proceed anyway, which defeats the purpose.
Failure handling: Error Alerts don't fire when data is pasted into the cell. A user who copies "approved" (lowercase) from an email and pastes it into a Stop-protected cell will get it accepted, and your lookup formulas will silently miss it. Paste-stripping is covered in the next section because it's the most common way a perfectly built dropdown stops working.
Recognize the Two Ways Your List Quietly Breaks
Dropdowns rarely fail loudly. They fail because the validation gets removed or because an entry slips in that looks right but isn't.
Paste destroys validation. When someone copies a cell from outside the validated range and pastes it into a dropdown cell, Excel removes the validation in most cases. The cell now accepts anything. Microsoft's support documentation acknowledges this behavior and offers no built-in prevention. If you're sharing the file, protect the sheet with the validated range unlocked: Review > Protect Sheet, with "Select locked cells" and "Select unlocked cells" ticked, and the dropdown cells formatted as unlocked first (Ctrl+1 > Protection tab > uncheck Locked).
Case and whitespace mismatches. A dropdown entry "Approved" won't match a pasted "approved" for a VLOOKUP or XLOOKUP. Excel's lookups are case-insensitive by default, so the real culprit is usually a trailing space. If you typed entries with spaces after commas in a typed list, every entry carries a hidden trailing space. Clean it by rebuilding the list from cells and using TRIM in any lookup that references the output.
A third, rarer failure: deleting rows inside a range-referenced list shifts the entries up but the reference $A$2:$A$6 stays put, so a blank appears at the bottom of the dropdown and the last real entry drops off. Named ranges sized generously with spare blank rows absorb this better.
Make Dependent Drop-Downs With the INDIRECT Trick
A dependent dropdown — where the second list's options change based on the first selection — is the most requested extension of this feature. It relies on named ranges whose names match the entries on the parent list exactly.
1: List your parent categories in one range, say A2:A4 holding Region, Product, Channel. 2: For each category, put its options in a separate column and give each column a named range whose name equals the category — the range covering the Region options must be named Region. 3: Create the first dropdown from A2:A4. 4: Create the second dropdown with Source set to =INDIRECT($D2) where D2 holds the first dropdown's cell.
INDIRECT converts the text in D2 into a reference, so selecting "Product" makes the second list read the range named Product. Two traps: a category name containing a space breaks the rule, because Excel names can't contain spaces, and INDIRECT is volatile, recalculating on every sheet change — negligible with a few dozen options, noticeable in a large model.
If any category has no matching named range, the second dropdown shows an error rather than an empty list. Test each category selection after building it. The newer alternative in Excel 365 is the FILTER function or a helper column using =FILTER(OptionsRange, CategoryRange=$D2), which avoids volatile functions but requires the dynamic-array engine.
Fix "The Source Currently Evaluates to an Error" and Other Messages
The single most common error text is "The Source currently evaluates to an error. Continue anyway?" It appears when the Source box references something Excel can't resolve when you click OK — usually a misspelled name, a range containing a blank cell, or a sheet reference typed directly. Click No and fix the reference rather than clicking Yes, which creates a broken dropdown that shows nothing.
If your dropdown arrow simply doesn't appear, check three things: the "In-cell dropdown" box is ticked in Data Validation, the cells aren't merged (validation on a merged cell applies to the whole merged area but the arrow placement confuses users), and the cell isn't in a protected range you can't interact with.
If a dropdown appears but is empty, the source range is blank or the named range points at the wrong sheet. Select the range via the Name Box — if it doesn't highlight the options you expect, that's your answer. Re-point it in Data Validation > Source.
On Excel for Mac, the dialog is at Data > Data Validation with the same Settings tab and the same 255-character cap for typed lists. The named-range route works identically. If a Windows-authored file's validation fails on Mac, check whether it relies on a feature — table-based structured references, for example — that behaves differently across the platform.
Know Where Data Validation Stops Protecting You
A dropdown limits what gets typed into that cell, and nothing more. Knowing the boundary saves you from believing the data is cleaner than it is.
Data Validation does not apply to cells that already contain data when you add the rule — Excel validates new entries, not existing ones. To check for pre-existing violations, use Data > Data Validation > Circle Invalid Data, which draws a red circle around every cell in the validated range that doesn't match the rule at that moment. Run it after adding validation to an existing table.
Validation is also per-sheet, not workbook-wide. Copying a validated cell to another workbook carries the rule if the source range travels with it, but a named range on Sheet2 won't resolve in the new file and the dropdown silently empties. When you ship a template, keep the source list on the same sheet as the dropdown, or include both sheets.
And the boundary that matters most for anyone using the dropdown to feed a pivot table or report: validation ensures only that entries come from the list, not that they're correct. Someone can still pick the wrong item from a perfect dropdown. Data Validation is a guardrail, not a check.
FAQ
Do drop-down lists work in Excel Online and Google Sheets?
Excel for the web supports the same Data Validation dialog with the List option, so typed and range-based lists work, and files round-trip to the desktop app without losing the rule. Google Sheets uses a different path — Data > Data validation > Add rule > Dropdown — and its version survives .xlsx conversion in most cases, though complex named-range setups can break on the way. If you'll share across both, keep the source list as a plain range on the same sheet.
Why does my drop-down show a blank entry at the bottom?
A range reference like $A$2:$A$10 includes every cell in the block, including any blanks below your last entry. Excel renders each blank as an empty option. Fix it by referencing only the filled cells, by using a named range sized to the real data, or by converting the range to an Excel Table and referencing the table name, since table references exclude blanks at the bottom.
Can I make the dropdown ignore blanks and duplicates?
A plain List validation doesn't deduplicate. If A2:A10 contains "Open" twice, the dropdown shows "Open" twice. Build a deduplicated source list elsewhere — in Excel 365, =UNIQUE(FILTER(A2:A100,A2:A100<>"")) produces a clean list automatically — then point the validation at that result range. In older versions you'd need a helper column or manual cleanup.
What's the character limit for entries typed into the Source box?
Microsoft's documentation caps a typed Source box at 255 characters total across all entries, not per entry. A list of twelve 20-character options already exceeds it. Once you hit the limit, Excel truncates or errors, which is the signal to move the list into cells and reference a range instead — that path allows up to 32,767 entries per Microsoft's stated Data Validation limit.
How do I remove a drop-down list without clearing the cell contents?
Select the cells, open Data > Data Validation, and click Clear All, then OK. The current values in the cells stay put; only the rule is deleted. If you want to remove every validation rule in the workbook, there's no single button — use Home > Find & Select > Go To Special > Data Validation to select the validated cells first, then Clear All in the dialog.
Does protecting the sheet really stop people pasting over the list?
Protecting the sheet with the dropdown cells unlocked and everything else locked prevents deletion of the validation rules in those cells, because the protection blocks paste operations that would alter locked formatting and data. It's the only practical protection against the paste-strips-validation problem. The trade-off is that users can then only edit the cells you unlocked, so plan the layout before protecting.
Can the same cell hold a drop-down and a formula?
No. A cell either contains a formula or accepts manual input — data validation applies to manual entry. If you need computed values that follow a dropdown's selection, place the dropdown in one cell and the formula in an adjacent cell that references it, then hide the helper column if needed.