Dependent Dropdowns in Google Sheets: Setup and Fixes

To make a dependent dropdown in Google Sheets, create a named range for each group of choices, then set the second dropdown’s source to an INDIRECT reference based on the first selection. For example, choosing “Fruit” in A2 can make the dropdown in B2 show only fruit names.

What a dependent dropdown does

A dependent dropdown, also called a cascading dropdown, limits the choices in one cell according to a selection in another. It is useful when a form or tracker has related categories: a person chooses a department, then sees only teams in that department; a user chooses a country, then sees only its cities.

Google Sheets provides dropdowns through data validation. The first rule gives a cell its main list. The second rule points to a range whose contents depend on the first selection. Sheets does not automatically infer the relationship between two lists; you set up the ranges and the reference that links them.

This method is a good fit when the categories are relatively stable and you can maintain a named range for each parent choice. It uses ordinary spreadsheet features and does not require an add-on or Apps Script.

Plan the source lists

Keep your source data organized before creating the rules. A simple layout puts the parent choices in one column and each child list in its own column. For example, on a sheet called Lists, put Fruit, Drinks, and Snacks in A1:A3; put the fruit choices in B1:B3, drinks in C1:C3, and snacks in D1:D3. The exact layout is flexible, but each child list should occupy a clean, contiguous range.

For this example, the named ranges will be Fruit, Drinks, and Snacks. Each name must refer to the corresponding list of choices, not its heading. Thus Fruit refers to the cells containing Apple, Pear, and Orange. Keeping headings outside the named range prevents the heading itself appearing as a dropdown option.

Use one parent category per child list.
Avoid blank cells inside a list, since they can appear as blank options.
Use clear, consistent labels. The parent selection must match the name used to locate its child range, or the reference must translate between them.

Create the named ranges

In Google Sheets in a web browser, select the cells for one child list, then choose Data > Named ranges. Enter a name such as Fruit and select Done. Repeat for the other lists. A named range gives the cells a readable identifier that can be used in a data validation rule.

Range names cannot contain spaces. If a parent label has spaces, use a matching underscore name—for instance, name the range Hot_Drinks—and convert spaces to underscores in the reference formula shown below. Keep the spelling and punctuation consistent. A simple convention is to use underscores instead of spaces throughout the names.

Named ranges are easier to maintain than hard-coded cell addresses when lists move, because the name continues to refer to the assigned cells. They do not make the list unlimited, however: if you add an option below the cells included in the range, update the named range so it includes that new cell.

Build the first and second dropdowns

Suppose the entry sheet uses A2 for the category and B2 for the dependent choice. First select A2, then choose Data > Data validation and add a rule. Under Criteria, choose Dropdown (from a range) and specify the cells containing the parent list, such as Lists!A1:A3. Save the rule. The available selections in A2 should now be Fruit, Drinks, and Snacks.

Next, select B2 and open Data > Data validation again. Choose Dropdown (from a range) for the criteria. With categories in A2 and the corresponding named ranges in place, use this range expression:

With the category in A2 and named ranges called Fruit, Drinks, and Snacks, enter =INDIRECT($A2) as the range expression for B2. If A2 contains Fruit, the expression resolves to the Fruit named range, so B2 offers Apple, Pear, and Orange.

For labels containing spaces, use this version instead. With Hot Drinks in A2 and the corresponding named range called Hot_Drinks, enter =INDIRECT(SUBSTITUTE($A2," ","_")). The formula replaces spaces in the selected label with underscores before looking up the range. Save the rule and test several parent choices.

In the rule settings, decide what should happen when someone enters an item that is not in the list. Rejecting invalid input keeps entries consistent; showing a warning allows exceptions. The wording and options may vary slightly as Google updates its interface, but the rule is managed from Data > Data validation.

Apply the rule to more rows

For a table with many entries, the parent and dependent columns might be A2:A100 and B2:B100. You can apply the child dropdown rule to the intended cells in column B and use a row-relative reference such as =INDIRECT($A2). The dollar sign fixes the parent column at A, while the row number can adjust for each row in the applied range.

Set up and test the rule on the first data row before extending it. Confirm that changing A3 controls B3, rather than making B3 follow A2. If the reference does not adjust as intended, create or edit the rule for the relevant row or range and inspect the reference carefully. In a shared sheet, protect the source-list area or limit editing access so that users do not accidentally overwrite the values or named ranges.

A dependent dropdown controls future selections; it does not necessarily erase an earlier child choice when its parent changes. If A2 was Fruit and B2 was Apple, then changing A2 to Drinks can leave Apple displayed in B2 even though Apple is no longer a valid choice for that category. Re-select a valid child value after changing the parent. If the sheet must clear stale values automatically, that requires a separate workflow, such as a carefully designed Apps Script; data validation alone is not a clearing rule.

Keep the lists maintainable

When categories change, update both the source list and the named range it uses. If you add Mango below the current Fruit range but do not expand the range, Mango will not appear in the dependent dropdown. You can edit named ranges through Data > Named ranges, then check the range address for each list.

Consider using a dedicated Lists sheet to keep source values away from everyday data entry. You can hide that sheet to reduce clutter, though hiding it is not a security measure: editors may still be able to access or unhide it. For collaborative files, decide who is responsible for maintaining categories and make that process clear.

For a large number of categories, named ranges can become tedious to create and maintain. A consistent naming scheme helps, but it does not remove that workload. If the category data changes frequently or is generated from other records, a helper-table design or a small Apps Script may be more appropriate than manually maintaining many ranges.

Common problems and fixes

The dependent list is empty. Check that the parent cell is not blank and that its text resolves to an existing named range. Verify spelling, underscores, and the named range’s cell address.
The range name contains a space. Rename it with underscores and use the SUBSTITUTE version of the expression, or choose parent labels that already match valid range names.
A new option does not appear. Expand the named range to include the added cell. Also check that the option is not outside the range or separated from the other choices by an unintended blank.
The wrong row controls the dropdown. Check the formula’s row reference and the range the rule applies to. A reference anchored to the wrong row can make multiple cells depend on one parent selection.
An old choice remains after the category changes. This is expected: validation governs allowed entries but does not automatically clear existing cell contents. Replace the old choice with a valid one.

When diagnosing a rule, test it with a few known values and inspect the named ranges under Data > Named ranges. This is usually faster than rebuilding all the dropdowns. If other editors report different results, check whether they can edit the source lists and whether the rule applies to the same cells they are using.

When to use another approach

Named ranges with INDIRECT are straightforward for a compact set of categories. They may be less convenient when categories are numerous, list membership changes often, or labels do not map neatly to range names. One alternative is to organize the source data as a two-column table of category and item, then create helper ranges or formulas to prepare the choices. That approach can be easier to extend, but it takes more setup and still needs to be connected to data validation.

Google Sheets dropdowns are not the same as a searchable database picker. A normal dropdown is appropriate for a manageable list of standard options; for very large lists, scrolling and manual maintenance can frustrate users. Depending on the task, a Google Form, a structured input sheet, or a purpose-built app may suit the workflow better.

Excel also supports dependent dropdown techniques, including named ranges and INDIRECT, but its data-validation interface and formula behavior are not identical in every detail. The menu paths here are for Google Sheets in a browser, not Excel. Avoid copying a tutorial’s exact range syntax from one program to the other without checking it.

Frequently Asked Questions

Can Google Sheets make a dependent dropdown without a script?
Yes. Named ranges and an INDIRECT-based data validation range are enough for many common cases. Apps Script is only needed for extra behavior, such as clearing a child cell automatically when its parent changes.

Why does INDIRECT show an error?
Usually the text in the parent cell does not resolve to a valid named range. Check for extra spaces, misspellings, and spaces in the name. If your labels contain spaces, map them to underscore names with SUBSTITUTE.

Can I use a dependent dropdown in every row?
Yes. Apply the validation rule to the child cells in your table and make sure the parent reference uses the correct relative row. Test multiple rows after applying it to confirm each child cell follows its own parent.

Does changing the first dropdown clear the second one?
No. A validation rule does not automatically remove a value already in the cell. After changing the parent, choose a valid child value or use a separate automation if automatic clearing is essential.

Can the dropdown list update when I add choices?
Yes, if the named range includes the new cells. If the added items fall outside the range, edit it under Data > Named ranges so the dependent dropdown can see them.

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.