Drop-down lists in Excel and Google Sheets: yes/no, from a list, from a range, dependent

A drop-down is data validation with a list. That one feature does four jobs in a template: it stops typos, it makes formulas reliable, it documents the allowed values, and it turns a plain cell into a checkbox that works in every app. Here is each way to set one up and when to use it.

Contents

Why a template wants drop-downs

A formula like =COUNTIF(F8:F157,"Done") only works if every done task says exactly Done — not done, not Done with a trailing space, not Finished. A drop-down makes that true without asking anyone to be careful. In our workbooks every categorical column is a drop-down for that reason: shift codes in the rota, P/A/L/E/H in the attendance grid, category and priority in the checklist.

1. The quickest: a typed list

Excel: select the cells → Data → Data Validation → Allow: List → Source:

source box

High,Medium,Low

Google Sheets: select the cells → Data → Data validation → Add rule → Dropdown → type the options one per line.

Use this for lists that will never change — Yes/No, Monday/Sunday, three priorities. Excel caps a typed source at 255 characters; past that, use a range.

2. From a range on the same sheet

Source: =$M$7:$M$20 (Excel) or Dropdown (from a range) → M7:M20 (Sheets). Blank cells in the range are skipped in the menu, so leave room to grow. This is what the free version of our calendar does: the event list sits on the same sheet, and the category cells validate against a small block to the right.

3. From a named range on a Setup sheet — the template way

Put the list on a sheet called Setup, name the range, and point every drop-down at the name:

Excel: Formulas → Define Name

Name:       cats
Refers to:  =Setup!$A$8:$A$19

Data Validation → List → Source:   =cats

Three things you get for free:

One place to edit. Add a category on Setup and every drop-down in the workbook has it.
Formulas can use the same name. =COUNTIF(Tasks!C:C, cats) is impossible, but =COUNTIFS(Tasks!$C$8:$C$157, $A5) where A5 is a Setup value is exactly how the Board sheet in the checklist counts per category — the list and the counts come from the same cells.
It survives sorting and inserting. A named range stretches when you insert rows inside it.

Google Sheets: Data → Named ranges, then in the validation rule choose Dropdown (from a range) and type the name. Sheets also lets you colour each option as a chip — a nice touch that Excel doesn’t import, so don’t rely on it.

4. Yes/No, and the ✓ that works everywhere

Excel 365 has real checkboxes (Insert → Checkbox). Excel 2016–2021 don’t; Numbers has its own; Google Sheets has its own. A checkbox column that opens the same way in all of them is a drop-down with one option:

one-item list

Source:  ✓        ← the tick character, U+2713

Count done:   =COUNTIF(F8:F157,"<>")      any mark counts
Or by status: =COUNTIF(G8:G157,"Done")    if a status column reads the tick

Allow blank (the default) so the cell can be cleared. Any mark — an x typed over it — still counts, because the formula tests for non-empty rather than for the tick itself.

For a true Yes/No: Yes,No as the list, and =IF(D5="Yes", …) downstream. Avoid TRUE,FALSE as a typed list: Excel turns the strings into booleans in some locales and not others.

Country → City, Category → Subcategory. The portable way (Excel and Sheets) is INDIRECT on a named range whose name equals the parent value:

Setup sheet

A            B            C
Work         Home         Money       ← row 1: the parent list, named  cats
Sprint       Garden       Invoices
Report       Repairs      Tax
Review       Cleaning     Insurance

Name  A2:A10  →  Work      (the range name is the parent value)
Name  B2:B10  →  Home
Name  C2:C10  →  Money

validation on the child cell (parent in C8)

Source:  =INDIRECT($C8)

Rules: the parent values must be legal range names (no spaces — use Work_Travel and show it that way), and the child cell doesn’t reset when the parent changes, so add a conditional-format rule that flags mismatches: =COUNTIF(INDIRECT($C8),$D8)=0.

Numbers doesn’t support INDIRECT in validation; there the dependent menu becomes a plain cell. If Numbers users matter, keep the second level as a flat list instead.

6. Messages and the “not in list” error

Two tabs in the Excel dialog most people skip. Input Message shows a tooltip when the cell is selected — write what the value means (“M morning · E evening · N night”). Error Alert is what appears on a wrong entry; set the style to Stop for codes a formula depends on, and Warning for lists where a new value is sometimes right. Sheets has the same pair under Advanced options.

What survives where

	Excel 2016+	Google Sheets	Numbers
Typed list	yes	yes	becomes a Pop-Up Menu format
Range / named range	yes	yes	values kept, menu dropped
✓ one-item list	yes	yes	Pop-Up Menu
`INDIRECT` dependent	yes	yes	no
Colour chips per option	no	yes	no
Input / error messages	yes	yes	no

The row that matters is the second: Numbers imports the cells and their values, but a validation that points at a range doesn’t come across. That’s fine for a template — the formulas still work, users just type — but it’s why we never make a formula depend on the menu existing.

Three mistakes we’ve fixed in our own files

A whole column as the source (=Setup!$A:$A). The menu shows a thousand blanks and Google Sheets slows down. Bound the range: $A$8:$A$19.
The source on a hidden sheet, then the sheet deleted. The drop-down turns into “The source currently evaluates to an error” for everyone. Keep Setup visible and say so in the README.
Typed list for values a formula tests. Someone edits the list to add Med, the formula still looks for Medium. Use a named range and have the formula read the same cells — the Board sheet pattern above.

The checklist, the rota and the attendance grid in the catalogue all use pattern 3, with the lists on Setup and every count reading the same cells the menus do.