How to Create a Dependent Dropdown List in Google Sheets

Dependent dropdown lists are a type of dropdown list where the options available in the list depend on the value selected in another dropdown list. This type of dropdown list is useful when you design data entry forms or create dashboards with options to automatically change their content based on the user’s selection. Using dependent dropdown lists can help ensure the data integrity and accuracy of your work, and also save you time. Let’s see how you can create a dependent dropdown list in Google Sheets.

When do you need dependent dropdown lists in Google Sheets?

Dropdowns play a vital role in reducing data entry errors. They provide users with a limited set of valid choices, effectively preventing the input of inconsistent or incorrect data. This advantage finds application in numerous aspects of our daily tasks.

For instance, consider the scenario of crafting a data entry form. By incorporating dropdowns and dependent dropdowns, you can restrict users from inputting values not found within the predefined list. This approach ensures that your dataset only contains anticipated and accurate values.

Furthermore, dropdowns offer versatility across various contexts. One notable example is the creation of interactive dashboards. These dashboards leverage dropdowns to offer user-friendly interfaces, enabling users to effortlessly modify the displayed dataset by selecting relevant options from the dropdown lists.

Additionally, dropdown menus serve as effective tools for presenting data to your audience in an engaging manner. Through these menus, you can showcase information attractively and enhance the overall user experience.

Creating a dependent dropdown list in Google Sheets

Let’s say you are going to create a data entry form that consists of two fields with dropdown lists, namely “Region” and “Country“. A region consists of many countries. The country dropdown list should be populated dynamically as the region changes.

To do this, first, you need to create a dropdown list using data validation. Then you need to create a candidate list for the second dropdown list. This can be done using, the Google Sheet QUERY function. Then you can use it to generate the dependent dropdown list.

Creating the main dropdown list

This is straightforward. You can use the data validation option to create the main dropdown list.

  1. Select the cell you want to create the dropdown 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 dropdown is ready, and it shows only the unique values in the region list.

Creating the dependent dropdown list

Before creating the dependent dropdown 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:

=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.

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 dropdown 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 dropdown 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“.

Wrapping Up

In this tutorial, I showed you how to create a dependent dropdown list, which is dynamically populated based on the selection of another dropdown 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.

8 thoughts on “How to Create a Dependent Dropdown List in Google Sheets”

  1. The issue i have is that when i select a different option in drop down 1, the selected option of drop down 2 does not switch to the first item in the new list, it stills shows the previously selected item. Is there a way to reset the selection of drop down 2?

    Reply
  2. This is great! I’m just struggling with how to use it with two different sheets. I have a master sheet with continents and regions and the other sheet for others to use to search the data on the master sheet. Right now the drop boxes on the Search sheet shows all regions even if the drop box for the continent is selected on one continent. How do I tinker this?

    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
  4. 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
  5. 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

Leave a Comment

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

Share via
Copy link