Conditional Dropdowns in Google Sheets: How They Work

To make a conditional (dependent) dropdown in Google Sheets, create a named range for each group of choices, then set the second dropdown to use a range based on the first selection. For example, a choice of “Fruit” can make the next cell offer only apple, pear, and orange. You can do this in Google Sheets in a web browser with named ranges and the INDIRECT function.

What a conditional dropdown does

A standard dropdown presents the same options wherever it appears. A conditional dropdown changes its options according to a value selected elsewhere. The first dropdown is often called the parent or controlling dropdown; the second is the dependent dropdown. Additional levels are possible, such as choosing a department, then a team, then an employee.

This is useful when a form or tracker has related choices. A product-entry sheet might offer a category in column A and the matching products in column B. A project sheet might ask for a region, then show only the offices in that region. Restricting choices can make entries more consistent than asking people to type labels from memory, and it can reduce irrelevant options.

“Conditional dropdown” is not a separate Google Sheets menu command. It is a setup built from data validation, which controls what can be entered in a cell, and a source of choices that depends on another cell. The key distinction is that conditional formatting changes a cell’s appearance, while a dependent dropdown changes the permitted list of choices.

Create a regular dropdown first

Before making the list dependent, it helps to understand the standard dropdown control. In a Google Sheets browser tab, select the cell or cells, then choose Data > Data validation > Add rule. Under Criteria, choose Dropdown to type the options directly, or choose Dropdown (from a range) to use cells that contain the options. Set whether invalid data should be rejected or merely trigger a warning, then select Done.

For a list that will change over time, a range-based dropdown is usually easier to maintain than typing each item into the rule. Put the source options on a separate sheet, for example, and point the validation rule at that range. You can hide the source sheet from ordinary users if appropriate, but hiding it is not a security measure: editors may still be able to access or unhide it.

Data validation helps guide entry, but it does not automatically clear a dependent cell when its parent changes. If someone selects “Fruit,” chooses “Pear,” then changes the parent to “Dairy,” the old child value may remain even though it is no longer a valid choice. Plan to clear or reselect the dependent value when that happens.

Prepare the source lists and named ranges

For a simple two-level setup, organize each child list in its own column or range. For example, create a source sheet with a column headed Fruit containing Apple, Pear, and Orange, and another headed Dairy containing Milk, Yogurt, and Cheese. Keep the category names in the first row of the corresponding columns if that makes the layout easier to understand.

Create a named range for each child list. Select the cells containing the Fruit options, then choose Data > Named ranges, enter a clear name such as Fruit, and select Done. Repeat for the Dairy options. Named ranges give the validation rule a recognizable reference rather than requiring a separate hard-coded address for every category. Avoid spaces in the range names; use underscores when needed, such as Home_Office.

Keep category labels and range names consistent. A category called “Home Office” needs a range name that can be formed as “Home_Office.”
Do not include the column heading in the named range unless you also want the heading to appear as a selectable option.
Leave room for likely additions, or expand the named range when you add items. A fixed range does not necessarily include rows added below its current endpoint.

Connect the parent choice to the dependent dropdown

Suppose A2 contains the parent category and B2 is where the user should choose a matching item. First, add a regular dropdown to A2 using the category names, such as Fruit and Dairy. Then select B2, choose Data > Data validation > Add rule, and choose Dropdown (from a range) for the criteria. In the range field, use a reference that turns the parent’s text into the corresponding named range. With category names that contain spaces, the reference can replace spaces with underscores.

With the parent category in A2 and named ranges called Fruit and Dairy, the range reference is =INDIRECT(SUBSTITUTE(A2," ","_")). INDIRECT interprets the text produced by SUBSTITUTE as a range reference. For example, if A2 contains “Home Office,” SUBSTITUTE changes that text to “Home_Office,” and INDIRECT points to the named range Home_Office. Choose Done to save the rule.

The range formula must match the actual names in your sheet. If the selected category has no corresponding named range, the reference cannot resolve, and the dropdown may show an error or fail to provide options. Test every parent choice, not just the first one. Google Sheets’ validation dialog and available controls can change over time, so if the range field rejects a reference in your account, use the helper-range approach below rather than assuming the rule was saved correctly.

Apply the setup to multiple rows

For a small sheet, create and verify the rule in B2, then copy that cell and use Edit > Paste special > Data validation only on the remaining destination cells. Check the resulting rules on a few rows. A relative reference such as A2 should correspond to the parent cell on each row, but copied rules and ranges are easy to misalign if the source or destination selection is unusual. Test by choosing different parent values in two separate rows.

If the same validation rule is applied to a range such as B2:B100, the row reference needs to correspond to each row’s parent cell. Do not lock the row number with dollar signs if each row should depend on its own parent. Conversely, if every child dropdown intentionally depends on one shared selector, use an absolute reference to that selector. The right reference depends on whether the sheet is a row-by-row data-entry table or a single form controlled by one cell.

When users change a parent after selecting a child, they should check that the child still belongs to the new category. Data validation is not a cascading reset system. For important records, consider adding a separate review column or using a controlled form workflow so stale combinations can be spotted rather than silently trusted.

Use a helper range when the named-range method is awkward

Named ranges work well when there are a manageable number of categories and each has a clearly defined list. They are less convenient when the options are stored in one long table with columns such as Category and Item, or when category names are irregular. In that case, a helper area can produce the options for a selected category, and the dependent dropdown can use that helper area as its source range.

For example, keep a two-column source table on a Lists sheet, with categories in column A and items in column B. A helper formula can filter the item column for the category selected on the entry sheet. Point the dropdown’s Dropdown (from a range) rule at the helper cells. The helper formula and source range must be arranged so the output fits the validation range and does not overwrite other data. If each entry row needs its own different dependent list, each row needs an appropriate helper range or a more carefully designed layout; one shared helper list only reflects one controlling selection at a time.

This approach is often easier to audit than maintaining many named ranges, but it uses extra sheet space and needs planning for multiple simultaneous rows. Named ranges are simpler for a compact form; a table plus helper ranges can be more manageable for a larger, frequently updated catalogue. Neither approach is inherently more secure, and both depend on keeping the source data accurate.

Maintain the lists and choose rejection or warning

As options change, update the source cells and make sure the named range or helper source covers the new entries. If the list is sorted for readability, remember that sorting does not itself change the dependency logic. For collaborative sheets, protect source ranges or restrict editing where appropriate, so users do not accidentally overwrite the lists. Sheet protection can limit edits, but it is not a substitute for access controls on sensitive information.

In the validation rule, rejecting invalid input is appropriate when the value must come from the list. A warning is more flexible if users sometimes need to enter an exception, but it also means the sheet can contain values outside the dropdown options. Decide based on how the data will be used: a reporting table that expects standardized categories usually benefits from rejection, while a draft worksheet may need the flexibility of a warning.

Also decide how blanks should work. An empty parent may leave the child list unresolved, which is reasonable if the parent is required first. If users can leave a row incomplete, make that behavior clear and avoid treating a blank dependent cell as a completed choice. Test empty cells, new options, and parent changes before sharing the sheet widely.

Common problems and how to fix them

The child dropdown is empty: confirm that the parent text exactly matches the named range after any substitutions, and that the named range contains cells with options rather than only a heading.
One category works but another does not: check for extra spaces, punctuation, spelling differences, and missing named ranges. A category like “Home Office” and a range called “HomeOffice” will not match the example reference.
The old child value remains after changing the parent: this is expected behavior. Clear the cell or select a valid value for the new category; validation does not necessarily erase existing content.
New list items are missing: expand the named range or the dropdown’s source range to include them. Confirm that the source cells are not outside the specified range.
The rule behaves differently in different rows: inspect each rule’s referenced parent cell. Relative and absolute references behave differently when copied.
People can still type other values: edit the rule and choose the invalid-data rejection option if entries must be restricted. A warning allows non-list entries.

When troubleshooting, check the rule itself as well as the source cells. Select a problem cell and open Data > Data validation to inspect its criteria and range. Testing with a new blank row can help distinguish a broken rule from an old value that was entered before the rule was added.

Frequently Asked Questions

Can I make a dependent dropdown without named ranges?
Yes. Use a helper range that generates the options for the chosen parent, then use that range for the dropdown’s source. This can suit a normalized category-and-item table, though multiple rows may need separate helper areas.

Does Google Sheets clear the second dropdown when I change the first?
Not automatically in the usual data-validation setup. A previously selected child value can remain in the cell even if it is no longer on the new list. Clear it or choose a valid replacement.

Can I have more than two levels?
Yes. Each level can depend on the choice before it. For example, region can control office, and office can control team. Each additional level requires matching source lists and a rule or helper setup that references the correct preceding choice.

Can other people edit the dropdown options?
People with sufficient access can edit the source cells or validation rules. Protecting ranges can help prevent accidental changes, but sharing permissions determine who can access the spreadsheet.

Is a conditional dropdown the same as conditional formatting?
No. A dropdown limits or guides data entry. Conditional formatting changes how cells look based on rules, such as highlighting a row when a status is “Overdue.” The two features can be used together but are configured separately.

Google Sheets versus Excel

The instructions here are for Google Sheets in a web browser, using its Data > Data validation and Data > Named ranges controls. Excel also supports dependent lists, but its validation dialogs, range handling, and workbook behavior differ. Do not assume a rule or named-range reference will transfer between the programs unchanged; test the dropdowns after importing or exporting a workbook. In Google Sheets, the practical choice is usually between named ranges for a compact, clearly structured set of lists and helper ranges for lists maintained as a larger table.

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.