How to Create a Dependent Drop-Down List in Google Sheets

The drop-down lists are useful when you are creating data entry forms or creating interactive dashboards to present your data. While creating drop-down lists, you may come across situations where one drop-down list need to be populated dynamically as the value of the other drop-down changed. In other words, needs to create dependent drop-down lists.

Creating a dependent drop-down list is very easy in Google Sheet compared to Microsoft Excel. Here I am using the powerful QUERY() function to generate the dependent list.

Let’s say you are going to create a data entry form which consists of two fields with drop-down lists namely “Region” and “Country“. A region consists of a number of countries. The country drop-down list should be populated dynamically as the region changed.

Creating Main Drop-down List

This is straightforward. You can use data validation option to create the main drop-down list. First, select the cell you want to create the drop-down list. Then 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 default option). Select the region range for the second box (Data range). Here I have named the range as “Regions“. So I type the range as Regions. Add validation text as necessary and click save. Now your main drop-down is ready and it shows only the unique values in the list.

Creating main dropdown - Google Sheet

Creating the Dependent Drop-down List

To create this list, first, create the candidate list by filtering the country list by the selection of the main list. For this purpose, I am going to use the QUERY() function. The function looks like following:

Here I am using “Named Ranges” to make the function more readable. “A” and “B” are column labels.

 Creating filtered list for dependent dropdown list

The argument CountryByRegion is the table which includes the list of regions and countries. And the Region refers to the cell which contains the main drop-down list. This function filters the countries by the selected region and creates a filtered list as shown in the following animation.

Now you can use this 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.

Creating Dependent Dropdown List

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: