You add a drop down in Excel through the Data Validation dialog: select the cells, click Data > Data Validation, set Allow to List, and type items in Source separated by commas or point to a range. That path exists in Excel 2010 through Microsoft 365 and is the only method that works on both Windows and Mac without code. Everything else β typed lists, table references, checkboxes β is a variation on that one dialog.
The boundary matters. If you share the workbook with Excel for the web users, Data Validation lists survive, but checkboxes from the newer Cell Controls group do not render the same way, and VBA-driven lists block editing entirely in the browser.
The 90-Second Fast Path From Blank Cell to Working List
Open a blank sheet and picture the finished state: cell B2 shows a small arrow, and clicking it offers Tea, Coffee, Water. Build that once and you understand every variation below.
1: Select B2. Keep the selection to cells you actually want restricted β a whole column works, but it also blocks free typing in the header row. 2: Go to the Data tab on the ribbon and click Data Validation in the Data Tools group. In Excel for Mac, the same button sits under Data > Data Validation. 3: In the Settings tab, open the Allow dropdown and choose List. 4: Click into Source and type Tea,Coffee,Water. No spaces after the commas unless you want them inside the item names. 5: Leave In-cell dropdown checked, then click OK. Click B2 and confirm the arrow appears.
The first time this fails, it fails silently in one direction: the dropdown shows, but typing something else is also accepted. That means Ignore blank was left on and the Error Alert tab still sits on its default β open the Error Alert tab and set Style to Stop if you want hard enforcement. The second common failure is a comma inside an item, such as "Smith, John." Excel splits that into two entries, so move such lists to a range in cells instead.
Data Validation Versus the New Checkboxes: Which Control Fits
Microsoft added checkboxes to Excel through the Cell Controls group on the Insert tab, rolling out to Microsoft 365 subscribers from 2024 onward. They behave like form controls, not lists. A validation list picks one item; a checkbox inserts TRUE or FALSE into the cell and toggles with a click.
| Feature | Data Validation list | Cell Controls checkbox | Form Control (Developer tab) |
|---|---|---|---|
| Where it lives | Data tab > Data Validation | Insert tab > Checkboxes | Developer tab > Insert > Form Controls |
| Values produced | The item text you typed | TRUE or FALSE | Linked cell number (1 or 0) |
| Multi-select in one cell | No | Yes, across many cells | No |
| Works in Excel for the web | Yes | Partial; editing differs by tenant rollout | Yes, renders as a control |
| Works in Excel 2016/2019 | Yes | No | Yes |
| Best for | Choosing one category from many | Yes/no task tracking | Legacy shared workbooks |
Pick validation when the answer is one of several categories β department, region, status. Pick checkboxes when each row is a task and you want a visible tick. The third column exists because files built before 2010 still circulate in finance teams, and Form Controls survive where ActiveX does not.
Type the List Inline or Point to Cells: The Trade-Off Nobody Explains
Inline lists live in the dialog itself. Range lists live on a sheet. The difference shows up the first time you need to edit 400 rows of validation: inline means reopening the dialog, range means editing one cell.
The 255-character cap is the hard limit for inline sources. Count the characters you type into Source β commas included β and the dialog rejects anything longer with a generic error. A list of twelve department names at roughly 14 characters each lands near 180 characters, so it fits. Twenty longer names will not, and the fix is a range.
Range-based sources also let you use a named range, which is the only way to make the list portable across sheets. Select the cells holding your items, type a name into the Name Box left of the formula bar, press Enter, then put that name in Source. Excel accepts the name without an equals sign, and it resolves on any sheet in the workbook.
One caveat about table references: if your items sit in an Excel Table (Insert > Table), pointing Source at the structured reference works, but the reference text inside the dialog is fixed at creation. Adding a row to the table does not extend the validated range automatically unless you rebuild the source. Named ranges behave the same way β they freeze at the address, so insert a row inside the named block rather than below it.
Six Ways to Build the List, Matched to the Job
Each method below solves a different constraint. Choose by what changes most often: the items, the sheet layout, or the person editing the file.
| Method | Setup path | Best when | Known limit |
|---|---|---|---|
| Inline comma list | Data Validation > List > type items | Under about 15 short items | 255-character source cap |
| Cell range reference | List > Source > select A2:A10 | Items change weekly | Blank cells in range show as blanks in the menu |
| Named range | Formulas > Define Name, then use the name | List reused on several sheets | Does not auto-expand |
| Excel Table reference | Insert > Table, then point at the column | Rows added regularly | Source text stays fixed after entry |
| Dependent (cascading) list | INDIRECT plus named ranges per category | Second list depends on first choice | Names must match exactly, no spaces |
| VBA-driven combobox | Developer tab, macro-enabled file | Custom filtering, large lists | Blocked in Excel for the web |
For most readers the third method wins: name the range, reference the name, and you can move the item block anywhere on the sheet without breaking 200 validation rules. The VBA route only earns its complexity when the list runs past a few thousand entries, where the dropdown becomes unusable anyway.
Build a Cascading Drop Down Where the Second List Follows the First
Cascading lists solve a real problem: country then city, category then product. The standard implementation uses INDIRECT, and it fails in a specific, famous way β "The Source currently evaluates to an error."
1: Put your parent items in A2:A4: Fruit, Vegetables, Grains. 2: Put each child list on its own block, and name each block after its parent: select B2:B5 and name it Fruit, select C2:C5 and name it Vegetables, select D2:D5 and name it Grains. 3: Select the cell for the first list and build a validation list from A2:A4. 4: Select the cell for the second list, open Data Validation, choose List, and type =INDIRECT(E2) where E2 holds the first dropdown. 5: Test by choosing Fruit in E2 and confirming the second cell offers only fruit names.
The error appears when a name in the parent list does not exactly match a defined name β a trailing space, a lowercase letter, or a parent item added after the names were created. Fix it by opening Formulas > Name Manager and comparing letter for letter. Parent items must also be single words; "Leafy Greens" needs an underscore in its defined name.
Copy the Rule Down Without Breaking the Reference
Copying a validated cell with Ctrl+C and pasting down duplicates the rule with relative references adjusted β which is usually what you want for a source range, unless you pointed at a named range, in which case nothing shifts.
The failure case: you paste the rule into rows below the original data, and Excel extends the source range automatically because the original range was directly adjacent. That behavior is helpful once and destructive later, since deleting rows leaves dangling references. Anchor the source with absolute addresses, $A$2:$A$10, or use a name.
To strip a rule without clearing content, select the cells, open Data Validation, and click Clear All. This removes the restriction and leaves the typed values intact β a two-click fix when a colleague asks why their typed entry is being rejected.
Why Blank Cells Appear in the Menu and How to Strip Them
Blank rows inside a source range show as blank lines at the top of the dropdown. The cause is a range that includes empty cells, and the cure depends on why they're empty.
If rows are reserved for future items, ignore blank stays on by default and the menu shows gaps. If the blanks are stray, shrink the range or move the list to a dynamic named range using OFFSET and COUNTA, which is a formula approach that recalculates as items are added. The formula looks like =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1), pasted into Name Manager's Refers to box. It is fussy but it self-maintains.
A cleaner modern answer, available in Microsoft 365 and Excel 2021 onward: convert the list to an Excel Table and reference the table column, then add new items at the bottom of the table. The name expands with the table, though the validation source text itself still needs a one-time update to the table reference.
Cross-Sheet Sources: What Works and What Gets Rejected
Excel rejects a direct cross-sheet reference in the Source box. Type =Sheet2!A1:A10 and the dialog refuses it with "You may not use references to other worksheets or workbooks for Data Validation criteria." The workaround is a named range, which crosses sheets freely.
Define the name while Sheet2 is active so the reference resolves to that sheet, then type the bare name into Source. Named ranges also survive sheet renaming better than raw addresses do, since the name follows the moved block.
The same rule applies to workbooks: external references are not accepted, so copy the list into the same file and point at it. Shared workbooks with validation, a legacy feature, do keep rules when opened in Excel 2016 and later, but editing restrictions differ, so rebuilding on the modern co-authoring model avoids surprises.
Error Alerts, Input Messages, and Sharing Without Frustration
The Error Alert tab controls what happens when someone types a non-list value. Three styles exist: Stop refuses the entry, Warning offers a choice, and Information shows a note and accepts anything. The default is Stop, which is why most failed entries feel like the file is broken.
The Input Message tab shows a tooltip when the cell is selected. Use it to say where the list came from, not what the list is β people can already see the items. A useful message reads "Pick a region; the summary sheet recalculates."
Past a handful of rows, validation rules do not travel with a copied cell into another workbook unless you paste special or copy the whole sheet. If you email a sheet to a colleague and the dropdown vanishes, the rule stayed behind with the source range. Include the item block in the same file and rebuild.
FAQ
Does the dropdown still work if I protect the worksheet?
Yes, provided you enable the option during protection. In the Review tab, click Protect Sheet and confirm "Select locked cells" is checked β validation rules on locked cells still allow the dropdown, and the list itself cannot be edited. Unlock the cells containing the source items first if anyone needs to update the menu, then protect.
Why does my list show duplicates after I copy the rule?
Copying validation into rows that already hold a rule creates a second, overlapping rule only if the first was never cleared; more often the duplicates come from the source range itself. Select the source block and run Data > Remove Duplicates. Then reopen Data Validation on the target cell and confirm the Source range starts and ends on the item block.
Can I make a list that accepts more than one value per cell?
Not through Data Validation. Typing two comma-separated items triggers the Stop alert, because a list rule allows exactly one of the listed entries. Checkboxes across adjacent columns are the supported multi-select substitute, and a helper column using TEXTJOIN can collect a summary.
What is the character limit for a typed list?
255 characters for the whole Source entry, commas included, and Excel counts each comma as one character. A twelve-item list of short category names fits; a list of full department names with locations does not. Move anything longer to a range.
Does Data Validation work in Excel for the web and Google Sheets?
Excel for the web honors existing validation lists and lets you create new ones through the Data tab, though the dialog layout differs slightly. Google Sheets uses Data > Data validation with a Dropdown criterion, and sheets imported from Excel keep their list cells in most cases but lose dependent-list logic built with INDIRECT.
How do I remove a drop down without deleting the data?
Select the cells, open Data Validation, and click Clear All, then OK. The arrow disappears and previously selected items stay as plain text. If the cells are locked and the sheet is protected, unprotect first β the Clear All button is greyed out until then.
Can I sort or filter by the selected item?
Yes. A validation list writes plain text into the cell, so AutoFilter on the column sorts and filters exactly like typed data. The filter menu lists every item that actually appears in the column, not the full validation source, so unused options stay hidden until someone selects them.