How to make a drop-down list in Excel and Google Sheets: yes/no, dependent, and how to remove one

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 — the short Yes/No drop-down guide has the Sheets steps, the colour chips and the counting formulas. 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.

7. Changing one that already exists: edit, copy, remove

Add an option. If the source is a named range on a Setup sheet, type the new value into the range and every cell that uses it updates — that is the whole reason for pattern 3. If the source is a typed list, you have to reopen the dialog on every cell that has it, which is the reason to stop using typed lists as soon as a list has more than one user.

Copy the menu without the contents. Copy the cell, then paste special:

paste only the rule

Excel:          Home → Paste → Paste Special → Validation
Google Sheets:  Edit → Paste special → Data validation only

An ordinary paste carries the value as well and overwrites what was there. Dragging the fill handle copies the rule too, which is usually what you want down a column.

Remove it. Select the cells, then:

the rule goes, the typed values stay

Excel:          Data → Data Validation → Clear All
Google Sheets:  Data → Data validation → click the rule → Remove rule

Both leave the text that was already chosen sitting in the cells as plain text. That is worth knowing before you clear a column a formula reads: the values survive, so nothing breaks immediately, and the typos start on the next edit.

The arrow only appears when the cell is selected — in both apps. A column that looks plain is not proof the validation is gone; click a cell and look, or use Excel’s Home → Find & Select → Data Validation to highlight every cell that has one.

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.