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 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 (also called conditional drop-down list):
Dependent Drop Down List in Google Sheets – Demo
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).
Related: Creating a Dependent Drop Down List in Excel.
Creating a Dependent Drop Down List In Google Sheets
Below is the dataset that I will use to create a dependent drop down list:
Dependent Drop Down List in Google Sheets – Dataset
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.Dependent Drop Down List in Google Sheets – 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’.Dependent Drop Down List in Google Sheets – Fruits NR
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.Dependent Drop Down List in Google Sheets – Fruits Done
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.Dependent Drop Down List in Google Sheets – Vegetables NR
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.Dependent Drop Down List in Google Sheets – 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).Dependent Drop Down List in Google Sheets – DV Criteria
Make sure ‘Show dropdown list in cell’ is checked and click on Save.Dependent Drop Down List in Google Sheets – DV Done
This will create a drop-down list in cell D3.Dependent Drop Down List in Google Sheets – DD1
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 a dynamic dependent drop-down list and if you change the drop-down in cell D3 from Fruits to Vegetable, the list would change and show the list of vegetables.
Dependent Drop Down List in Google Sheets – Indirect
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.Dependent Drop Down List in Google Sheets – DV
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.Dependent Drop Down List in Google Sheets – DV criteira
Click Save.
Now when you make a selection is Drop Down 1, you will notice that the items in Drop Down 2 changes accordingly.
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
hoolysense
