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 needs to be populated dynamically as the value of the other drop-down changed. In other words, it 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 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.

Creating main drop-down list

This is straightforward. You can use the data validation option to create the main drop-down list.

  1. Select the cell you want to create the drop-down list.
  2. Go to Data> Data Validation.
  3. In the popup window, for the first box of the Criteria field, select “List from a range” (yes, it is the default option).
  4. 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.
  5. 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() function. The function looks like the following:

In the above function, I have used “Named Ranges” to make the function more readable. “A” and “B” are column labels.

The argument CountriesByRegion is the table which includes all the list of regions and countries (or you may use A1 notation for this; e.g. A2:B213). And the Region refers to the cell which contains the main drop-down list. The above function filters the countries of the selected region and creates a filtered list, as shown in the following video.

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; “How to Create a Dependent Dropdown List in Google Sheets and apply to Multiple Rows“.

4 thoughts on “How to Create a Dependent Drop-Down List in Google Sheets”

  1. You are assuming that the people how came here does know how to do the CountryByRegion, but it is not the case.
    To be complete, should all parts be shown .
    Cheers!

    Reply
  2. Thank You, Gonzalo for your comment.

    CountriesByRegion is the name given to the range of cells which performs this query on. You can read more about named range from this link.

    I updated the post and added the link to the above Google Sheet, so you make a copy to your drive and try it.

    Reply
  3. Hi, Is there any way to make the dependent drop-menu usable for an entire column?
    I’ve created a double tier dependent drop menu, and now I want to have this dependent drop-menus in all the cells of the 3 columns for this/a.k.a. in multiple rows.

    (Also, thank you sharing your example sheet. I caught a little error you made – Your drop menu of filtered countries doesn’t include all the countries in the filter. I think your defined range for that section is cut short. For example, if you select region: “Sub-Saharan Africa”, you are unable to select Zimbabwe.)

    Thank you!

    Reply

Leave a Reply

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