Drop-down lists stop typos and keep your data consistent: everyone picks “In progress” instead of typing “in prog”, “In Progress” or “WIP”. They take a minute to set up with Excel’s Data Validation tool. Here is how to create them, make them grow automatically, and fix common problems.
Quick answer
- Select the cells where you want the drop-down.
- Go to Data → Data Validation.
- Under Allow, choose List.
- In Source, type the items separated by commas, for example
Not started,In progress,Done, and click OK.
Method 1: Type the items directly
Best for short lists that rarely change, like Yes/No or High/Medium/Low.
- Select the cells, for example
D2:D100. - Data → Data Validation → Allow: List.
- Source:
High,Medium,Low - Make sure In-cell dropdown is ticked, then click OK.
Tip: depending on your regional settings, Excel may expect a semicolon instead of a comma between items.
Method 2: Use a list of cells
Better for longer lists, such as departments or product names.
- Type the items in a column on a separate sheet, for example a sheet called Lists, cells
A2:A8. - Select the cells that need the drop-down.
- Data → Data Validation → Allow: List.
- Source: click the arrow and select
=Lists!$A$2:$A$8, then click OK.
Method 3: A list that grows automatically
If you often add items, put the list in an Excel Table so the drop-down picks up new items by itself.
- Click inside your list and press Ctrl + T. Name the table Departments (Table Design → Table Name).
- Go to Formulas → Define Name. Name:
DeptList. Refers to:=Departments[Department](the table’s column). - In Data Validation, set the Source to
=DeptList.
Now when you type a new department at the bottom of the table, it appears in every drop-down.
Method 4: Dependent drop-downs
A second list that changes based on the first, for example choosing a Category and then only the Products in that category.
- On the Lists sheet, put each category’s products in its own column, with the category name as the header (for example Office, Electronics).
- Select each product list and name it exactly like its category using the Name Box (left of the formula bar). Names cannot contain spaces, so use
Office_Suppliesrather than “Office Supplies”. - Create the first drop-down in column A with the category names.
- For the second drop-down in column B, use Source:
=INDIRECT(A2).
When someone picks “Office” in A2, B2 shows only office products.
Add a helpful message and error alert
- Input Message tab: shows a short hint when the cell is selected, such as “Choose the task status”.
- Error Alert tab: choose Stop to block anything not in the list, or Warning to allow it after a warning.
Other useful validation rules
| Allow | Use it for | Example |
|---|---|---|
| Whole number | Quantities | Between 1 and 1000 |
| Decimal | Prices | Greater than 0 |
| Date | Valid date ranges | Between 1/1/2026 and 31/12/2026 |
| Text length | Codes or IDs | Exactly 8 characters |
| Custom | Formula rules | =COUNTIF($A:$A,A2)=1 blocks duplicates |
Edit or remove a drop-down
- Edit: select the cells, open Data Validation, change the Source. Tick Apply these changes to all other cells with the same settings to update them all.
- Remove: select the cells, open Data Validation, click Clear All.
- Find them: Home → Find & Select → Data Validation highlights every cell that has a rule.
Common problems
- Pasting removes the drop-down: normal paste overwrites validation. Use Paste Special → Values instead.
- Blank items in the list: your source range includes empty cells. Use a Table (Method 3).
- Old wrong entries are still there: validation only checks new entries. Use Data → Data Validation → Circle Invalid Data to find them.
FAQ
Do drop-down lists work in Google Sheets?
Yes: select the cells, then Insert → Dropdown (or Data → Data validation). You can also colour each option.
Can I make the drop-down searchable?
In Microsoft 365, drop-down lists now suggest matching items as you type. In older versions, the list is not searchable.
How do I create a drop-down list in Excel?
Select the cells, go to Data → Data Validation, choose List under Allow, and type the items separated by commas or select a range of cells. Click OK.
How do I add items to an existing drop-down list?
If the list comes from cells, add the new item to that range (or to an Excel table, which grows automatically). If you typed the items, edit them in Data → Data Validation.
How do I remove a drop-down list?
Select the cells, open Data → Data Validation and click Clear All.
Related guides
- Free project tracker template (uses drop-downs for status and priority)
- Excel conditional formatting: 10 practical examples
- XLOOKUP in Excel: The Complete Beginner Guide
- 25 Excel Formulas Every Office Worker Should Know
- SUMIFS and COUNTIFS Explained with Real Examples
- Excel Tables (Ctrl + T): Why and How to Use Them
- Named Ranges in Excel: Make Formulas Easier to Read
1 thought on “Excel Data Validation: Create Drop-Down Lists”