How to Create a Drop-Down List in Excel and Google Sheets (Step by Step)
A drop-down list stops typos and keeps your data consistent: "Yes", "yes" and "Y" become one clean value that formulas, filters and charts can count. Both Excel and Google Sheets make it easy, and once you know the basics you can build lists that grow on their own or change depending on another cell.
The quick answer
| Task | Excel | Google Sheets |
|---|---|---|
| Add a drop-down | Data > Data Validation > Allow: List | Insert > Dropdown, or Data > Data validation > Add rule |
| Type the options | Source: Yes,No,Maybe | Criteria: Dropdown, then type each option |
| Use a list of cells | Source: =$H$2:$H$10 | Criteria: Dropdown (from a range), then H2:H10 |
| Colour the choices | Conditional formatting | Pick a colour next to each option |
| Allow several choices in one cell | Not built in | Chip style, then tick Allow multiple selections |
| Block other entries | Error Alert tab, Style: Stop | Advanced options > Reject the input |
| Copy to more cells | Copy, then Paste Special > Validation | Copy, then Paste special > Data validation only |
| Remove it | Data Validation > Clear All | Open the rule and click Remove rule |
Excel: step by step
- Select the cells that should have the list, for example B2:B100.
- Go to Data > Data Validation (in the Data Tools group).
- On the Settings tab, set Allow to List.
- In Source, either type the options separated by commas, such as
Yes,No,Maybe, or select a range of cells that holds the options, such as=$H$2:$H$10. In some regional settings, use semicolons instead of commas. - Leave In-cell dropdown ticked and click OK.
Press Alt+Down Arrow on a selected cell to open the list from the keyboard. The source range can be on another sheet, such as =Lists!$A$2:$A$10, which keeps your option lists out of the way.
Google Sheets: step by step
- Select the cells, then choose Insert > Dropdown. The Data validation panel opens on the right.
- Under Criteria choose Dropdown and type each option, or Dropdown (from a range) and enter the range, such as
Lists!A2:A10. - Pick a colour for each option if you like. It shows as a coloured chip in the cell.
- Under Advanced options, choose whether invalid data shows a warning or is rejected, and pick the display style: Chip, Arrow or Plain text.
- Click Done.
To let people pick several options in one cell, choose the Chip style and tick Allow multiple selections. The cell then stores the choices as text separated by commas, such as "Red, Blue".
A list that grows automatically
If your options change, you do not want to edit the rule every time.
- Excel 365 or 2021 and later: in H2, build the list with a formula such as
=SORT(UNIQUE(FILTER(A2:A500,A2:A500<>""))). Then use=$H$2#as the Source. The # means "the whole spilled range", so new items appear automatically. Our FILTER function guide and remove duplicates guide explain the formulas. - Google Sheets: point the rule at a longer range than you need, such as
Lists!A2:A200. Blank cells in the range are not shown in the list, so you can add options at the bottom later.
Dependent drop-down lists
A dependent list changes based on another cell: choose "Fruit" in A2 and B2 offers only fruits.
In Excel, put each category's items in its own column and give each range a name that matches the category exactly (Formulas > Define Name, no spaces, so use Dairy_Free rather than Dairy Free). Then set the Source of the second list to:
=INDIRECT($A2)In Excel 365 you can skip the names: keep a two-column table of Category and Item, put =FILTER(Lists!$B$2:$B$100,Lists!$A$2:$A$100=A2) in a helper cell, and point the second list at that helper's spilled range with #.
In Google Sheets, a simple method is to place the filtered items for each row in a helper area with the same FILTER formula, then set that row's drop-down to "Dropdown (from a range)" pointing at its helper range. For large sheets with many rows, a short Apps Script is the usual solution.
Use the choices in formulas
Drop-downs are most useful when formulas read them:
- Count how many rows say Yes:
=COUNTIF(B2:B100,"Yes") - Add up amounts for rows marked Paid:
=SUMIFS(C2:C100,B2:B100,"Paid"), see our SUMIFS and COUNTIFS examples. - Colour the whole row when the status changes: see highlight a row based on a cell value.
- With multiple selections in Sheets, count cells that include an option with a wildcard:
=COUNTIF(B2:B100,"*Red*").
Drop-down not working? Common fixes
- No arrow appears (Excel): make sure "In-cell dropdown" is ticked. The arrow only shows when the cell is selected.
- Blank entries in the list: the source range includes empty cells. Shorten it, or use the growing-list formula above.
- "The source currently evaluates to an error": a named range or the INDIRECT reference does not match the category name exactly, often because of a space.
- People can still type anything: in Excel set the Error Alert style to Stop; in Sheets choose "Reject the input". Note that pasting values over a cell can still remove the rule, so protect the sheet if that matters.
- Typed list shows as one long option: your region uses semicolons as separators, so write
Yes;No;Maybe.
Quick FAQ
How do I edit a drop-down list? Select a cell with the list and open Data Validation (Excel) or Data > Data validation (Sheets), then change the source or options.
How do I remove a drop-down list? Excel: Data Validation > Clear All. Sheets: open the rule in the Data validation panel and click Remove rule.
Can a drop-down allow several choices? In Google Sheets, yes: use the Chip style with Allow multiple selections. Excel does not offer it without VBA.
Need a project for your new skill? A drop-down status column is the heart of our Black Friday deal tracker and the Secret Santa generator.