How to Create Dependent Dropdowns in Google Sheets

To create dependent dropdowns in Google Sheets, name each list range after the option that selects it, then use =INDIRECT() as the second dropdown’s data validation source. Pick “Fruit” in the first list and the second list shows only fruits; pick “Vegetables” and it switches. It’s a two-formula setup.

What you’re building

A dependent (or cascading) dropdown: the second list’s options change based on what’s chosen in the first.

The mechanism is simple once it clicks: the first dropdown’s value becomes the name of a range, and INDIRECT turns that name back into a range reference.

Step 1: Set up the lists

Somewhere out of the way — a separate sheet is cleanest — lay each list out in a column with a header:

A1: Fruit        B1: Vegetable     C1: Drink
A2: Apple        B2: Carrot        C2: Coffee
A3: Banana       B3: Peas          C3: Tea
A4: Cherry       B4: Spinach       C4: Water

Step 2: Name each range after its header

This is the step that makes it work.

1. Select A1:A4 (the Fruit header and its items).
2. Go to Data → Named ranges.
3. Name it Fruit — matching the header exactly.
4. Click Done.
5. Repeat for B1:B4 named Vegetable, and C1:C4 named Drink.

The range name must match the dropdown value exactly — same spelling, same capitalisation, no spaces. If the header says Vegetable and the dropdown option says Vegetables, INDIRECT fails with #REF!.

Naming tip: select the header cell and the items, so the name covers the whole column. Google Sheets’ named ranges can be referenced directly and won’t include the header as a choice — because the validation source starts from row 2.

Step 3: Create the first dropdown

1. Select the cell for the first choice (say E2).
2. Data → Data validation → Add rule.
3. Set Criteria to Dropdown.
4. In the source box, either type the options (Fruit,Vegetable,Drink) or select the header range A1:C1
5. Click Done.

Now E2 offers Fruit / Vegetable / Drink.

Step 4: Create the dependent second dropdown

1. Select the cell for the second choice (say F2).
2. Data → Data validation → Add rule.
3. Set Criteria to Dropdown.
4. In the source box, type:

   =INDIRECT(E2)

5. Click Done.

Pick “Fruit” in E2 and F2 offers Apple/Banana/Cherry. Change E2 to “Drink” and F2 updates to Coffee/Tea/Water.

How INDIRECT is doing it

INDIRECT converts text into a range reference.

• E2 contains the text "Fruit".
• INDIRECT(E2) reads that text and looks for a named range called Fruit.
• It resolves to the range A1:A4.
• The data validation uses that range as its option list.

That’s the whole trick — the first dropdown’s value is the pointer to the second dropdown’s list.

Applying it to many rows

Set it up once, then copy:

1. Select E2:F2 (both dropdown cells).
2. Copy (Ctrl + C).
3. Select the rows below and paste.

Each row’s INDIRECT refers to its own E cell, because Google Sheets adjusts relative references as you paste — as long as you wrote =INDIRECT(E2) without dollar signs. If you wrote $E$2, every row would read the first row’s choice, which is the usual cause of “all my rows show the same list.”

Common problems

Making it cleaner

• Hide the list sheet — right-click the tab → Hide sheet. INDIRECT still reaches it.
• Add an “unknown” option so a blank first choice doesn’t look broken.
• Reject invalid input in the validation settings so a stale second value can’t survive a first-dropdown change.

If you haven’t built a basic dropdown before, start with how to create a drop-down list in Excel for the underlying idea (named ranges work the same way), and Google Sheets formulas for beginners for the formula basics.

FAQ

How do I make dependent dropdowns in Google Sheets?

Name each list range exactly after its category, then set the second dropdown’s source to =INDIRECT(firstCell). The first choice selects which named range the second list uses.

Why does INDIRECT return #REF!?

The value in the first dropdown doesn’t exactly match a named range — usually a plural, a capital letter, or a stray space. The names must be identical.

Can I make dependent dropdowns across different sheets?

Yes. Put the lists on a separate sheet, create the named ranges there, and INDIRECT still finds them. Hidden sheets work too.

How do I apply dependent dropdowns to every row?

Copy the two dropdown cells and paste down. Each row’s formula adjusts to its own cell — as long as you didn’t use absolute references like $E$2.

Does Google Sheets have a built-in cascading dropdown?

No — there’s no one-click feature. It’s always named ranges plus INDIRECT, or a formula-driven list.