Skip to content

Dependent Dropdown Menu in Google Sheets

In this article, I will show you how to create a dependent dropdown menu in Google Sheets.

What is a Dependent Dropdown Menu?

A dependent dropdown menu is a list of options that changes depending on the selection of something else. Here is a simple example.

Let’s say you have a list of Makes and Models of cars like so.

The goal is to create two dropdown menus. The first dropdown menu allows the user to select the Make.

The second dropdown menu will have options that change depending on the first selection. For instance, if the user selects Ford for the first option, then the second menu will only contain Ford models to pick from.

This second menu is an example of a dependent dropdown menu because it depends on the selection of the first menu.

How to Create a Dependent Dropdown Menu

Follow the steps below to create the dependent dropdown menu.

  1. List the options for both menus in your spreadsheet.
  2. Create the first dropdown menu using data validation.
  3. Use the filter function to create a filtered list based on the first selection.
  4. Create the second dropdown menu using data validation and referencing the filtered list.

Keep reading to see each step in detail.

List the Options for Both Menus

The first thing you want to do is list the options for both lists in your spreadsheet.

Notice that the Models correspond to their proper Make by sharing the same row.

Create the First Dropdown Menu

To create the first dropdown menu, select the cell where you want to add the menu and go to Data Data Validation.

In the Data Validation Rules panel on the right, click on Add rule if needed, then select Dropdown (from a range) under Criteria.

For the range, select the cells containing the options for the first dropdown menu. Then click on Ok.

To change the style of the dropdown menu, you can click Advanced options and then change it to Arrow if you want.

Finally, click on Done.

Create a Filtered List

The next step is to create a filtered list that will change depending on the selection for the first menu we just created.

Start by selecting the cell you want to be the start of your list, then enter the following formula.

=FILTER(OptionsForSecondList, OptionsForFirstList=Menu1Selection)
=FILTER(B2:B7, A2:A7=F2)

This formula will filter the car Models depending on which Make the user chooses.

Create the Second Dropdown

You are now ready to create the final dropdown menu. Start by selecting the cell where you want the menu to be. Next, go to Data > Data Validation, and click on Add Rule.

In the Data Validation Rules panel on the right, select Dropdown (from a range) under Criteria.

For the range, enter the following reference.

=$D$2:$D

This is an open-ended reference that begins with the cell address of the first cell in the list and will include all of the options from the filtered list, no matter how big or small it may be.

Change the display style if you want to, and then click Done. Now whenever you select an option for the first menu, the available options for the second will change accordingly.

And that is how to create a dependent dropdown menu in Google Sheets.

Link to Practice File

Click the link below to make a copy of the file and try it yourself!

Dependent Dropdown Menu in Google Sheets

This Post Has One Comment

  1. I can’t wait to use this more often. The more I read your blog and watch your videos, the more comfortable I’m getting with data validation!

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Back To Top