Nested Dropdowns in Google Sheets: How to Set Them Up

Yes. You can make a nested, or dependent, dropdown in Google Sheets by making the choices in a second cell depend on the selection in a first cell. A practical setup uses named ranges and INDIRECT in the second dropdown’s range rule; helper formulas are an alternative when categories do not map neatly to range names.

What a nested dropdown does

A nested dropdown narrows a later choice according to an earlier one. For example, a user first chooses a department, then chooses a role that belongs to that department. A product form might ask for a category such as Beverages, then offer only Coffee, Tea, and Juice in the next cell. This is also called a dependent dropdown or cascading dropdown.

Google Sheets has ordinary dropdowns, including dropdowns that use a cell range as their list, but its dropdown editor does not provide a separate visual control for building a hierarchy of menus. The dependency is created by connecting the first selection to a list used by the next validation rule. The familiar colored dropdown chips change how a selection appears; they do not, by themselves, make one dropdown depend on another.

Plan the source data first

Before adding validation, decide how the parent choices and child choices will be stored. For a short, stable list, separate columns for each parent category are easy to inspect. For a growing list, a two-column table is usually easier to maintain: one column contains the parent category and the other contains its child choice.

For example, a source table could contain Department and Role, with rows such as Sales—Account manager, Sales—Sales analyst, and Support—Help-desk specialist. Keep the spelling consistent, remove accidental spaces, and avoid blank rows inside a list where possible. If a category has no child options, decide whether the second cell should remain empty or show a message elsewhere.

For the named-range method, each parent category needs a corresponding named range containing its child options. A category called Home Audio can use a range named Home_Audio. Use simple, unique names made of letters, numbers, and underscores, and make sure the category-to-range naming pattern is predictable. A category with punctuation or several special characters may be easier to handle with a helper formula instead.

Build dependent dropdowns with named ranges

Put each set of child choices in its own range. For example, put Citrus options in E2:E5 and Berries options in F2:F4, or place each list in a clearly labeled column.
In Google Sheets, open Data > Named ranges. Select the cells for a list, give the range a name such as Citrus, and save it. Repeat for each parent category. Named ranges should refer only to the intended child choices, not to the heading above them.
Set up the parent cell, such as A2. Select it, choose Data > Data validation, and add a rule. Choose Dropdown (from a range) and point it to the range containing the parent labels. Save the rule.
Select the dependent cell, such as B2, and add another rule using Data > Data validation. Choose Dropdown (from a range). In the range field, use =INDIRECT(SUBSTITUTE(A2," ","_")). This turns the selection Home Audio in A2 into the range name Home_Audio. Save the rule and test each parent selection.

Here, A2 is the parent choice, and the named range is the child list. If the category labels already match their named ranges exactly, the simpler reference =INDIRECT(A2) may be enough. When applying a rule to multiple rows, check that the parent reference is relative to the first row of the applied range. For example, a rule starting at B2 should normally refer to A2, so each row can use its own parent selection. Test more than one row before filling out a live form.

Use helper formulas when names do not fit

Named ranges are straightforward when there are only a few categories and their names can be mapped cleanly. If labels contain punctuation, change often, or are not suitable as range names, a formula-generated helper list can be more manageable. Keep the original data in two columns: parent categories in D2:D and child choices in E2:E. Suppose the selected parent is in A2.

Put this formula in H2. It returns the matching child choices across the row, sorted and without duplicates:

=IFNA(TRANSPOSE(SORT(UNIQUE(FILTER($E$2:$E,$D$2:$D=$A2,$E$2:$E<>"")))),"")

Then set B2’s validation rule to Dropdown (from a range) and use a sufficiently wide helper range such as H2:AZ2. The formula creates the current options there; the validation rule reads those cells. Choose a width large enough for the largest category, and keep the cells to the right of each formula clear so its results can spill across them. If no matching child choices exist, the formula returns a blank rather than a no-match error.

For several entry rows, create a helper formula for each row and change its parent reference accordingly: the formula in H3 should refer to A3, for instance. Each dependent validation rule must point to that row’s helper range. This takes more setup than named ranges, but it separates the source data from the output list and accommodates labels that are awkward as range names. If you expect a very large form, consider whether a single-row-at-a-time form or a script-free redesign would be easier to maintain.

Keep the lists accurate as they change

Dependent dropdowns do not necessarily erase a child value already entered when someone changes the parent. If A2 changes from Sales to Support, B2 may still display the former Sales role even though it is no longer a valid Support choice. A validation rule controls or warns about entries; it should not be treated as an automatic cleanup mechanism for an existing value.

Choose the invalid-data behavior in the data validation rule deliberately. Reject input is useful when the sheet must contain only approved combinations. A warning is less restrictive and can suit a working sheet where exceptions are allowed, but it does not enforce clean data. When the parent changes, have the user reselect the child, or review combinations with a separate check. For an important intake sheet, a visible note can remind users to revisit the dependent field after changing the parent.

When new child choices are added, confirm they fall inside the named range or helper formula’s source data. A named range that stops at row 5 will not include a new choice typed in row 6 unless the range is expanded. Open Data > Named ranges to adjust it. Helper formulas based on open-ended columns such as E2:E can include later additions, though keeping source columns tidy remains important.

Common problems and practical fixes

The second list is empty. Check that the parent text matches the range name after any substitution. A trailing space or a hyphen where the formula expects an underscore can break the mapping. Check the named range spelling in Data > Named ranges.
The range formula is rejected. The dropdown editor’s accepted range syntax can be less intuitive than a normal cell formula. Try the named range method in a single cell first. If the editor does not accept the INDIRECT expression in your current setup, use a helper range containing the filtered options and point the rule to those cells.
Choices from another category appear. Verify that the source range contains only the intended list, or that the helper formula compares the correct parent column with the correct selection cell. Also check that the validation rule has not been copied with a fixed reference to the wrong parent row.
Old selections remain after a parent change. This is expected unless you add a separate process to clear or flag the old child value. Re-select the child choice, or add a review check for inconsistent pairs.
The list includes duplicates or blanks. Clean the source data, or use UNIQUE and filter out empty child cells in a helper formula. Named ranges display whatever cells they include, so remove unwanted entries there directly.

When to use each method

Use named ranges when the categories are few, stable, and can be converted to simple range names. They are easy to audit because each child list has a recognizable label, and the dependent rule is compact. The main maintenance cost is keeping each named range and its category mapping in sync.

Use helper formulas when categories are numerous, labels are inconsistent, or the options are maintained in a normalized two-column table. A FILTER-based helper can derive choices from that table and UNIQUE can remove repeated entries. The trade-off is extra helper cells and more validation rules to manage. You can put helper content on a separate sheet and hide that sheet for a cleaner form, but hiding it is only a presentation choice, not access control or protection.

If the second list contains only a handful of choices and does not need to change based on the first choice, an ordinary dropdown is simpler. If the user needs three or more levels, repeat the same pattern: each level’s options depend on the selection immediately above it. Keep the mapping explicit, test combinations at each level, and avoid building a long cascade when a searchable table or separate form would be clearer.

Google Sheets and Excel are not identical

The steps above refer to Google Sheets in a web browser, using its Data menu and dropdown validation rules. Excel has its own data validation interface, named-range behavior, and formula conventions, so these exact menu steps should not be copied into Excel. The basic idea—use a parent selection to determine the child list—is shared, but implementation details vary by spreadsheet program and version.

In Google Sheets, the visible dropdown can be styled as a chip or shown as an arrow depending on the rule’s display options. That affects appearance, not the list logic. Also, sharing a sheet with someone does not make a dependent dropdown a secure permission system: users with sufficient edit access may be able to change source data or validation settings. Protect source ranges if appropriate, while recognizing that sheet protection is meant to prevent accidental edits rather than provide strong data confidentiality.

Frequently Asked Questions

Can Google Sheets make a dropdown depend on another cell?

Yes. Use a dependent data validation rule, commonly with named ranges and INDIRECT, or use helper cells that generate a filtered list for the second dropdown. The first method suits clean category-to-range mappings; helper formulas are useful when the source is a changing table.

Does Google Sheets have a built-in nested dropdown button?

No separate nested-menu builder is required or provided in the ordinary dropdown editor. You create the relationship with a range reference, named range, or helper formula, then apply a dropdown validation rule to the dependent cell.

Why does INDIRECT not work in my dropdown rule?

Check the exact parent label, the named range’s spelling, and whether spaces or punctuation are converted consistently. Some setups or editor states may not accept the expression as a range input. In that case, generate the options in helper cells and use the resulting cell range as the dropdown source.

Will changing the first dropdown clear the second one?

Usually, no. Changing the parent selection does not automatically remove a previously entered child value. Ask users to choose again, or add a separate validation or review step that flags a child choice that no longer belongs to its parent.

Can I make three levels of dependent dropdowns?

Yes. Apply the same idea at each level: the third list depends on the second selection, and the second list depends on the first. Use clearly named ranges or helper formulas for each relationship, and test each valid path as well as what happens when an earlier choice changes.

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.