How to Create a Dependent Drop Down List in Google Sheets

A drop down list in Google Sheets can be used when you want to show a list of options and the user can select from it. It helps in speeding up data entry and makes it more reliable.

In real life, there would be a need to create a dependent drop down list where the once you make a selection in a drop-down menu, the next drop down only shows relevant options based on the first selection.

Below is an example of a dependent drop down list in Google Sheets:

In the above example, you can see that the options in Drop Down 2 changes based on what is selected in Drop Down 1. It happens as Drop Down 2 is dependent on Drop Down 1.

In this tutorial, I will show you how to create a dependent drop down list in Google Sheets (also called conditional drop down list).

Creating a Dependent Drop Down List in Google Sheets

Below is the dataset that I will use to create a dependent drop down list:

Here are the steps to create a dependent drop down list in Google Sheets:

Select the cells that have the fruits (A2:A5).

Go to Data –> Named Ranges.

In the Named ranges pane, enter the name for this named range. Note that the name needs to be exactly the same as it’s parent category. In this case, it would be ‘Fruits’.

Click on Done. This will create the first named range with the name ‘Fruits’ that would refer to the cells that have the fruit names.

Now click on the Add a range option and use the same steps shown above to create a named range for Vegetables. You need to create as many named ranges as many options you want to show in the first drop down list.

Now it’s time to create the first drop down list that shows Fruits/Vegetables as the options. Select the cell where you want to create it and go to Data –> Data Validation.

In the Data Validation dialog box, select the ‘Criteria’ as ‘List from a range’ and specify the cells that contain the names (Fruits/Vegetables).

Make sure ‘Show dropdown list in cell’ is checked and click on Save.

This will create a drop-down list in cell D3.

Now before we create the second drop down, go to any empty cell in the worksheet, or create another worksheet if you want, and enter the formula =INDIRECT(D3). This would list all the items from the category selected in Drop Down 1. This is dynamic and if you change the drop down in cell D3 from Fruits to Vegetable, the list would change and show the list of vegetables.

Now the last step is to create a drop down list in E3 (which is our Drop Down 2). To do this, select cell E3 and go to Data –> Data Validation.

In the Data Validation dialog box, select the ‘Criteria’ as ‘List from a range’ and specify the cells that contain the result from the INDIRECT function. It is possible that the number of items in different lists are different (for example ‘Fruits’ category has 4 items and ‘Vegetables’ has 10 items). To handle this, select an appropriate cell range that should suffice all the categories. For example, in this case, I select 10 cells in case the list grows in future.

Click Save.

Now when you make a selection is Drop Down 1, you will notice that the items in Drop Down 2 changes accordingly.

Important Notes While Creating a Dependent Drop Down List in Google Sheets

Here are a couple of important points to note while creating a dependent drop down list in Google Sheets:

The named range could only be a single word. In case it more than one word, use underscore to join the words while creating the named range. For example, if the category is ‘Seasonal Fruits’ instead of ‘Fruits’, when you create a named range, name it Seasonal_Fruits.

While I create the Indirect formula right below the list, as a best practice, create it in a different worksheet. You can then hide the worksheet if needed.

When you select a category and an item within it (for example you select Fruits and then Mango), and then you change the Main category (for example selecting ‘Vegetables’), the dependent drop down list wouldn’t change. This means that it can happen that Drop Down 1 shows ‘Fruits’ as the category and Drop Down 2 has a vegetable name in it. However, Google Sheets will warn you by showing a red triangle at the top right of the cell. If you hover the cursor over that cell, you will see the warning as shown below:

Sparklines are miniature charts that you can create within a cell. These are quite useful when you’re creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers (maximum/minimum) visually. According

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work in the sheet simultaneously. In this tutorial, you’ll learn various to insert timestamp in Google

Google Sheets recently added an option to insert a checkbox in a cell. In this tutorial, I will cover everything you need to know about using checkboxes in Google Sheets, along with some useful examples. Click

Google Sheets is a relatively new spreadsheet tool in the market. It has quickly gained a lot of fan following and I see a lot of people migrating from other spreadsheets applications to Google Sheets.

In Excel you have an option to Format Control of the dropdown. This allows an input range and a cell link so that you can the input the value and then tie that cell to a formula. Anything like that in Google Sheets?

This is GREAT! I have a question though that I couldn’t locate in your article. I have a situation where I want to create drop downs and their dependant drop downs for many rows. For example in each row you will enter a date, a category and then it’s corresponding items.

The indirect formula only references 1 cell. Per your example D3. But when new data is entered into D4, I want the new dependant list to reference the category selected in D5 not D4.

How will this work if I need this to function time and again row after row?

Do I need to make a ton of indirects formulas in a new sheet and manually do individual validations for each dependant list to reference its unique indirect formula which points to that dependant’s category list?

Hey Jenna… Unfortunately, in Google Sheets, you can’t have data validation show a drop-down list when it refers to a formula (only to a range of cells). The best solution, in this case, is to create as many lists as there are drop downs. For example, if you have 5 drop downs, then you need to have five sets of lists, each referring to the corresponding drop down. I wish there was a better solution.

There is a way to do this using Scripts, but I haven’t delved into it yet. Will try and update this tutorial if the script method works.