Dependent Dropdowns in Google Sheets: How to Set Them Up

To create a dependent dropdown in Google Sheets, make a named range for each set of choices, then use =INDIRECT($A2) as the source for the second dropdown, assuming the first choice is in A2 and the dependent dropdown is in B2. The second menu will then show the list whose named range matches the choice in column A.

What a dependent dropdown does

A dependent dropdown changes its available choices according to a selection made elsewhere. For example, someone filling in an order form could choose a category such as Fruit or Drinks in one cell, then see only apples and pears, or tea and juice, in the next cell. This is also called a cascading dropdown or dependent list.

Google Sheets’ regular dropdown can show a fixed list or values from a specified range. The dependent behavior comes from linking that range to the first selection. In the method below, named ranges give each list a readable name, and the INDIRECT function turns the selected category into a reference to the matching named range.

Plan the sheet and its lists

It is easiest to keep the source values on a separate tab and the form or working data on another. For a small example, make a tab called Lists and lay it out like this:

Put Fruit and Drinks in Lists!A2:A3. These are the choices for the first dropdown.
Put Apples, Pears, and Bananas in Lists!B2:B4.
Put Tea, Coffee, and Juice in Lists!C2:C4.
On a tab called Orders, use column A for the category and column B for the dependent choice.

The category labels need to correspond to the names assigned to the lists. In this example, the named ranges will be Fruit and Drinks. Keep the spelling consistent: a mismatch between a category and its range name prevents the second menu from finding the correct choices.

Create a named range for each choice list

In Google Sheets in a web browser, select the cells containing the first list, such as Lists!B2:B4. Choose Data > Named ranges, enter Fruit as the range name, and confirm. Repeat for Lists!C2:C4, naming that range Drinks. Named ranges are labels for cell ranges; they do not copy or rearrange the values.

Use simple range names that match the category text. Names cannot contain spaces, so a category such as Home Goods needs a small adjustment: name its range Home_Goods and convert the space to an underscore in the formula. Avoid punctuation and make sure each name is unique. You can review or edit these definitions later through Data > Named ranges.

Add the first dropdown

On the Orders tab, select A2 or the cells where people will choose a category. Choose Data > Data validation, then add a rule. Set the criterion to Dropdown (from a range) and enter the category range, for example Lists!A2:A3. Save the rule. The category menu should now offer Fruit and Drinks.

If you want the rule to cover future rows, select a suitable range such as A2:A100 before adding it. You can use a larger range, but applying rules to unnecessary cells can make a sheet harder to manage. Keep the source range limited to the actual category values so blank source cells do not become confusing menu entries.

Set up the dependent dropdown

Select B2, or the intended cells in column B, and add a data validation rule. Choose Dropdown (from a range) for the criterion. With Fruit or Drinks in A2, the range source for the dependent rule is =INDIRECT($A2). The dollar sign fixes the reference to column A while leaving the row relative, which matters when the rule is used on multiple rows. Save the rule, then test it by choosing Fruit in A2 and opening B2.

For a category with spaces, use a formula that changes the spaces to underscores. For example, if A2 contains Home Goods and the named range is Home_Goods, use =INDIRECT(SUBSTITUTE($A2," ","_")) as the range source. This formula assumes the category is in A2 and that replacing spaces with underscores produces the exact named range. If categories also contain punctuation or other characters that differ from the range names, use a separate mapping or simplify the labels rather than relying on a space replacement alone.

To apply the dependent rule to a block such as B2:B100, select that block before adding the rule and keep the row reference relative, as in =INDIRECT($A2). Check a few rows afterward: B2 should respond to A2, B3 to A3, and so on. If all rows appear to use the same parent selection, inspect the references and ensure the row number was not fixed with a dollar sign.

Handle changes to the parent choice

A dependent dropdown limits the choices presented when a user opens the menu, but it does not automatically erase a value already in the dependent cell. For instance, if B2 contains Apples and someone changes A2 from Fruit to Drinks, Apples can remain in B2 even though it is no longer a valid choice for the new category. This is a common source of inconsistent records.

Decide how your sheet should handle that situation. For a simple form, tell users to reselect the dependent value after changing the category, or clear B2 manually. For data that must remain consistent, add a review step, such as a conditional formatting rule or a separate validation check that flags values that do not belong with their category. An Apps Script can clear a child cell when its parent changes, but that introduces code to maintain and should be tested carefully on a copy.

Keep the lists maintainable

When choices change, update the source cells and the named range as needed. A named range defined as Lists!B2:B4 will not automatically include a new item typed in B5. You can edit the range through Data > Named ranges, or define it to cover a reasonable extra area and accept that unused blank cells may be included in the dropdown. Choose the approach that keeps the menu clean for your users.

For a workbook with many categories, arrange each category’s options in a clearly labeled column and document the relationship between category names and named ranges. Avoid duplicate category labels that need different lists: a category label used by INDIRECT points to one named range, so the label alone cannot distinguish two different lists. Test additions, renames, and removals in a few sample rows before relying on the sheet for data collection.

Common problems and how to fix them

The second dropdown shows an error or no useful choices: Confirm that the selected category matches a named range exactly. Check spelling, underscores, and whether the range still exists.
The menu works for one category but not another: Verify that every category has its own named range and that each range points to the intended cells on the Lists tab.
Rows use the wrong category: Check that the rule starts on the same row as its reference. For a rule beginning in B2, use A2, not A1 or a fully fixed reference such as $A$2.
A previous selection remains after changing the category: This is expected behavior; the data validation rule does not clear existing cell contents. Clear or reselect the dependent value.
Blank items appear in the menu: Tighten the source range or move the named range boundary when you add or remove options.
A category with spaces fails: Use a matching underscore-based named range and a formula that replaces spaces, or rename the category to a simple label.

Also check that the cell itself has the intended data validation rule. Selecting a cell and opening Data > Data validation lets you inspect the rule and its source. If users paste values into cells, remember that pasted data may not behave like a selection made from the dropdown, so a menu alone is not a complete data-cleaning system.

Alternatives and when to use them

Named ranges with INDIRECT are a practical choice when categories and their lists are stable and clearly named. They are easy to inspect in the named ranges panel, but they require careful naming and can become cumbersome when there are many categories or frequent structural changes.

For a short form with only a few categories, separate sections or separate dropdown rules may be simpler to maintain. For a larger data-entry process, consider whether the choices should be stored in a more structured table and whether a form or database-style tool is better suited to the workflow. Google Forms can collect responses with questions and sections, though its branching options and presentation differ from an in-sheet dropdown. These alternatives do not automatically solve every dependent-list use case; choose based on how people enter, review, and update the data.

Excel has its own data validation and named-range features, and similar dependent-list techniques are possible there. However, this article’s menu paths and setup steps are for Google Sheets in a web browser. Do not assume a workbook imported from Excel will preserve every validation rule or named-range behavior exactly; check the converted sheet and test its menus.

Frequently Asked Questions

Can I make a dependent dropdown in Google Sheets without named ranges?
Yes, other approaches can build a changing list, but named ranges combined with INDIRECT are a straightforward option for a modest set of categories. The best alternative depends on the layout and how often the source lists change.

Does changing the first dropdown clear the second one?
No. The second cell can retain its old value after the first selection changes. Users need to clear or replace it, or you need an additional check or automation to handle stale values.

Can category names contain spaces?
They can appear in the dropdown, but a named range cannot use a space in its name. One common solution is to use underscores in range names and use SUBSTITUTE in the range-source formula to convert spaces to underscores.

Can I use the setup for many rows?
Yes. Apply the rule to a range such as B2:B100 and use a relative row reference such as $A2. Test several rows to confirm each dependent menu refers to the category in its own row.

Why does my dependent dropdown show an invalid range?
Usually the category text does not resolve to a named range. Check the range name, the spelling in the parent cell, the rule’s row reference, and whether the named range still points to valid cells.

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.