Step 1 — Prepare your source list
Put the values you want in the dropdown into a column on any sheet. Same workbook, different workbook, separate sheet — Excel doesn't care, as long as it can reach the range.
A good source list has three properties:
- Every value is unique. Duplicates technically work but they appear twice in the dropdown, which looks broken and confuses users.
- No leading or trailing spaces.
NorthandNorthare two different entries to Excel — they'll both appear in your dropdown, and anyVLOOKUPthat depends on this column will silently miss rows. - No invisible characters. Values pasted from web pages or PDFs often contain non-breaking spaces (
U+00A0) that look identical to a regular space. The dropdown will show both, and downstream formulas will fail.
Pro Tip
Your source list must be unique and have no leading or trailing spaces. If you're pasting from an existing report or web page, 90% of dropdown problems trace back to this single issue. Always clean the source range before building data validation on top of it.
What “dirty” vs “clean” looks like
Both columns below were pasted from the same source report. Only the right one will produce a working dropdown.
Dirty source list
| Region | Why it's broken |
|---|---|
| North | ok |
| North | trailing space |
| north | case duplicate |
| South | ok |
| East | ok |
| East· | NBSP suffix |
| West | ok |
Dropdown shows 7 items for what should be 4 regions. Users pick “North” or “north” based on which one they happen to click. VLOOKUP downstream fails on the ones with trailing space.
Clean source list
| Region | Status |
|---|---|
| North | unique, trimmed |
| South | unique, trimmed |
| East | unique, trimmed |
| West | unique, trimmed |
Dropdown shows the four regions, once each. Filtering, sorting, and lookups behave consistently.
Warning
Does your drop-down have duplicate values or weird formatting? Drop-down lists fail if your source data is “dirty.” Before you set up Data Validation, clean the source range — strip the invisible characters and de-duplicate. Two free tools handle this in seconds, both browser-based, no upload:
Step 2 — Select the target cell
Click the cell where you want the dropdown to appear. You can also select a range — every cell in the selection gets the same dropdown. This is the standard way to apply a dropdown to an entire column (e.g. B2:B100 for a 99-row data-entry form).
Step 3 — Open Data → Data Validation
On the Excel ribbon, switch to the Data tab and click Data Validation (the icon usually shows a tick inside a box). The Data Validation dialog opens with three tabs: Settings, Input Message, Error Alert. You only need the first one.
Shortcut: press Alt + A + V + V on Windows to open Data Validation without leaving the keyboard.
Step 4 — Choose “List” and pick the source range
- In the Allow dropdown, choose List.
- In the Source field, either:
- Click the little arrow icon and select the cleaned source range with your mouse, OR
- Type the values directly, comma-separated:
North,South,East,West. Good for short fixed lists; bad for anything that needs to update.
- Make sure In-cell dropdownis checked (it's on by default).
- Click OK.
Click the cell you applied the validation to — a small arrow appears on the right edge. Click it; your dropdown opens with the values you specified.
Common problems and what causes them
- Dropdown shows duplicates. Your source range contains exact duplicates, or near-duplicates with trailing spaces / case differences / invisible characters. Run the source through the Duplicate Remover with case-insensitive + ignore-whitespace turned on.
- Dropdown is empty. Source range points at an empty area, or includes blank cells. Trim the range and re-apply.
- Error: “The list source must be a delimited list, or a reference to a single row or column.” You picked a range that spans multiple columns. Pick one column only.
- User can type any value, dropdown doesn't enforce. In the Data Validation dialog, switch to the Error Alert tab, set Style to Stop, and tick “Show error alert after invalid data is entered.”
- Filter / sort / pivot fails on the column with the dropdown. Usually a sibling problem — there are merged cells elsewhere in the data range. See our guide on fixing the “merged cells need to be the same size” error.
Bonus — making the dropdown grow when you add new values
By default, if your source range is A2:A10 and you add a new value at A11, the dropdown won't pick it up — the range is fixed. Two ways to fix this:
- Convert the source to a Table (Ctrl+T). In the Data Validation Source field, reference the table column with
=INDIRECT(“TableName[ColumnName]”). The dropdown auto-grows when you add rows. - Use a dynamic-array formula on Excel 365 / Excel for the web: point the Source at
=A2#if your source is a spilled formula like=UNIQUE(A2:A1000).