In this article:

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

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.

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started