How to Make a Drop-Down List in Excel and Google Sheets
Select the cells, then Data → Data Validation → Allow: List, and point Source at the range holding your options. That's the whole job, and it takes about twenty seconds.
What follows is everything that decides whether the drop-down still works in six months: where the options should live, how to make a second list depend on the first, and the one way validation quietly stops protecting you.
The category on this row is Plant, so the item list offers the five plant items and nothing else. Change the category and the list changes with it.
Three places the options can come from
| Source | Looks like | Use it when |
|---|---|---|
| Typed straight in | Yes,No,Not applicable | Two or three options that will never change |
| A range | =Lists!$A$2:$A$5 | Almost always |
| A named range | =CategoryList | The list is used in more than one place |
The typed version is tempting and it's a trap. Those options exist only inside a dialog box nobody thinks to open. Six months later someone needs a fourth option, can't find where the three live, and adds a column of free text beside your drop-down.
Put the options in cells. It's the same argument as keeping a threshold in one referenced cell rather than typing it into 150 formulas — anything you might change should have one visible home.
⚠️ If you do type the list, the separator is your system's list separator. That's a comma in most setups but a semicolon in many European locales. If Yes,No shows up as a single option, that's why.
Naming the range
Select the options, then Formulas → Define Name, and give it a name with no spaces. In Data Validation, the source becomes =CategoryList.
Worth the extra step for three reasons. The source reads as words rather than coordinates. Inserting rows into the list updates the name automatically. And it's the only way the dependent drop-down below works at all.
Lists that grow on their own
A fixed range like $A$2:$A$5 won't include a fifth option added underneath. Turn the list into a Table with Ctrl+T and it extends itself as you type.
There's a catch worth knowing before you try it: Excel won't accept =Table1[Category] typed directly into the Source box. Define a name that refers to the table column, then use the name:
Name: CategoryList
Refers to: =Table1[Category]
Source: =CategoryListThe name is accepted where the direct reference isn't, and the list now grows by itself.
A second list that depends on the first
Pick Materials in one column and the next column offers only materials. This is the part people come looking for, and it rests on one function:
=INDIRECT($B2)INDIRECT turns text into a reference. If B2 contains the word Materials, and a named range called Materials exists, then INDIRECT($B2) resolves to that range.
Which sets the whole requirement: every option in the first list must exactly match the name of a range holding its sub-options.
- On a Lists sheet, put each category's items in its own column.
- Name each column exactly as the category is spelled.
- Name the categories themselves as a separate range.
- Validate the first column against the category list.
- Validate the second with
=INDIRECT($B2), written for the first row of the selection.
Note $B2 — column locked, row free. Each row then reads its own category.
Two things that will bite
Names can't contain spaces. A category called "Job board" cannot have a range named Job board. Name it Job_board and translate in the formula:
=INDIRECT(SUBSTITUTE($B2, " ", "_"))Names also can't start with a number or be a valid cell reference — a category called A1 will not work.
Changing the category doesn't clear the item. Choose Materials, pick Cement, then switch the category to Plant. Cement stays in the cell, now invalid and still looking perfectly normal. Data validation checks values as they are entered, never afterwards.
There's no fix within validation itself. Either accept it and check occasionally, or flag it with a conditional formatting rule that colours any row where the item isn't found in its category's column.
The thing to know about validation
Pasting bypasses it entirely. Copy a cell from anywhere and paste it over a validated cell, and the value goes in whatever it is — no warning, no error. Worse, the paste often replaces the validation rule too, so the drop-down disappears from that cell.
This matters because pasting is how bad data actually arrives in a spreadsheet. Someone pastes forty rows from an email and half your drop-downs are gone.
Two defences. Teach people to paste with Ctrl+Shift+V (values only), which leaves the rule intact. And run Data → Data Validation → Circle Invalid Data occasionally, which draws a red ring around every cell breaking its rule — including cells that were already wrong before the rule existed.
The corollary is worth stating plainly: a drop-down is a convenience, not a guarantee. Any formula reading a validated column still needs its IFERROR.
The settings that matter
| Setting | What to do with it |
|---|---|
| In-cell dropdown | Leave ticked. Unticked, the rule still restricts entries but no arrow appears — which looks broken. |
| Ignore blank | Leave ticked so empty cells are allowed. Untick it and every blank row complains. |
| Input Message | A tooltip shown on selection. Good for a unit — "in metres" — and ignored otherwise. |
| Error Alert: Stop | Refuses the entry. The default, and right for codes and categories. |
| Error Alert: Warning | Asks, then allows. Right when the list covers most cases but not all. |
| Error Alert: Information | Tells you and allows. Almost never what you want. |
Write the error message in the Error Alert tab. "Choose a category first, then pick an item from it" beats Excel's default, which says only that the value doesn't match the restrictions.
Does this work in Google Sheets?
Mostly, and the setup is friendlier. Data → Data validation → Add rule → Dropdown (from a range). Options appear as coloured chips by default, and you can set a colour per option — which Excel can only imitate with conditional formatting.
Dependent drop-downs work the same way through INDIRECT and named ranges (Data → Named ranges).
One real difference: Sheets can't validate against a list in a different file. Excel can, badly. In both, copy the list into the same file.
When it goes wrong
No arrow appears
In-cell dropdown is unticked, or the sheet is protected and the cell locked.
"The Source currently evaluates to an error"
Nearly always a named range that doesn't exist or is spelled differently. Check Formulas → Name Manager against what's actually in the source box.
The dependent list is empty
The category text and the range name don't match exactly — a space, a plural, different capitalisation of a word that matters. Or the first column is blank, in which case INDIRECT has nothing to resolve.
The dependent list shows the wrong category's items
Anchoring. The formula needs $B2, and it must be written for the first row of your selection. Written for row 2 while the selection starts at row 5, every row reads three rows off.
Blank options at the end of the list
The source range is larger than the data in it. Trim it, or use a Table so it sizes itself.
The drop-down vanished from some cells
Someone pasted over them. Reapply the rule to the column.
Common questions
Can I hide the sheet holding the lists?
Yes — right-click the tab and hide it. Validation keeps working against a hidden sheet, and named ranges make the source readable without needing to look at it.
How many options can a drop-down hold?
More than is usable. Past about thirty, scrolling is worse than typing — consider splitting into a dependent pair instead.
Can one cell allow several selections?
Not without VBA in Excel. Google Sheets does support it natively on a dropdown rule. If you need multi-select in Excel, use one column per option with Yes/No, which is easier to count later anyway.
Can the list depend on something other than the cell beside it?
Yes — INDIRECT takes any reference. The pattern is identical whether the driving cell is one column over or on another sheet.
Why does my dependent list break when I insert a row?
The named ranges shifted but the fixed references didn't, or the reverse. Named ranges defined over a Table survive row insertion; fixed ranges often don't.
The short version
- Data → Data Validation → List, source pointing at cells rather than typed text.
- Name the range. It reads better, survives edits, and dependent lists require it.
- A Table plus a named range gives you a list that grows on its own.
=INDIRECT($B2)makes the second list follow the first — column locked, row free.- Range names can't contain spaces.
SUBSTITUTEaround INDIRECT handles it. - Changing the first choice doesn't clear the second. Validation never re-checks.
- Pasting bypasses validation and often deletes it. Keep the IFERROR in your formulas.
Next steps: a drop-down on a code column is what stops a price lookup returning #N/A in the first place, and every template on this site uses one — the project tracker drives its phases and owners this way. To find entries that got in before the rule did, COUNTIF will tell you what doesn't belong.
Comments
Post a Comment