Skip to main content
SheetHub Docs
Navigation Menu
SheetHubDOCSSpreadsheet Knowledge Base
About
Productivity•8 min read

Google Sheets Dependent Dropdown: Dynamic Lists

Create Google Sheets dependent dropdowns with FILTER, helper ranges, and reliable validation for changing lists.

SheetHub••8 min
A regular dropdown always shows the same choices. A Google Sheets dependent dropdown changes the second list after someone makes a first selection. In Google Sheets, you can build that relationship with a clean source table, FILTER, and a helper range. You do not need to copy Excel's named-range and INDIRECT pattern. This guide builds a category-to-item dropdown. The same design works for departments and employees, countries and cities, or any other parent-child list.

What a dependent dropdown does

Suppose a sheet has a category in E2 and an item in F2. When E2 is Fruit, the dropdown in F2 should offer only fruit. When E2 changes to Office, F2 should show office supplies instead. The source data can look like this:
CategoryItem
FruitApples
FruitBananas
OfficePens
OfficeNotebooks
TravelCarry-on bag
TravelPacking cubes
 Keep one category-item pair per row. Consistent spelling matters: Office and office should not be treated as separate labels in a user-facing list.

Prepare a clean source table

Put the source list on a sheet such as Lists:
  • Column A: category
  • Column B: item
  • Row 1: headers
  • Rows 2 onward: records
Avoid merged cells and category labels that appear only once above a group. A formula can filter a normalized table reliably, but it cannot infer that several blank category cells belong to the label above them. Remove accidental blank items where possible. If blank rows are unavoidable, the helper formula below excludes blank item values. You can also put the source in a named range or a dedicated worksheet so the dashboard remains easy to use.

Create the first dropdown

The first dropdown holds the parent selection.
  1. Select the category cell, such as E2.
  2. Choose Data > Data validation.
  3. Add a rule with Dropdown from a range.
  4. Select the category source range, such as Lists!A2:A.
  5. If the same category appears many times, use a separate unique category list instead of the repeated source column.
  6. Click Done.
A separate category list can be generated in another helper column: 
=SORT(UNIQUE(FILTER(Lists!$A$2:$A, Lists!$A$2:$A<>"")))
Use that result as the first dropdown's source range. Keeping the category list separate makes the control easier to scan and avoids repeated choices.

Generate the dependent list in a helper range

Google Sheets data validation reads values from a range. It does not require the filtering formula to live inside the validation rule. Put the formula in a helper cell, then point the second dropdown at the cells populated by that formula. For a form where E2 contains the selected category, place this formula in H2 on a helper sheet or outside the visible dashboard: 
=IFNA(SORT(UNIQUE(FILTER(Lists!$B$2:$B, Lists!$A$2:$A=$E2, Lists!$B$2:$B<>""))),"")
The formula does four things:
  1. FILTER keeps items whose category equals E2.
  2. The second condition removes blank item values.
  3. UNIQUE removes duplicate choices.
  4. SORT gives the dropdown a predictable alphabetical order.
IFNA returns a blank when there is no match. Leave enough empty cells below H2 for the result to expand; content in the spill path can block it. If your source table is on the same sheet, remove the Lists! prefix. If the selected category is in another row, adjust the reference to that row, such as $E3 for a helper formula serving row 3.

Point the second dropdown to the helper range

Now connect the generated values to the item cell.
  1. Select F2.
  2. Choose Data > Data validation.
  3. Select Dropdown from a range.
  4. Use the helper range beginning at H2, for example Helpers!H2:H50.
  5. Choose whether invalid values should be rejected or merely show a warning.
  6. Click Done.
The validation rule watches the helper range, so changing E2 refreshes the choices in F2. Select a new category, then choose an item from the refreshed list. For a clean dashboard, keep helper columns on a hidden or protected worksheet. Never place arbitrary text in the helper spill area.

Handle blanks and no-match results

A category with no matching items should not produce a confusing #N/A message in the dashboard. The IFNA wrapper handles that case, but the validation control may still show no useful choices. That is a signal to add source data or correct the category spelling. A blank helper result is different from a blank item in the source table. The formula removes source blanks with Lists!$B$2:$B<>"", so empty rows do not become selectable values. If a user changes E2 after choosing F2, the old F2 value may remain even when it no longer belongs to the new category. Tell users to reselect the child value, or use Apps Script when automatic clearing is required. Apps Script is outside this formula-only setup.

Support multiple input rows

For categories in E2:E20 and items in F2:F20, each row needs an isolated helper result. Allocate one helper column per form row:
  • H2 filters using $E2
  • I2 filters using $E3
  • J2 filters using $E4
Point F2, F3, and F4 to the corresponding helper ranges. Each result then has its own spill path and remains easy to troubleshoot. For a larger form, use a separate input sheet or Apps Script. One vertical spill column cannot safely hold several expanding lists.

Why direct formula entry may fail

A common mistake is pasting FILTER directly into a dropdown's criteria field. Dropdown from a range expects cells, not an arbitrary variable-length formula result. Generate the list in a helper range first. Selecting only H2 as the validation source can also omit results that expand through H10. Select a range such as H2:H50, or maintain a deliberately sized helper range. For more complex helper transformations, the advanced QUERY in Google Sheets guide explains how to filter, label, and reshape formula output. Use QUERY when the list needs several conditions or a report-style transformation; FILTER is usually easier to read for one parent-child relationship.

Google Sheets versus Excel

The concept is shared, but the implementation is not. In Excel, dependent dropdowns often use named ranges and INDIRECT, or a spill reference such as =$E$4#. Google Sheets uses its own validation panel and a source range populated by formulas. See the Google Sheets dependent dropdown compared with Excel's named-range method guide for the Excel-specific methods instead of copying its validation source unchanged. A dependent dropdown is also different from a checkbox. A checkbox stores a TRUE/FALSE choice, while a dropdown stores one selected label. If your interface needs a yes/no switch alongside the category and item fields, Google Sheets checkboxes explains the formula behavior.

Troubleshooting checklist

The second list is empty when the parent text does not match a source category. Check the spelling in E2 and the category column on the Lists sheet. Keep IFNA around the helper formula when it shows #N/A, then check whether the selected category has any matching items. If the list stops early, expand the validation source range beyond the first helper cell. A source such as H2:H50 must cover the cells the formula can fill. Clear the cells below the helper formula when it shows #REF!. Text or another formula in the spill path blocks the result. Keep UNIQUE, check category spelling, and give each input row its own spill area when duplicates or row conflicts appear.

AI prompt callout

After testing one working list, you can ask an AI assistant to adapt the pattern to your columns. Include the source sheet, parent cell, child cell, blank behavior, and expected fallback. For example:
"In Google Sheets, my source sheet has Category in column A and Item in column B. The selected category is in E2. Write a formula for a helper range that returns sorted, unique, nonblank items matching E2, and explain how to use that helper range for a data-validation dropdown. Return a blank result when there are no matches."
Compare the generated formula with a tested example before applying it. Check every range, sheet name, absolute reference, and validation source.

Summary

To create a Google Sheets dependent dropdown, keep parent-child data in a normalized source table, create the parent validation rule, generate the matching child list with FILTER, and use that helper range as the second validation source. Add UNIQUE, SORT, and blank handling for a cleaner control. For multiple rows, isolate each helper spill area so the lists cannot collide.
Article Topics
#google-sheets#dependent-dropdown#data-validation#dynamic-lists

Recommended Next Reading

All Articles

Share this tutorial

Discussion & Community

Share questions, tips, or edge-cases about this spreadsheet formula.

Recommended Next Reading

All Articles