The drop-down lists are useful when you are creating data entry forms or creating interactive dashboards to visualize your data. Sometimes, you may need to create drop-down list depend on another dropdown list, in other words, a dependent drop-down list. In this tutorial, I will show you how to create a second drop-down list, which is populated dynamically as the value in the first dropd-down list change.
Creating a dependent drop-down list in Google Sheets is easy compared to Microsoft Excel. Here I am using the
Creating dependent drop-down list in Google Sheets
Let’s say you are going to create a data entry form that consists of two fields with drop-down lists, namely “Region” and “Country“. A region consists of many countries. The country drop-down list should be populated dynamically as the region changed.
To do this, first, you need to create a drop-down list using data validation. Then you need to create a candidate list for the second dropdown list. This can be done using, Google Sheet
QUERY function. Then you can use it to generate the dependent drop-down list.
Creating main drop-down list
This is straightforward. You can use the data validation option to create the main drop-down list.
- Select the cell you want to create the drop-down list.
- Go to Data> Data Validation.
- In the popup window, for the first box of the Criteria field, select “List from a range” (yes, it is the default option).
- For the Data Range, we use our region list. Here I have named the range as “Regions“. So I type the range as Regions in the Data Range box.
- Add validation text as necessary and click Save.
Now your main drop-down is ready, and it shows only the unique values in the region list.
Creating the dependent drop-down list
Before creating the dependent drop-down list, you need to create a candidate list (List of countries in the selected region) to be used in the data validation. This list should be dynamically updated when you choose the region from the main dropdown list.
Creating the candidate list
For this purpose, I am going to use the Google Sheets
=query(CountriesByRegion,"select B where A='"&RegionDL&"'")
In the above function, I have used “Named Ranges” to make the function more readable. “A” and “B” are column labels.
Set data validation
Now you can use the above filtered list to create another data validation rule for the country drop-down list (dependent list) as we created the region list (main list) at the beginning.
You can make a copy above Google Sheet from this link.
You can apply the dependent dropdown list to only one cell using this method. If you want to apply the dependent dropdown list in multiple rows, you may read; “Multi-Row Dependent Dropdown List in Google Sheets“.
In this tutorial, I showed you how to create a dependent drop-down list, which is dynamically populate based on the selection of another drop-down list on Google Sheets. This will be helping you to create Google Sheets data entry form or interactive dashboards to visualize your data.
However, this method cannot be applied to multiple rows. If you want to create a multi-row dependent dropdown list in Google Sheets, you can learn it from here.