In this article:

How to Create a Dependent Drop-down list in Google Sheets

May 8, 2024

What is a Dependent Drop-down List?

A dependent drop-down list in Google Sheets is a dynamic feature that allows you to create a series of drop-down lists where the options in one list depend on the selection made in another list. This is useful when you want to narrow down choices for the user based on their previous selections.

Example: If you have two drop-down lists – one for countries and another for cities – selecting a country in the first drop-down can determine the list of cities that appear in the second drop-down.

How to Create a Dependent Drop-down list in Google Sheets

In the example below, we will focus on a bookstore scenario where sorting books by categories like 'Genre', 'Author', 'Year', and 'Publisher' can streamline inventory management. Follow the steps below to create a dependent drop-down list in Google Sheets. 

1. Define Named Ranges for Each Category

Organize your categories ('Genre', 'Author', 'Year', 'Publisher') and their options in columns A, B, C, D respectively, with options listed in rows 2 to 5.

dependent drop down list google sheets

Click on the range A2:A5 (options under 'Genre'). Go to 'Data' and select 'Named ranges'.

google sheets dependent drop down

Name this range 'Genre'. Click 'Done'. Repeat this for 'Author' (B2:B5), 'Year' (C2:C5), and 'Publisher' (D2:D5). Name these ranges 'Author', 'Year', and 'Publisher' respectively.

dependent drop-down list google sheets

2. Create a Drop-down List for Categories

Select cell A10 for your main drop-down list. Go to 'Data' and select 'Data validation'.

dependent dropdown google sheets

Click 'Add Rule'.

how to create a dependent drop down list in google sheets

Then choose 'Drop-down (from a range)' and select the range A1:D1, which contains your category headings. Save the data validation. Now, cell A10 will have a drop-down with 'Genre', 'Author', 'Year', 'Publisher'.

how to create dependent drop down list in google sheets

3. Prepare for Dynamic Range Selection

Select cell E2:E5. This will be the area where you paste the formula for showing different data based on the category selected in cell A10.

dependent drop down list in google sheets

Click 'Data' and select 'Data Validation.

Choose 'Drop-down (from a range)'. Leave the range field empty for now as it will be dynamically set by a formula.

4. Use the Formula for Dynamic Range Selection

In the range E2:E5, use the formula:

=IF(A10="Genre", INDIRECT("Genre"), IF(A10="Author", INDIRECT("Author"), IF(A10="Year", INDIRECT("Year"), INDIRECT("Publisher"))))

This formula checks the category selected in A10 and sets the dynamic range in E2:E5.

You can use this template to adapt the formula:

=IF([Main Dropdown Cell]="[Category1]", INDIRECT("[Named Range1]"), IF([Main Dropdown Cell]="[Category2]", INDIRECT("[Named Range2]"), ...))

  • Replace [Main Dropdown Cell] with the cell where the main category is selected (e.g., A10).
  • Replace [Category1], [Category2], etc., with your category names.
  • Replace [Named Range1], [Named Range2], etc., with the corresponding named ranges for each category.

5. Set Up Data Validation for the Dependent Drop-down

Click the drop-down in cell B10 and click the edit icon.

Select 'Drop-down (from a range)' and choose the range E2:E5 where you inputted the formula earlier. Click 'OK' and then 'Done'.

6. Testing the Dependent Dropdown

Once you've completed the above steps, test the functionality. Select a category in A10, like 'Genre'.

The dropdown in B10 should then show the options related to 'Genre' (Fiction, Non-Fiction, etc.). Changing the category in A10 should update the options in B10 accordingly.

How to Create a Multiple Dependent Drop-down list in Google Sheets

In the example below, we want to organize books in a bookstore by categories like 'Genre', 'Author', 'Year', and 'Publisher'. By setting up multiple drop-down lists, we can easily select and see different book details in each row. Simply follow the steps below. 

1. Input Information in your Spreadsheet 

In your sheet, make sure your categories ('Genre', 'Author', 'Year', 'Publisher') and their options are listed in columns A, B, C, D, from rows 2 to 5. 

2. Set Up Data Validation for Category Selection in Multiple Rows

Go to cell A10. This is where you'll pick a category like 'Genre'. Click 'Data' and select 'Data validation'.

Click 'Add rule' under 'Data validation rules.'

Select 'Drop-down (from a range)' and enter A1:D1 as the range. This range includes your categories. Do the same for cells A11, A12, and A13.

3. Apply Data Validation for Dependent Options in Multiple Rows

In cell B10, this is where you'll see options based on what you chose in A10.

Apply data validation, but don't set the range yet. It will change based on what you pick in A10. Repeat these steps for cells B11, B12, and B13.

4. Insert the Multi-Row Formula

We use a special formula to make sure the right options show up in B10 to B13 based on what you choose in A10 to A13. To create a formula range, pick a space for the formula, like E2:H5. In E2, put this formula:

=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=A1, INDIRECT("Genre"), IF(TRANSPOSE(A10:A13)=B1, INDIRECT("Author"), IF(TRANSPOSE(A10:A13)=C1, INDIRECT("Year"), INDIRECT("Publisher")))))), ""))

You can use the template below in E2 (or your chosen range):

=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=[Category1 Cell], INDIRECT("[Named Range1]"), IF(TRANSPOSE(A10:A13)=[Category2 Cell], INDIRECT("[Named Range2]"), ...)))), ""))

You can replace [Category1 Cell] with the cell that contains the first category header (like A1 for 'Genre').

Replace [Named Range1] with the corresponding named range for this category (like 'Genre').

Repeat for other categories and their named ranges.

5. Link the Dependent Drop-downs to the Formula

To edit B10's data validation, click the cell 'B10' then select the edit icon.

Choose 'Drop-down (from a range)' and set it to E2:H2.

Do the same for B11 (E3:H3), B12 (E4:H4), and B13 (E5:H5). These ranges change based on your selections in A10 to A13.

6. Testing the Setup

To verify the functionality of the multiple dependent drop-down, pick 'Genre' in A10.

You should see genre options in B10. Test this for A11, A12, and A13, and check if B11, B12, and B13 show the right options.

We hope that you now have a better understanding of how to create a dependent drop-down list in Google Sheets. If you enjoyed this article, you might also like our article on how to create a Box and Whisker Plot in Google Sheets or our article on how to create a multiple dependent drop-down list in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->