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

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

A dependent drop-down list is a type of data validation that changes based on the selection of another drop-down list.

This can be a great way to make your data more interactive.

For example, let’s say you have a list of countries in one drop-down list and a list of cities in another.

You want the city drop-down list to change based on the country drop-down list selection.

This guide will show you how to create a dependent drop-down list in Google Sheets.

How To Set Up a Dynamic Drop-Down List in Google Sheets

Here’s how to set up a dynamic drop-down list in Google Sheets.

Step 1

First, set up the options for the first drop-down list in a single row.

These options will also act as the header for each column.

Step 2

Next, input the choices you want to display in the second drop-down menu.

In this example, we’ve set up a list of cities for each continent option.

Step 3

We’ll now create a named range for each sub-group. Select the first group of options under the first header.

Step 4

Click on the Named ranges option under the Data menu.

Provide the header as the name of the range and click Done to proceed.

Step 5

Create named ranges for each sub-group.

Step 6

Next, select an empty cell to place the first drop-down menu.

Step 7

Click on the Data validation option under the Data menu.

Step 8

In the Data validation rules panel, select Dropdown (from a range) as the criteria option. Provide the range that includes the options you want for the first drop-down list.

Step 9

Next, we’ll populate a new range with the appropriate sub-group using the INDIRECT function.

The INDIRECT function allows the user to return a named range.

Step 10

Create a new drop-down menu with the INDIRECT output as options.

You should now have a dependent drop-down list that shows the appropriate options based on the first drop-down list.

If we change the option in the first drop-down list, the second drop-down list will update with new options.

Summary

This guide should be everything you need to create a dependent dropdown list in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.