A dependent dropdown is one where the available options change based on what you selected in a previous dropdown. The classic example: you pick a country, and the region dropdown updates to show only that country's regions. Or you pick a product category, and the next dropdown shows only products in that category.

Google Sheets can do this natively with the INDIRECT function and named ranges. No code, no Apps Script.

This tutorial covers the complete setup, including the most common points where it breaks.

---

How This Works: The Core Concept

The technique has three parts:

  1. A reference table — a separate area (or sheet tab) where you list the options for each parent value
  2. Named ranges — you name each row or column of options with the exact parent value it belongs to
  3. INDIRECT in a data validation rule — the second dropdown uses INDIRECT(A2) to say "look up the named range whose name matches what's in cell A2"

When the user picks a value in the first dropdown, INDIRECT points to the named range with that same name, and the second dropdown shows those options.

---

Step 1: Build the Reference Table

Create a new tab in your spreadsheet. Name it Lists (or Options — any name works).

In this tab, set up a column for each parent option. Put the parent value in Row 1 and the child options below it:

| A | B | C | |---|---|---| | Furniture | Electronics | Clothing | | Chair | Laptop | Shirt | | Desk | Monitor | Trousers | | Bookshelf | Keyboard | Jacket | | Cabinet | Headphones | Shoes |

Row 1 — the headers — must exactly match the values in your first dropdown. Capitalization matters.

---

Step 2: Create Named Ranges for Each Column

This is where the INDIRECT technique gets its power. Each column of options needs a name that exactly matches its header.

For the Furniture column (A1:A5 in the Lists tab):

  1. Select cells A2:A5 (the options, not the header)
  2. Click Data > Named ranges
  3. In the name field, type Furniture
  4. Click Done

Repeat for each column:

  • Select B2:B5 → name it Electronics
  • Select C2:C5 → name it Clothing

Important: The named range name must match the header row value exactly. If the header says "Electronics" and the named range says "electronics," it won't work.

Named ranges can't have spaces. If your category is "Office Supplies," you can't use that as a named range name directly. See the workaround in the Common Problems section below.

---

Step 3: Set Up the First Dropdown (Parent)

Go to the main data entry area in your sheet (not the Lists tab).

  1. Click the cell where the first dropdown should go — say, B2
  2. Click Data > Data validation
  3. Under Criteria, select Dropdown (from a list)
  4. Type your parent values: Furniture, Electronics, Clothing
  5. Click Save

---

Step 4: Set Up the Second Dropdown (Dependent)

Click the cell where the dependent dropdown should go — say, C2 (one column to the right of the parent).

  1. Click Data > Data validation
  2. Under Criteria, select Dropdown (from a range)
  3. In the range field, type:
Code
=INDIRECT(B2)
  1. Click Save

That's it. When you select "Furniture" in B2, clicking C2 shows Chair, Desk, Bookshelf, Cabinet. When you change B2 to "Electronics," C2 shows Laptop, Monitor, Keyboard, Headphones.

---

Step 5: Apply to Multiple Rows

To set up the same dependent dropdown relationship for rows 2 through 100:

  1. Select C2 (your dependent dropdown cell)
  2. Copy it (Ctrl+C / Cmd+C)
  3. Select C3:C100
  4. Paste (Ctrl+V / Cmd+V)

The data validation rule copies with relative references, so C3 looks at B3, C4 looks at B4, and so on.

Do the same for the parent dropdown: copy B2, select B3:B100, paste.

---

Common Problems

The dependent dropdown shows nothing / "Invalid" after selecting the parent

Most likely cause: The named range name doesn't exactly match the parent dropdown value. Go to Data > Named ranges and check the names you created. Compare them character by character to the dropdown values. Even a trailing space breaks it.

Second cause: You selected the header cell as part of the named range. Named ranges should include only the option values, not the header row. If you included the header "Furniture" in the range, delete the named range and recreate it starting from the row below the header.

My category names have spaces (like "Office Supplies")

Named ranges can't have spaces. The workaround: use underscores in the named range names instead — Office_Supplies — and make the dropdown values also use underscores. If you want the dropdown to display "Office Supplies" without the underscore, this approach doesn't work directly.

The better workaround for multi-word categories: use a helper column. In a separate column, use =SUBSTITUTE(B2," ","_") to convert the selected value to an underscore version. Then use =INDIRECT(D2) (pointing to the helper column) for the dependent dropdown, and name your ranges with underscores.

Changing the parent dropdown doesn't clear the child dropdown

This is expected behavior — Google Sheets doesn't automatically clear a dependent dropdown when the parent changes. The old value stays in the cell, but the dropdown now shows different options. If someone tries to submit the form or process the data, they'll have a child value that doesn't match the new parent.

To clear the child cell automatically when the parent changes, a short onEdit Apps Script can help:

Code
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedCell = e.range;

  // Clear column C when column B changes (adjust column numbers to match your setup)
  if (editedCell.getColumn() === 2) {
sheet.getRange(editedCell.getRow(), 3).clearContent();
  }
}

Paste this in the Apps Script editor (Extensions > Apps Script), save, and it runs automatically. No trigger setup needed — onEdit is a simple trigger.

The dropdown works but shows duplicate entries or extra blanks

Your named range extends beyond the actual data. If you named A2:A10 but only have 4 items, the dropdown shows 4 items plus 6 blank entries. Fix this by resizing the named range to cover only the cells with data.

---

A Note on Larger Datasets

This technique works well for reference tables with up to a few hundred options per category. For very large option lists (thousands of items), the INDIRECT approach can be slow or difficult to maintain. At that scale, Apps Script is the better foundation — but for most real-world use cases, the INDIRECT method handles it fine.

---

What to Build on Top of This

Dependent dropdowns pair well with data validation rules that lock cells once a value is entered, or with conditional formatting that highlights rows based on the selected category.

Don't want to set this up yourself? Describe your categories and options, and it'll be configured for your sheet. Get it installed