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.
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.
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.
Click on the range A2:A5 (options under 'Genre'). Go to 'Data' and select 'Named ranges'.
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.
Select cell A10 for your main drop-down list. Go to 'Data' and select 'Data validation'.
Click 'Add Rule'.
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'.
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.
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.
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]"), ...))
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'.
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.
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.
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.
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.
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.
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.
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.
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.
