Excel Drop-Down Lists with Data Validation

Excel Data Validation: Create Drop-Down Lists

by

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

  1. Select the cells where you want the drop-down.
  2. Go to Data → Data Validation.
  3. Under Allow, choose List.
  4. 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.

  1. Select the cells, for example D2:D100.
  2. Data → Data Validation → Allow: List.
  3. Source: High,Medium,Low
  4. 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.

  1. Type the items in a column on a separate sheet, for example a sheet called Lists, cells A2:A8.
  2. Select the cells that need the drop-down.
  3. Data → Data Validation → Allow: List.
  4. 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.

  1. Click inside your list and press Ctrl + T. Name the table Departments (Table Design → Table Name).
  2. Go to Formulas → Define Name. Name: DeptList. Refers to: =Departments[Department] (the table’s column).
  3. 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.

  1. On the Lists sheet, put each category’s products in its own column, with the category name as the header (for example Office, Electronics).
  2. 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_Supplies rather than “Office Supplies”.
  3. Create the first drop-down in column A with the category names.
  4. 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.

Next steps

Leave a Comment