You build a working drop-down list in Excel with the Data Validation dialog: select the cell, open Data > Data Validation, choose "List" under Allow, and point the Source box at your allowed values. The whole job takes about 90 seconds for a short list typed directly into the dialog, or 3 minutes if you point it at a range on a helper sheet. Excel's built-in option holds up to 32,767 characters in a single Source box, which is far more room than most people need.
Why Data Validation Is the Only Excel Drop-Down You Should Use
Excel technically has two ways to produce a clickable list of choices, and only one of them writes a value into the cell. The other is the Form Control combo box from the Developer tab, which draws a floating control on top of the grid and links its output to a separate cell. That distinction matters more than it sounds.
A Form Control lives on the drawing layer. Copy a row containing one and it may not travel the way you expect. Move columns and the control stays put while its linked cell shifts. Data Validation, by contrast, is a cell property, so it copies, fills, sorts, and survives file conversion as part of the cell itself. For a reader who just wants a fixed set of choices in a sheet, Data Validation is the right tool almost every time.
The choice list also travels with the file in a way Form Controls do not when you export to CSV or share through a web version of Excel. That portability is why most templates you inherit use validation.
One boundary worth stating up front: data validation is an input control, not security. Anyone can copy a value into the cell from elsewhere in the sheet, and Excel will accept it. If you need to genuinely lock the entry, you combine validation with worksheet protection, which I cover below.
How to Create a Drop-Down List in Excel Using Typed-in Values
This path is the fastest and works in every desktop version from Excel 2010 forward, including Microsoft 365. You type the list straight into the dialog. Use it when you have five or six fixed options and no reason to store them anywhere else.
1: Select the cell or cells that should receive the list. To apply it down a whole column of entries, select the range from the first data cell to the last, such as C2:C200. 2: On the ribbon, open the Data tab, then click Data Validation in the Data Tools group. 3: In the Settings tab of the dialog, set Allow to List. 4: Click into the Source box and type your values separated by commas, with no spaces around the commas. For example: Yes,No,Pending. 5: Leave In-cell dropdown checked. Tick Ignore blank if the cell should be allowed to stay empty. 6: Click OK. Click any selected cell and the arrow appears at its right edge.
The comma is the separator on every English-language Windows install I have seen, but Excel follows your regional list separator. On many European systems that character is a semicolon, so "Yes;No;Pending" is correct there. If your list collapses into one long option, that setting is the cause, and you fix it through Windows regional settings rather than inside Excel.
The Source box caps at 32,767 characters, so typed lists are for short fixed sets. Keep the capitalization and spelling consistent with how you want the chosen value to read downstream, because that exact text is what lands in the cell.
Pointing the List at a Range So It Updates Automatically
Hard-coded lists rot. The moment someone adds a fourth status or a new department, you are back in the dialog editing a text string. A range-based source fixes that permanently, because the drop-down reads whatever currently sits in those cells.
Build your list on a second worksheet β call it Lists β with one value per cell going down, say A2:A10. Then select your input cells, open Data Validation, set Allow to List, click the Source box, and either type the reference with an equals sign, like =Lists!$A$2:$A$10, or click the arrow at the right of the Source box and drag over the range on the sheet. Absolute references with the dollar signs matter: without them, copying the validated cell sideways shifts the reference and the list breaks.
Now the payoff. Type a new entry at the bottom of your Lists column, and any drop-down pointing at a range that already includes that cell shows it immediately. Excel's own documentation for the Source box recommends extending the reference a few rows beyond your current data on the helper sheet so additions land inside the reference without you touching validation again.
The trade-off is that a referenced list now depends on another sheet existing and being intact. Delete or rename Lists and every validated cell in the workbook throws the same error, "The Source currently evaluates to an error." That message is your cue that the reference broke, not that the validation itself is wrong.
Named Ranges and Dynamic Lists That Grow Without Editing
Named ranges make the reference readable and portable. Select your helper column values, type a name like StatusList into the Name Box to the left of the formula bar, press Enter, then use =StatusList as the Source. The advantage shows up when you copy the sheet or hand it to a colleague: the name travels and the validation still resolves. You can also reference a name across sheets without worrying about the sheet name changing.
For a list that grows on its own, wrap the range in an Excel Table first. Select your helper values, press Ctrl+T, confirm the range has headers, and then reference the body column with structured syntax such as =Table1[Region]. Every new row typed under that column extends the table and the drop-down picks it up. This avoids the extend-the-reference-by-a-few-rows trick entirely.
Excel 365 subscribers have a cleaner option: point the Source at a spilled dynamic array, like =UNIQUE(A2:A100) entered on a helper sheet if that range names non-option noise you want filtered out. Confirm your build supports spilled ranges before relying on it, since older perpetual versions reject the reference.
A caution for anyone maintaining templates through VBA: name collisions break references silently. Two ranges both called StatusList in different scopes will resolve to whichever Excel prefers, and the drop-down may quietly point at the wrong set of values.
Using Named Ranges for the Same List in Multiple Places
Reusing one validated list across a workbook deserves its own note, because doing it wrong is common. If you plan to apply the same choices to ten columns on four sheets, define the list once and reference it by name everywhere, rather than recreating the range reference each time.
The procedure is short: build the values on a helper sheet, select them, name the range through the Name Box or Formulas > Define Name, then in Data Validation use =YourName as the Source. Every input cell that needs those choices gets the identical reference. Update the values in one place and all ten columns follow.
If you later move the helper sheet to a different workbook, expect breakage. Named-range references to values outside the current file are fragile and often fail silently on other people's machines. Keep the list values inside the same workbook as the inputs.
For version control, be aware that a name pointing at an empty range produces a drop-down that opens and shows nothing, which reads to users as a broken control. Check that the named range contains data before shipping the file.
Typing When Your List Hides Behind a Validation Warning
Half the complaints about drop-downs in Excel are not about the list at all β they concern what happens when someone tries to enter something else. Once validation exists, Excel rejects off-list entries with "The value you entered is not valid. A user has restricted values that can be entered into this cell," unless you turn that guard off.
When the value you are typing is simply not visible, open Error Alert in the Data Validation dialog and clear Show error alert. The cell then accepts anything, and the drop-down becomes a suggestion rather than a rule. This is the right setting for a column where most entries come from the list but occasional exceptions are legitimate.
The warning itself is adjustable. In Error Alert you can pick Stop, Warning, or Information. Stop blocks the entry outright. Warning and Information allow the off-list value through after a click, which is useful when you want the picker but cannot predict every legitimate entry. You can also add a short message explaining why the restriction exists.
There is a companion setting worth using: Input Message. Enable it and a small tooltip appears when the cell is selected, telling the user what the list is for. Both messages are part of the same dialog, so you set them while you are already there.
Where the Drop-Down Breaks: Merged Cells, Deleted Rows, and Cross-Sheet Links
The most frequent failure I encounter is a reference to a cell that got deleted or moved. If your Source points at a single cell, say =Sheet2!$B$5, and someone deletes row 5, the validation does not follow the value to its new home. It returns the evaluate-to-error message, and every cell using it starts rejecting valid entries. Referencing a multi-cell range is more forgiving, but deleting enough of that range triggers the same outcome.
Merged input cells produce a subtler problem. Drop-downs work fine in merged cells, but the validation applies only to the top-left cell of the merged region, so a row of merged inputs may accept an off-list value silently in the hidden cells behind it. Avoid merging input cells you intend to validate.
Edits that change options are another trap. Suppose a list contains "Approved" and someone renames the option on the helper sheet to "Approved by Manager." Existing cells that already hold the old text stay put and now fail validation, giving the impression that the list is broken when the data is simply stale.
Last, be aware that validation applied to a whole blank column limits future entries in that column only. Anything already typed below or to the side of your selected range passes unnoticed. Confirm the exact range in the dialog before you click OK, since re-editing a list usually means selecting all the cells that should share it.
Cross-Checking with the Settings You May Have Missed
A few options in the dialog do more than they appear to. They are the difference between a drop-down that works for you today and one that keeps working when someone else opens the workbook next month.
| Setting | Where it lives | What it changes | Common default |
|---|---|---|---|
| In-cell dropdown | Data Validation > Settings | Whether the arrow appears in the cell at all | Checked |
| Ignore blank | Data Validation > Settings | Whether an empty cell passes validation | Checked |
| Show error alert | Data Validation > Error Alert | Whether off-list entries get blocked | Checked (Stop) |
| Input message | Data Validation > Input Message | Tooltip that appears when the cell is selected | Off |
| List separator | Windows regional settings | Whether commas or semicolons split typed sources | Comma on English US installs |
| 32,767 character limit | Data Validation > Source | Maximum length of a typed list | Hard cap, no workaround |
Two observations from that table matter for planning. The 32,767-character cap is fixed, so a typed list that approaches it should become a range reference instead. And the "Ignore blank" default trips people up in forms: a cell left empty is accepted even though the list has options, which is usually what you want for optional fields and wrong for required ones.
If you are handing sheets to other people, turning on the input message is cheap insurance. It reduces the question "what goes in this column" without a separate instructions tab.
A Quick Path to Cascading Drop-Down Lists
Cascading lists β where the second list's options depend on the first list's choice β look advanced but are two validations and one helper reference. The first column picks a category; the second shows only that category's items.
Set up a small table on a helper sheet with categories as column headers and their items below each header. Then point the second drop-down at the appropriate column using the INDIRECT function, combined with the first cell, so the second list follows your chosen category. You enter =INDIRECT(SUBSTITUTE(B2," ","_")) as a Source, substituting for named ranges if your category labels contain spaces; without that cleanup, Excel cannot resolve the name.
The setup requires one named range per category, sized to its items. Build those through Formulas > Define Name before adding the second validation, because the INDIRECT call will fail on any missing name. If the sheet has a trailing blank row, the second drop-down opens empty; include only rows that hold real items.
Protecting a Finished Sheet So Nobody Edits the List
Validation alone is soft. Anyone can paste over the cell or delete the helper range, and Excel allows it. Locking the input cells to validation only requires worksheet protection after you validate.
Right-click the cells, choose Format Cells, open the Protection tab, and make sure Locked is ticked. Then review > Protect Sheet with a password if you need it. With protection on, copying a value into a validated cell still works, so this blocks editing of the list and the formula, not every possible paste.
Before turning on protection, check where the helper range lives: unlocked cells are editable even when the sheet is protected. If your helper list sits in an unlocked column, someone can overwrite a value and watch every drop-down in the workbook change. Locking the helper cells and keeping validation on the input cells covers both ends.
Protection is not encryption β a determined colleague can undo it β but it stops accidental breaks, which is the real threat to a shared template.
FAQ
Can a drop-down list show values from another sheet without named ranges?
Yes, since Excel 2010 you can reference another sheet directly with =Sheet2!$A$2:$A$10 in the Source box. Before that version, cross-sheet references required a named range, which is why you will see the old workaround in templates. If the reference fails, check that the sheet name has no spaces or that spaces are wrapped in single quotes.
Why does my drop-down arrow disappear when I click the cell?
The In-cell dropdown box in Data Validation > Settings is probably unchecked, which hides the arrow while still enforcing the rule. Another cause is a cell that shares a merge with a validated cell; the arrow shows only on the top-left cell. Reopen the dialog and confirm both the setting and the exact selected range.
What happens if I add a value to the list after I have used it?
If the Source points at a range or named range that already includes the cell you typed into, the new option appears immediately. If you typed the values directly into the Source box, you have to reopen Data Validation and edit the text string. That gap is the main reason range-based lists age better than typed ones.
How many items can a drop-down list hold?
A typed Source box accepts 32,767 characters total, so the item count depends on how long each entry is. A referenced range can hold up to 1,048,576 rows, but a very long list is unusable without the search behavior Excel does not provide in validation; for long lists, a combo box or a filtered search column works better.
Does the drop-down work on Excel for Mac and Excel for the web?
On Mac, Data Validation sits under the Data tab in the same dialog with the same allow-list workflow, though the icon placement differs from Windows. Excel for the web can display and edit validated cells, but it may not let you create or change the validation rule itself. Build the list on desktop first if the file will be used in a browser.
Can someone bypass the list by pasting into the cell?
Yes. Pasted values overwrite cells without validation checks, which is the single biggest hole in the method. If the restriction must hold, protect the sheet after validating, and keep the helper list locked as well. Protection does not stop everything, but it catches the accidental paste that pure validation misses.
Why did my list suddenly show "The Source currently evaluates to an error"?
That message appears when the Source reference points at a deleted row, a renamed sheet, or an empty named range. The validation rule itself is intact. Trace the reference in the Source box, restore the missing sheet or value, and the drop-down returns without re-entering anything.