How to Make a Dependent Drop Down List in Google Sheets

We can utilize lists in Google Sheets to organize and standardize our data. One of the best ways to do this is by using a dependent drop-down list, which makes data entry faster, more consistent, and less prone to errors.

Dependent drop-down lists are useful in a variety of situations. You can create a setup where the first list choice determines the options for the second. In this guide, we will walk through how to create a dependent drop-down list in Google Sheets, including a standard method and a multi-row method.

What Is a Standard Dependent Drop-Down List?

A Google Sheets dependent drop-down list is a drop-down list where the options shown in one cell depend on what you select in another cell. This helps users enter data in a simple, error-free way because they choose from approved options instead of typing values manually. Note that these are also called selection dropdowns or dependent menus.

For example, if a user selects a category in one cell, the next cell can show only the options that belong to that category. A common real-world analogy is browsing a store, selecting a genre or category first, and then seeing the related items available under that category.

Here is my video on how to create a dependent drop-down list in Google Sheets:

A Step-by-Step Guide to Making a Dependent Drop-Down List in Google Sheets

To show you how to create a dependent drop-down list in Google Sheets, I will use an example of a shirt store with four categories, namely, Color, Size, Type, and Year.

Each category has its own list of options. For example, the category Color has four options: Blue, Red, Green, and White.

In this walkthrough, we will create a dependent drop-down in cell A10 that lets us select a category. Once we select the category, a dependent drop-down list will appear in cell B10 showing only the options for that category.

Step 1. Select the cell range with data and define named ranges.

Select the range and right-click, then choose the option to Define named range.

Defining named ranges is helpful because it makes your data validation and formulas easier to maintain. In the example above, I named each category’s option range.

For example, I named the cell range A3:B6 as Shirt_Color because it contains the color options.

Here is how it looks after naming the range:

Step 2. Use data validation to create a drop-down list for the categories.

In the data validation menu, select List from a range, then select the cell range with the headings (the categories).

In this example, I used A2:D2 because it contains all the categories (Color, Size, Type, and Year).

Step 3. Set up a helper output range that will show the correct options for the selected category.

Select the helper range where your formula output will appear. In this example, I used E2:E5. This is where the formula will return the options list used by the dependent drop-down in B10.

Step 4. Use an IF + INDIRECT formula to return the correct named range based on the selected category.

Generic structure:

=IF(EXPRESSION, INDIRECT("NAMED_RANGE_IF_TRUE"), INDIRECT("NAMED_RANGE_IF_FALSE"))

Example formula used:

=IF(A2=A10, INDIRECT("Shirt_Color"), INDIRECT("Shirt_Size"))

This tells Google Sheets to check the category selected in A10 and return the corresponding named range into the helper output area. Then, the dependent drop-down in B10 can use that helper output as its “List from a range.”

Step 5. Create the dependent drop-down in B10 using the helper output range.

Go to Data > Data validation for cell B10, select List from a range, and select your helper output range (for example, E2:E5).

Once completed, your dependent drop-down should look like this:

Try It Yourself »

Best Practices for Dependent Drop-Down Lists

• Use clean category labels. Avoid trailing spaces in category headers, and keep naming consistent so your formulas match exactly.
• Use a naming convention for named ranges. For example, Shirt_Color, Shirt_Size, and so on. Consistent names make troubleshooting easier.
• Put your lists on a dedicated tab. A “Lists” or “Data” sheet keeps your source lists separate from your entry form and reduces accidental edits.
• Apply validation to the rows you actually use. Instead of validating entire columns, validate a bounded range (for example, A10:A200) to keep performance snappy.
• Decide whether to reject input or show warnings. If multiple people edit the sheet, rejecting input enforces consistency. Warnings can be better for flexible workflows.
• Document your helper ranges. Add a note above helper areas like E2:E5 so future you knows what it is for.

Multi-Row Dependent Drop-Down

A multi-row dependent drop-down list is similar to the standard method, but it is designed to work across multiple rows so you can create many dependent drop-down pairs at once.

There are two key differences:

• The formula: it outputs multiple rows of option lists at the same time.
• Data validation ranges: you apply validation for each row so each dependent drop-down points to the correct output range.

The Formula

Formula used:

=ARRAYFORMULA(
  IF(
    LEN(A10:A),
    TRANSPOSE(
      ARRAYFORMULA(
        IF(
          TRANSPOSE(A10:A)=A2,
          INDIRECT("Shirt_Color"),
          IF(
            TRANSPOSE(A10:A)=B2,
            INDIRECT("Shirt_Size"),
            IF(
              TRANSPOSE(A10:A)=C2,
              INDIRECT("Shirt_Type"),
              INDIRECT("Shirt_Year")
            )
          )
        )
      )
    ),
    ""
  )
)

This formula returns option lists for multiple rows at once, based on the categories selected in A10:A. Each row’s dependent list can then point to the correct output row.

Performance note: For best performance, avoid using entire columns in your formula unless you need them. Use a bounded range like A10:A200 if you only expect a couple hundred entries.

Data Validation

In this example, I used a horizontal output range (E2:H2) because the formula displays the data horizontally.

Here is how I created multiple rows of dependent drop-down lists:

1. Step 1. For each cell from A10 down to A13, add data validation using List from a range and select the category header range (for example, A2:D2).
2. Step 2. For each cell from B10 down to B13, add data validation using List from a range and point to the matching helper output range for that row (for example, E2:H2 for B10, E3:H3 for B11, and so on).

If you are having trouble following along, here is a template for you.

Having trouble? Check out our other article where we discuss in detail how to make multiple selections in drop-down lists in Google Sheets.

Important Considerations

Dependent drop-down lists are powerful, but they are also more complex than a basic drop-down. Most setups rely on helper ranges that output the correct list for data validation to reference.

If you want dependent drop-downs for many rows, plan your structure first:

• Keep source lists on a dedicated “Lists” tab.
• Use consistent named ranges so formulas stay readable.
• Use a helper output area that can scale to the number of rows you need.

I also covered how to make a drop-down list in Google Sheets. You will want to make sure you are comfortable with that process before you move onto more complex setups like dependent drop-down lists.

Troubleshooting: Dependent Drop-Down Not Working

If your dependent drop-down is blank, showing the wrong values, or rejecting selections, these are the most common causes and fixes.

The dependent drop-down is blank

• Check the helper output range. If the helper range (for example, E2:E5) is blank, your dependent list will be blank too.
• Confirm data validation points to the helper range. In the dependent cell, open data validation and verify “List from a range” references the correct output range.

The options do not match the selected category

• Look for hidden spaces. Category values must match your formula logic exactly. Trailing spaces in headers are a common issue.
• Confirm your category references. Make sure your formula is checking the correct category header cells and the correct selection cell (for example, A10).

The helper formula shows an error

• Test your formula in one cell first. Before applying it to an entire range, test the logic on a single row.
• Verify named ranges exist. Go to Data > Named ranges and confirm your named ranges are spelled correctly.

Quick debug checklist

1. Confirm all named ranges exist and contain the correct data.
2. Confirm the category drop-down works and returns the expected text.
3. Confirm the helper output range is populated and has no errors.
4. Confirm the dependent cell’s data validation points to the correct helper output range.

Frequently Asked Questions

How Do I Create a Dependent Drop-Down List in Google Sheets for Multiple Rows?

To create a dependent drop-down list for multiple rows, apply data validation to the category column first (for example, A10:A200). Then create a helper output area that returns the correct options per row, and apply data validation to each dependent cell so it references the matching helper output range for that row.

How Is a Subcategory Drop-Down List Different in Google Sheets and Excel?

Both Google Sheets and Excel support drop-down lists, but the workflow and functions differ. Excel’s data validation tools support list sources in different ways, while Google Sheets dependent drop-downs often use helper ranges and functions like INDIRECT to reference dynamic lists.

Can You Have Multiple Data Validation Rules in One Cell in Google Sheets?

Google Sheets supports one active data validation rule per cell. If you need more complex logic, the typical approach is to use helper cells or helper ranges to compute what the drop-down should show, and then validate against that result.

Conclusion

Google Sheets is one of the best tools for structured data entry, and dependent drop-down lists are a practical way to speed up entry while reducing errors. In this article, we covered how to create a dependent drop-down list in Google Sheets using both a standard method and a multi-row method.

To go further, learning Google Sheets conditional formatting based on another cell value is a strong next step, since conditional formatting complements drop-down list workflows and improves readability.

Related guides you may find helpful:

• Data validation in Google Sheets
• Multiple selections in Google Sheets drop-down lists
• How to make a drop-down list in Google Sheets