Drop-down lists can be very useful to create in Google Sheets when you want to show a specific list of items for users to select from.
In Google Sheets, you can also create dependent drop-down lists. This means that when a user makes a selection in the first list, the second list will only display relevant values depending on what they selected.
In this tutorial, I will show you how to create a dependent drop-down list in Google Sheets.
Creating a Dependent Drop Down List
Here is what you need to do to create a dependent drop-down list in Google Sheets:
1. The first thing you need to do is create the lists that contain the values you want in your drop-down lists. An important thing to note is that your header rows cannot contain any spaces. This is important because we will be creating named ranges with them.
2. Next highlight the range of cells that you want in your first drop-down list
3. In the top menu select Data and then select Named ranges
4. In the top text field, type the name that you used in your header row for your drop-down list. Select Done when you are finished
5. Next we need to create a named range for your second list. Select Add a range
6. Type the name of the header column, and make sure to set your cell range for the range of this second list. In this example it is B2:B6. Select Done when you are finished
7. Next, click on the cell where you want the first drop-down list to appear. In my example, I selected cell D1. Then, go into the top menu and select Data, then select Data validation
8. For the criteria section, you want to have a list from a range set up for the range that contains your header rows. In my example it is A1:B1. Also make sure the checkbox for Show dropdown list in cell is selected. Select Save when you are finished.
9. In the cell that you selected in step 7, you should now see a drop-down list that contains your two lists
10. In any cell, we need to now create an INDIRECT formula that references the cell our first drop-down list is in. In my example, I am using cell D1, so my INDIRECT function is =INDIRECT(D1)
11. Next select the cell next to your first drop-down, list. I have selected E1 in this example.
12. In the top menu select Data, and then select Data validation
13. For the list from a range criteria, you need to select the range of cells that contains the values from your INDIRECT function. In my example you can see these values are in cells A8:A12, so that is what I input into this box. Make sure the “show dropdown list in cell” checkbox is marked, and select Save
14. You have now created your dependent drop-down list. You should see that as you select the first list, the options in the second list change depending on your first selection
Closing Thoughts
We have known shown you the basics of creating a conditional drop-down list. Whatever you select in the first list will change the options in the second list.
This is just scratching the surface of what is possible. Your drop-down list options are dynamic, so if your values in your ranges ever change, your options in the drop-down list will also change.
One tip that you might find useful is to leave a much larger range in step 13. This will allow room for your values to grow so your list stays dynamic.
You can also use this method and create lists from another sheet, you will just have to change your cell references to pull from a different sheet. This can be useful if you don’t want your list and all the options to appear on the same sheet.
More Google Sheets Tutorials:
How to Transpose Data
How to Create Dynamic Named Ranges
How to Use the IMPORTRANGE Function