Google Sheets Dropdown Based on Another Cell: Guide

Yes. In Google Sheets, you can make one dropdown depend on another by creating named ranges for each set of choices, then using INDIRECT in the second dropdown’s range rule. For example, choosing Hot Drinks in A2 can make B2 offer only items from the Hot_Drinks named range.

What a dependent dropdown does

A dependent, or cascading, dropdown limits the choices in one cell according to a selection in another. It is useful when a form or tracker has related fields: a department followed by a role, a country followed by a city, or a product type followed by a product. Without a dependency, every possible choice appears in one long list, making selection slower and increasing the chance of inconsistent entries.

Google Sheets does not infer the relationship between two lists automatically. You provide the relationship through the way you organize the source data and configure data validation. The first cell contains a category; the second cell’s validation rule points to the list associated with that category. This is different from merely changing a cell’s color or hiding rows: the dependent cell receives a genuine dropdown rule.

Prepare the source lists and named ranges

Start with a clear layout. For example, place the categories in A2:A4 on a sheet named Lists, and put each category’s choices in its own column: Hot_Drinks in B2:B5, Cold_Drinks in C2:C5, and Snacks in D2:D5. The labels in the category cells can be reader-friendly, such as Hot Drinks; the named ranges must be valid range names and can use underscores instead of spaces.

Create a named range for each list. Select the cells containing the choices for one category, then choose Data > Named ranges. Enter a name such as Hot_Drinks and confirm that the range shown is the intended cells. Repeat for Cold_Drinks and Snacks. Named ranges are labels for cell ranges; they do not copy or duplicate the list data.

Keep the names distinct and easy to map to the parent dropdown labels.
A named range cannot contain spaces. Avoid punctuation and names that could be confused with cell references.
If category labels contain spaces, use underscores in the corresponding range names; the formula below converts spaces to underscores.
Use contiguous cells for each list and avoid including headings or blank rows unless you want them available as choices.

For a small workbook, separate columns are easy to inspect. A longer or frequently changing source list may need more planning: decide where additions will go and make sure each named range covers them. If a new choice is entered below the range’s last cell, it will not appear in the dropdown until the named range is extended.

Set up the first and dependent dropdowns

In this example, the user will choose a category in A2 and a matching item in B2. Create the first dropdown by selecting A2, choosing Data > Data validation, and adding a rule. Set the criteria to Dropdown (from a range) and select the category cells on the Lists sheet. Save the rule. The choices in A2 should now be the category labels, such as Hot Drinks, Cold Drinks, and Snacks.

Next select B2, open Data > Data validation, and add a rule with the criteria Dropdown (from a range). In the range field, enter this expression. It assumes the parent choice is in A2, the child dropdown is in B2, and the named range for Hot Drinks is Hot_Drinks. If A2 contains Hot Drinks, the expression resolves to the named range Hot_Drinks, so B2 shows the items in that range.

=INDIRECT(SUBSTITUTE($A2," ","_"))

Save the rule and test it by choosing each category in A2. If your interface rejects the expression, check that the named range exists, the category text matches the range name after spaces become underscores, and the rule is set to Dropdown (from a range), not a plain dropdown with manually entered options. Testing with one parent and child cell first makes errors easier to locate.

Why INDIRECT and matching names matter

INDIRECT treats text as a reference. The category selected in A2 is text, not a cell range by itself. SUBSTITUTE changes spaces in that text to underscores, producing a string that matches the named range. Together, the functions turn Hot Drinks into a reference to Hot_Drinks. The validation rule then uses that reference as the source of the child choices.

The mapping needs to be exact. If the parent dropdown says Cold Drinks but the named range is called Cold_Beverages, the expression will look for a range named Cold_Drinks and will not find the intended list. You can either make the labels and range names follow the same pattern or use a different mapping method when the labels cannot be standardized. This is why a short, consistent naming scheme is more than cosmetic: it is part of the link between the two dropdowns.

Named ranges can also help make a workbook easier to maintain because the validation rule refers to a meaningful label rather than a hard-to-read address. They do not, however, make lists automatically expand in every situation. Confirm that the named range covers any new entries you add, and check the dropdown after editing the source data.

Apply the rule to multiple rows

For a data-entry table, each row usually needs its own parent and dependent selection: A2 controls B2, A3 controls B3, and so on. One practical approach is to create and verify the rule in B2, then copy that cell and use Edit > Paste special > Data validation only on the other cells in column B. Keep the row reference in the rule relative, as in $A2, so it can adjust for the row receiving the copied rule. The dollar sign fixes the column A reference while allowing the row number to change.

After copying, test more than the first row. Select different categories in A2 and A3 and confirm that B2 and B3 show the lists associated with their own row. If all rows respond to A2, the rule was copied with an absolute row reference or applied in a way that retained the original reference. Correct the reference and test again before distributing the sheet to other users.

For a small form with only one dependent field, a single rule may be enough. For a large table, validation rules can take more care to set up and audit. Keep a clear header, protect source-list cells from accidental edits if appropriate, and include a brief instruction near the entry area so users know to select the parent before the child.

Handle changes, blanks, and invalid entries

A dependent dropdown controls what can be selected next; it does not necessarily erase a child value that is already present. If B2 contains Coffee and someone changes A2 from Hot Drinks to Snacks, the old value may remain in B2 even though it is no longer a valid choice for the new category. The user should reselect the child value. If this matters for reporting, add a review step or a separate check that flags parent-child combinations that no longer match.

Decide how the rule should handle blanks and invalid data. In the data validation settings, choose whether to reject invalid input or show a warning, depending on the workflow and the options available in the rule editor. Rejecting invalid input is preferable when consistency is essential; a warning may suit a sheet where users sometimes need to enter exceptional values. A blank parent can also leave the dependent rule without a usable source, so test what happens when the parent is cleared.

Use a simple test row before rolling the rule out to a full table.
Check for extra spaces or misspellings in the category labels and named ranges.
Confirm that source lists contain only the options intended for users.
Recheck dependent cells after changing a parent, since old selections are not automatically a reliable indication of validity.

Common alternatives and when to use them

For a few stable categories, named ranges and INDIRECT are usually a straightforward approach. They keep each list visible and make the relationship understandable to someone maintaining the sheet. Their main limitation is the naming convention: the parent text needs to translate cleanly into the range name, and every category needs an appropriate named range.

Another option is to maintain separate helper lists or helper ranges and point validation rules to those ranges. This can be useful when category labels and internal names do not match or when the source data needs extra transformation. It can also make the workbook more complex, because helper areas need to be maintained and kept out of the way of ordinary data entry. A plain dropdown with every option is simpler, but it is not dependent and may be unwieldy when choices are numerous.

Google Sheets also supports formulas in cells that can produce filtered lists, but a formula-generated list is not automatically a dropdown. The validation rule still needs a usable range as its source, so a helper area may be needed. For most users, begin with named ranges; choose a helper-range design when it solves a real naming, organization, or maintenance problem rather than adding it preemptively.

Frequently Asked Questions

Can I make a Google Sheets dropdown depend on another cell without named ranges?
Yes, but named ranges are a common way to connect category text to separate option lists. You can instead build helper ranges and point the validation rules at them. That approach is useful when labels do not map neatly to range names, but it requires maintaining the helper structure.

Can the dependent dropdown update automatically when I change the first dropdown?
The choices offered by the validation rule can follow the parent selection, but an existing child value is not necessarily cleared when the parent changes. Have users choose the child again, or add a separate review check if stale combinations would cause problems.

Can category names contain spaces?
Yes. The visible category labels can contain spaces. In the example, SUBSTITUTE changes spaces to underscores so Hot Drinks corresponds to the named range Hot_Drinks. If labels contain other characters that do not match your range names, adjust the naming scheme or use a helper mapping.

Why does my dependent dropdown show an error or no choices?
Check that the parent cell is not blank, the selected category matches a named range after the space-to-underscore conversion, and the named range points to the intended cells. Also confirm that the second rule uses Dropdown (from a range) and that the rule was copied with the correct row reference.

Does this work in Excel too?
The idea of dependent dropdowns exists in Excel, but Google Sheets menu paths, validation behavior, and formula handling are not identical. These steps are for Google Sheets in a web browser; use Excel’s own Data Validation workflow and verify its range-reference behavior rather than assuming the same setup will transfer unchanged.

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.