Multiple Dependent Dropdowns in Google Sheets: Setup

Yes. In Google Sheets, you can create multiple dependent dropdowns by using a parent selection to filter a helper range, then setting the next cell’s data validation to use that range. For a list that must work across many rows, put a separate helper formula on each row; choosing a different parent then changes the available child options.

How dependent dropdowns work

A dependent dropdown is a data-validation list whose choices depend on a value selected elsewhere. For example, a user selects a department in one cell and sees only that department’s roles in the next. A third dropdown can then show locations that match both the department and role.

Google Sheets does not automatically connect lists just because their values are related. You provide that connection with a formula or a named range, and point the dropdown rule at the resulting range. A helper range is often the clearest method: it makes the filtered choices visible on the sheet and is relatively easy to check when something goes wrong.

In the example below, a source table has categories in column A and subcategories in column B. A user chooses a category in D2, and E2 offers only the matching subcategories. The same structure can be copied down for more entries.

Arrange the source data first

Keep the master list in a simple, consistent table. Put one valid category-subcategory pairing on each row, with a header row and no merged cells. For example:

A1: Category; B1: Subcategory
A2: Fruit; B2: Apple
A3: Fruit; B3: Pear
A4: Drinks; B4: Tea
A5: Drinks; B5: Coffee

Repeating a category is expected: the repeated rows are what tell the filter which subcategories belong to it. Keep spelling and spacing consistent, because “Drinks” and “Drinks ” (with a trailing space) are different text values. If the source contains duplicates, the formulas below use UNIQUE so the dropdown does not repeat those choices.

You can keep the source table on a separate tab, such as Lists, and place the entry form on another tab. If you do, include the sheet name in formula references, for example Lists!A2:A. Protecting the source or helper areas from accidental edits can also help when a form is shared.

Create the parent dropdown

First create a clean list of categories in a helper column, such as H. With the category names in A2:A, enter this in H2:

With category names in A2:A, enter =SORT(UNIQUE(FILTER($A$2:$A,$A$2:$A<>""))) in H2. It returns the nonblank categories once each, in sorted order.

Select D2, then choose Data > Data validation > Add rule. Under Criteria, choose Dropdown (from a range), and enter H2:H as the range. Click Done. The validation rule now uses the helper list rather than a manually typed set of options, so adding a new category to the source can update the list automatically.

If the category list should have a particular order rather than alphabetical order, arrange it in a dedicated source list instead of using SORT. For instance, a workflow might require “New,” “In progress,” and “Complete” in that sequence. A hand-maintained list can preserve that order.

Build the dependent subcategory list

For a single entry row, place the dependent choices in a clear area of the sheet, such as F2. With the parent selection in D2 and the source categories and subcategories in A2:B, enter:

With the category in D2 and source categories and subcategories in A2:B, enter =IF(D2="","",IFERROR(SORT(UNIQUE(FILTER($B$2:$B,$A$2:$A=D2,$B$2:$B<>""))),"")) in F2. It lists the distinct, nonblank subcategories whose category matches D2, sorted alphabetically.

Now select E2 and choose Data > Data validation > Add rule. Set Criteria to Dropdown (from a range), and use F2:F as the range. Click Done. When D2 contains “Fruit,” the helper formula returns Apple and Pear, and those are the choices in E2. If D2 is blank, the helper formula returns blank rather than a visible error.

FILTER returns matching rows, UNIQUE removes repeated choices, and SORT orders the result. IFERROR handles a category with no matching subcategories, for which FILTER would otherwise return an error. The formula is ordinary Google Sheets syntax; the dollar signs keep the source columns fixed when you copy the formula to another row.

Make the dropdowns work on multiple rows

A common snag is using one vertical helper list for many entry rows. Each row may have a different parent choice, so one shared list cannot represent all the different child lists at once. Give each entry row its own helper output instead.

For example, use D2:D for category selections, E2:E for subcategory selections, and helper columns AA onward. In AA2, enter this row-oriented version of the formula:

With the category for row 2 in D2 and the source table in A2:B, enter =IF(D2="","",IFERROR(TRANSPOSE(SORT(UNIQUE(FILTER($B$2:$B,$A$2:$A=D2,$B$2:$B<>"")))),"")) in AA2. It returns that row’s matching subcategories horizontally across the helper row.

Set E2’s Dropdown (from a range) rule to AA2:AZ2, or another range wide enough for the maximum number of subcategories. Copy the formula in AA2 down for the other entry rows, then copy E2’s validation rule to the corresponding E cells. Each helper formula refers to its own row’s category while the source columns remain fixed. Hide the helper columns if users should not see them, but do not delete them: the validation rules depend on them.

The range AA2:AZ2 has room for 26 helper cells. If a category could have more choices, widen the range and make sure there is room for the formula’s horizontal results. Avoid placing unrelated values in those cells, because they can block the formula from expanding.

Add a third level or more

For three levels, extend the source table to three fields, such as Category, Subcategory, and Item. The item helper formula must filter on both selected parent values. For row 2, with the two selections in D2 and E2 and the source fields in A2:C, a horizontal helper formula can be:

With category in D2, subcategory in E2, and source fields in A2:C, enter =IF(OR(D2="",E2=""),"",IFERROR(TRANSPOSE(SORT(UNIQUE(FILTER($C$2:$C,$A$2:$A=D2,$B$2:$B=E2,$C$2:$C<>"")))),"")) in the helper row for the item dropdown. It returns items matching both selections, with duplicates removed.

Set the item cell’s dropdown range to that helper row, and make a separate helper row for each entry row, just as for the second level. The pattern scales: each later level filters the next source column by all earlier selections. More levels also mean more helper formulas and more opportunities for a parent change to make a child choice obsolete, so keep the form as simple as the task allows.

Helper formulas versus named ranges

Named ranges are another established way to make dependent lists. You can assign a named range to each parent’s child list, then use INDIRECT in a validation range that refers to the selected parent. This can be compact for a small, fixed set of categories, but it requires maintaining a separate range for each parent. Range names also have naming rules: spaces and many punctuation marks cannot simply be used as range names, so the category text and range name must be made to correspond.

Helper formulas are usually easier to maintain when the source list changes often or contains many parent values. The list of valid choices is produced from the table, rather than requiring a person to create a new named range for every category. Named ranges can still suit a small form where the options are stable and the lists are intentionally curated. Whichever method you use, test the actual dropdown after changing its parent value.

Common problems and practical checks

The child list is empty. Confirm that the parent value exactly matches the source text, that the formula points to the right columns, and that the source rows actually contain a matching child value.
The formula shows an expansion error. Clear cells to the right of a horizontal helper formula. It needs empty cells in which to return its results.
Old child values remain after a parent changes. Changing a parent does not necessarily erase a child value already selected in the row. Clear the child cell and choose again. A validation rule may mark the old value invalid, but it should not be treated as an automatic reset.
The dropdown includes unwanted blanks. Use a source condition that excludes blank child values, as in the example, and set a sensible helper range. Check for blank cells or stray content inside the range used by data validation.
New source rows do not appear. Check that the formula and validation ranges cover the added rows. Open-ended references such as A2:A include future rows, but the dropdown’s helper range still needs enough room for the results.
Values that look the same do not match. Look for extra spaces, inconsistent spelling, or different punctuation in the source and parent selection. Standardizing the source data is often more reliable than trying to work around inconsistent labels.

Keep helper formulas away from the input cells, label the helper area, and test several combinations—including a parent with only one child and a parent with no children. If other people will edit the sheet, consider protecting formula cells while leaving the intended input cells editable. Protection prevents accidental changes; it is not a substitute for controlling who can access the spreadsheet.

How this differs from Excel

These instructions are for Google Sheets in a web browser. Excel also supports dependent dropdowns, but its data-validation menus, workbook behavior, and formula details differ. Do not assume a Google Sheets validation range or named-range setup will transfer unchanged to an Excel workbook. If you need to share the file between the two programs, verify the dropdowns and formulas in each one after conversion.

Frequently Asked Questions

Can I make two dependent dropdowns in Google Sheets?

Yes. Create a parent dropdown from a category list, filter the matching child values into a helper range, and set the second dropdown to use that range. The examples above use a category in D2 and its dependent subcategory in E2.

Can dependent dropdowns work for every row in a form?

Yes. Use a helper formula for each form row and point that row’s child dropdown to its own helper output. Copy the formula and validation rule down, then check that the helper range is wide enough for the largest possible list.

Does changing the first dropdown clear the second one?

Not automatically in the basic setup. If a user changes the parent, an already selected child may remain in its cell even though it no longer matches. Clear the child selection and choose a valid option for the new parent.

Can I use more than two levels?

Yes. For each additional level, filter its source values using all the earlier selections. For example, an item list can filter by both category and subcategory. Each level needs an appropriate helper output and a validation rule.

Why use a helper range instead of typing choices into the rule?

A helper range can update from the source table and makes it easier to inspect what the formula returns. Manually entered choices are reasonable for a short, unchanging list, but become harder to maintain when the options depend on other cells or change over time.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.