How to create a drop-down list in Excel
A drop-down list makes a cell accept only values from a list you define. Instead of "paid", "Paid" and "pd" you get one spelling everywhere, so filters, COUNTIF and PivotTables give correct results.
In short: select the cells, then go to Data > Data Tools > Data Validation. Set Allow to List and enter the range with the allowed values as the Source, for example =$F$2:$F$4. That is your drop-down.
Try it now
The sheet lists invoices. Column C should only accept the three status values in F2:F4. Set up the data validation in the panel below the sheet and check it. Once it is right, you can try the drop-down right in the cells.
Task Create a drop-down list for C2:C7 that only allows the status values from F2:F4.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Amount $ | Status | Status values | ||
| 2 | R-2041 | 1250 | Open | |||
| 3 | R-2042 | 480 | Paid | |||
| 4 | R-2043 | 2300 | Reminded | |||
| 5 | R-2044 | 760 | ||||
| 6 | R-2045 | 1490 | ||||
| 7 | R-2046 | 310 |
Step by step: a drop-down list from a range
- Type the allowed values in a column of their own. A separate sheet named "Lists" keeps them out of the way.
- Select the cells that should get the drop-down.
- Go to Data > Data Tools > Data Validation.
- On the Settings tab, set Allow to List.
- Click into the Source box and select your values with the mouse. Excel fills in something like
=Lists!$A$2:$A$6. - Keep In-cell dropdown checked and click OK.
The dollar signs make every selected cell point to the same list. Without them, the source would slide down one row for each cell.
Best practice: keep the list in a table
Microsoft recommends putting list items in an Excel table. Click inside your list and press Ctrl+T. If you then select the table column's values as the source, every drop-down based on it grows automatically when you add an item at the bottom. With a plain range you would have to widen the source by hand each time.
A central list pays off in another way too: change an entry once, and every drop-down in the workbook offers the new text. Values people already picked stay as they were, though.
Typed entries without a helper list
For short lists like Yes and No you can skip the helper column. Type the entries straight into the Source box, without an equals sign: Yes,No. Separate them with commas. The order you type is the order people see.
Task Create a drop-down list for C2:C7 with the fixed entries High, Medium, Low, in this order. There is no helper list.
| A | B | C | |
|---|---|---|---|
| 1 | Task | Owner | Priority |
| 2 | Quote for Krause Ltd | Jana Weber | |
| 3 | Check travel expenses | Tom Schulz | |
| 4 | Book trade fair stand | Sara Klein | |
| 5 | October monthly report | Jana Weber | |
| 6 | Order printer cartridges | Ali Demir | |
| 7 | Prepare client meeting | Tom Schulz |
Limit numbers, dates and text length
Data validation does more than lists. The Allow box has several types, and the Data box sets the condition, such as between, greater than or less than or equal to.
| Allow | Good for | Example |
|---|---|---|
| Whole Number | quantities, units, days | quantity between 5 and 200 |
| Decimal | amounts, weights, percentages | discount between 0 and 0.2 |
| Date | dates within a period | delivery date in March 2027 |
| Time | times of day | shift start from 6:00 |
| Text Length | codes, IDs, comments | code with exactly 3 characters |
| Custom | your own formula that must return TRUE | =COUNTIF($A:$A,A2)=1 to block duplicates |
"Between" includes both limits. Enter percentages as decimals: 20% is 0.2.
Task The order quantity goes into C2:C7. Only whole numbers from 5 to 200 are allowed. Set up the matching data validation.
| A | B | C | |
|---|---|---|---|
| 1 | Item no. | Item | Quantity |
| 2 | A-117 | Printer paper | |
| 3 | A-104 | Binder | |
| 4 | A-131 | Highlighter | |
| 5 | A-109 | Sticky notes | |
| 6 | A-122 | Ballpoint pen | |
| 7 | A-140 | Envelopes |
Input message and error alert
The dialog has two more tabs. On Input Message you write a short note that pops up when someone selects the cell, such as "Quantity from 5 to 200". On Error Alert you decide what happens after a wrong entry. There are three styles: Stop refuses the value. Warning and Information only show a message, and the value can still be entered. You can also give the alert your own title and text.
Edit or remove a drop-down list
To edit, select a cell with the drop-down and open Data > Data Validation again. Change the source or the limits there. To remove it, select the cells, click Clear All in the same dialog and then OK. The values in the cells stay, only the rule is gone.
Lost track of which cells have rules? Home > Find & Select > Data Validation selects every cell on the sheet that has one.
Dependent drop-down lists
A common wish: the second drop-down should depend on the first. Pick "Paper" in A2, and B2 offers only paper products. The classic way is to build one list per category and give each list a name that matches the category exactly (type it into the Name Box left of the formula bar). The second drop-down then uses =INDIRECT(A2) as its source. INDIRECT turns the text in A2 into a reference to the list with that name. Names cannot contain spaces, so the categories have to be named to match. INDIRECT is not part of the exercises here, but the technique is worth it in any entry form.
Common problems
Blank lines in the drop-down
If you enter a larger source "just in case", like =$F$2:$F$50, the empty cells show up as blank lines in the list. Use exactly the filled cells, or better, a table that grows by itself.
The arrow does not show
The drop-down arrow only appears while the cell is selected. If it is missing even then, In-cell dropdown is unchecked in the dialog.
Pasting wipes out the rule
When you copy a normal cell and paste it with Ctrl+V over a cell with a drop-down, everything from the copied cell comes along, including its lack of a rule. The drop-down is gone. On top of that, Excel only checks entries that are typed: copied or filled values get through without a message. Paste data with Paste Special > Values so that at least the rule survives.
Old values break the rule
A new rule does not check values that are already in the cells. Use the small arrow on the Data Validation button to circle invalid data and find them.
Data validation in German Excel
| English | German |
|---|---|
| Data > Data Tools > Data Validation | Daten > Datentools > Datenüberprüfung |
| Allow: List, Whole Number, Decimal, Date, Text Length | Zulassen: Liste, Ganze Zahl, Dezimal, Datum, Textlänge |
| Source / In-cell dropdown | Quelle / Zellendropdown |
| Input Message / Error Alert | Eingabemeldung / Fehlermeldung |
| Clear All | Alle löschen |
Yes,No | Ja;Nein |
=INDIRECT(A2) | =INDIREKT(A2) |
German Excel separates typed list entries with a semicolon, just like function arguments.
Which Excel versions have drop-down lists?
Data validation with drop-down lists is in every current version: Excel 2016, 2019, 2021, 2024 and Microsoft 365. On a Mac you find it on the Data tab as well. Excel for the web can create drop-down lists too, under Data > Data Tools > Data Validation.
FAQ
How do I add an item to a drop-down list?
If the list is in a table, type the new item below the last row. With a plain range, add the item and then widen the source in the Data Validation dialog. With typed entries, add it right in the Source box.
Can the list be on another sheet?
Yes. Click into the Source box, switch to the other sheet and select the values. Excel adds the sheet name, as in =Lists!$A$2:$A$6.
Why can I still type a value that is not in the list?
Either the error alert style is Warning or Information, which let the value through after a prompt, so switch it to Stop. Or the value was pasted instead of typed, and pasted values are not checked.
How do I count how often each option was picked?
With COUNTIF: =COUNTIF(C2:C100,"Open"). That is exactly why drop-downs help: with mixed spellings the count would be wrong. To color the values, use conditional formatting.