Excel & Google Sheets
I've cleaned up more messy spreadsheets caused by typos than I can count — "Complete" in one row, "complete" in another, "Compl." in a third. A dropdown list fixes that in about ninety seconds. If you've ever wondered how those neat little arrows appear in a cell, or how picking a country in one column filters the states in the next, this guide walks you through both, using a real employee task tracker as the example instead of a made-up "Item A, Item B" list.
1. The Quick Answer (30-Second Summary)
In Excel: select your cell(s) → Data tab → Data Validation → set "Allow" to List → type your values or point to a range → OK.
In Google Sheets: select your cell(s) → Data menu → Data validation → set criteria to Dropdown (or "List from a range") → add items → Done.
For a dependent dropdown (where the second list changes based on the first), you name each list after the category it belongs to, then use INDIRECT() in the validation formula so the second dropdown automatically points to the right named list.
2. The Anatomy: How Data Validation Actually Works
Before touching any menus, it helps to understand the three things a dropdown list needs:
| Part | What it means | Example |
|---|---|---|
| Source | Where the list of allowed values lives | A typed list, or a range like Lists!A2:A6 |
| Target cell(s) | Where the dropdown arrow will actually appear | C2:C20 in your task sheet |
| Validation rule | The setting that restricts input to only the source list | "List" in Excel, "Dropdown" in Sheets |
For a dependent dropdown, there's a fourth piece: a named range for each category, and a formula that swaps between them based on what was picked in the first cell. That formula is almost always built around INDIRECT(), which just means "treat this text as a reference." So if cell B2 contains the word "Marketing", INDIRECT(B2) tells Excel or Sheets: go look for a named range literally called Marketing, and use that as the list.
Named ranges cannot contain spaces. "Human Resources" won't work as a name — use "Human_Resources" instead, and make sure the text in your source column matches exactly (more on this in the mistakes section).
3. Step-by-Step Practical Walkthrough
Let's use a real scenario: you manage a small team, and you're building a task tracker. Column B needs a dropdown for Department, and column C needs a dependent dropdown for Assigned Employee — so if you pick "Sales" in B2, C2 should only offer sales team members.
Here's the source data sitting on a second sheet called Lists:
| Sales | Marketing | Support |
|---|---|---|
| Riya Shah | Karan Mehta | Priya Nair |
| Aman Verma | Sonal Patel | Dev Iyer |
| Farah Khan | Ishaan Roy | Neha Joshi |
Part A — Create the Simple (Single) Dropdown
1Select the target cells. Click B2, then drag down to B20 (or wherever your task list ends).
2Open Data Validation.
- Excel: Go to the Data tab → click Data Validation in the Data Tools group.
- Google Sheets: Go to Data → Data validation → click Add rule.
3Set the source. In Excel, set "Allow" to List, then in the "Source" box either type the values directly separated by commas:
...or click the little grid icon and select Lists!A1:C1 if your headers are the department names. In Google Sheets, choose Dropdown as the criteria and either type each item with the + Add item button, or switch to "Dropdown (from a range)" and select Lists!A1:C1.
4Click OK / Done. You'll now see a small arrow appear whenever you click into B2:B20.
Once a cell has a dropdown, you can open it without touching the mouse: click the cell, then press Alt + ↓ in Excel. In Google Sheets, the same shortcut is Alt + ↓ on Windows or Option + ↓ on Mac.
Part B — Turn the Source Table into Named Ranges
This is the step people skip and then wonder why the dependent dropdown doesn't work. Each column in your Lists sheet needs to become a named range that matches the department name exactly.
1Select the Sales names (A2:A4, not the header).
2Name the range.
- Excel: Type
Salesdirectly into the Name Box (top-left corner, next to the formula bar) and press Enter. - Google Sheets: Go to Data → Named ranges → type
Salesas the name → click Done.
3Repeat for Marketing and Support, naming each range exactly after its column header.
If your team list grows, format the source data as an Excel Table first (Ctrl+T), or in Sheets, just leave a couple of blank rows below each name range and extend the range slightly beyond your current data — that way new names get picked up automatically without you having to re-create the named range.
Part C — Build the Dependent Dropdown
1Select C2:C20 (the "Assigned Employee" column).
2Open Data Validation the same way as before.
3Set Allow/Criteria to List, but this time, instead of typing values, use a formula that references the department picked in column B. In Excel's Source box, type:
In Google Sheets, choose "Dropdown (from a range)" and enter:
4Click OK / Done, then copy the validation down the rest of column C so each row references its own row in column B (C2 reads B2, C3 reads B3, and so on).
Now, whatever gets picked in column B automatically controls which names appear in column C for that same row. Change B2 from "Sales" to "Marketing," and C2's dropdown swaps to the Marketing team without you touching a single setting.
4. Excel vs. Google Sheets: What's Actually Different
| Feature | Excel | Google Sheets |
|---|---|---|
| Menu location | Data tab → Data Validation | Data menu → Data validation |
| Naming a range | Name Box or Formulas → Name Manager | Data → Named ranges |
| Typing a quick list | Comma-separated in the Source box | "Dropdown" criteria + Add item button |
| Dependent formula | =INDIRECT(B2) | =INDIRECT(B2) (identical) |
| Invalid-entry warning | Customizable error alert (Stop/Warning/Info) | "Reject input" or "Show a warning" toggle |
| Search-as-you-type in dropdown | Not built in (needs a combo box workaround) | Built in automatically for long lists |
| Multi-select in one cell | Needs VBA/macro workaround | Not native either, but easier via Apps Script |
The good news is the INDIRECT formula behaves identically in both, so once you've learned dependent dropdowns in one app, you already know it in the other. The main difference is just menu names and where "named ranges" live.
5. Three Common Mistakes & How to Fix Them
If column B says "Sales " (with a trailing space) but your named range is "Sales", INDIRECT() can't find a match and the dropdown just shows empty or errors out. Fix: retype the entry cleanly, or wrap the source list itself in =TRIM() before naming the range so stray spaces get stripped automatically.
Named ranges can't contain spaces, but your dropdown list might show "Human Resources" as an option. Excel and Sheets will look for a range literally called "Human Resources" and fail. Fix: either rename the range to "Human_Resources" and adjust accordingly, or use SUBSTITUTE(B2," ","_") inside your INDIRECT formula, like =INDIRECT(SUBSTITUTE(B2," ","_")).
If you build the rule in C2 using an absolute reference like =INDIRECT($B$2) and then drag it down, every row will stay locked to B2 instead of following its own row. Fix: use a relative reference — just =INDIRECT(B2), no dollar signs — so it shifts naturally to B3, B4, and so on as you copy it down.
6. Downloadable Example & Actionable Takeaway
Build it yourself in under 5 minutes: create a "Lists" sheet with your categories as headers and their items below, name each column after its header, then use =INDIRECT(B2) in the second dropdown. That's the entire technique — everything else in this guide is just supporting detail.
If you're managing anything with categories and sub-items — departments and employees, countries and cities, product categories and product names — this same three-part pattern (source table → named ranges → INDIRECT) will handle it.
7. FAQ
Why does my dropdown show a blank list with no options?
This almost always means the named range doesn't exist yet, or its name doesn't match the text in the trigger cell exactly (including capitalization in some cases, and spacing always). Double-check Data → Named ranges (Sheets) or Formulas → Name Manager (Excel) to confirm the name is spelled identically to what's typed in the first dropdown.
Can I let people type a value that isn't on the list?
In Excel, go back into Data Validation → Error Alert tab and change the style to "Warning" or "Information" instead of "Stop" — that lets typed entries through with just a caution message. In Google Sheets, choose "Show a warning" instead of "Reject input" when setting up the rule.
How do I create a dropdown from a list on a different sheet?
In Excel, just reference it directly, like =Lists!$A$2:$A$6, in the Source box. In Google Sheets, when picking "Dropdown (from a range)," click the grid icon and navigate to the other sheet's range — it fully supports cross-sheet references.
My dependent dropdown worked yesterday but now shows an error. What changed?
Someone likely edited or deleted a row in the source list, which can shift a named range if it wasn't set up to auto-expand. Check Name Manager (Excel) or Named ranges (Sheets) to confirm each range still points to valid cells, and consider converting the source table to an Excel Table or leaving buffer rows in Sheets so the range grows with new entries.
Comments