We can utilize lists in Google Sheets to portray our data. One of those ways is using a Google Sheets dependent drop down list, which makes data entry more efficient and free of errors.
The dependent drop-down lists can also be useful in a variety of situations. We can create a dependent drop-down list where the first list choice determines the options for the second. In this article, we will discuss how to speed up data entry by creating a dependent drop down list in Google Sheets. Read on to learn more.
Table of Contents
What is a Standard Dependent Dropdown List?
Let’s discuss what a Google Sheets drop down list based on another cell range is and how it helps users write their data in a simple, error-free way.
As mentioned before, the purpose of a drop-down list is to allow the users to build a drop-down list where when the user selects a category in cell A, they are presented with options in cell B accordingly.
An example of a situation where this type of drop-down list may be useful is if you are surfing through a website looking for a specific genre of music, you may find it easier if it was divided into different categories and all the songs you like were just one click away, and you would know what songs are available to stream on the site under each genre.
Other situations could be if you were looking for books written by a specific author and wanted to know what books the website has that are written by that author.
Here’s my video on how to create a dependent drop down list in Google Sheets:
A Step-By-Step Guide to Making a Dependent Drop Down List in Google Sheets
To show you how to create a dependent drop-down list Google Sheets, I will use the following example of a shirt store with four different headings (categories), namely, Color, Size, Type, and Year.
Each heading has four different options within. For example, the heading or category “Color” has four options: Blue, Red, Green, and White.
So, I’m going to create a dependent dropdown in cell A10 that will let me select a category, and once I select it, a dropdown list will show up in cell B10 listing all the options in my selected category. Let’s begin!
Step 1. Select the cell range with data and right-click, then define the named range.
Defining the cell ranges for a Google Sheets drop-down list based on another cell range is helpful since it makes it easy to use them in formulas later on. In the Screenshot above, I have named all the cell ranges since I will be using them in formulas later.
For example, I have named cell range A3:B6 as Shirt_Color since it is the range containing all the color options in the shirt category. Here’s how it looks after naming the range:
Step 2. Use the data validation option to create a dropdown list for the different categories.
Once in the data validation menu, select list from range, and select the cell range with the headings (the categories containing different options).
In the screenshot above, I have selected the cell range A2:D2 since It contains all the categories (Color, Size, Type, and Year).
Step 3. After creating a dropdown for the categories, create a dropdown for the options. 
In the screenshot above, I have selected the cell range E2:E5 since this is where I will paste the formula showing different data based on the categories I select in cell A10.
Step 4. Use the formula:
=IF(EXPRESSION,INDIRECT(“CELL-REFERENCE-IF-TRUE”),INDIRECT(“CELL-REFERENCE-IF-FALSE”))
In the screenshot above, I have used the formula:
=IF(A2=A10,INDIRECT(“Shirt_Color”),INDIRECT(“Shirt_Size”)
Because I want Google Sheets to look for the category in cell A10 and display the corresponding data in cell E2:E5.
This will help my dropdown in cell B10 show the relevant data based on the selected category in cell A10.
And done! I have successfully created a dependent dropdown in cells A10 and B10. Once I completed all the steps above here’s what the dropdowns in cells A10 and B10 looked like:
Multi-Row Dependent Dropdown
A Multi-row Google Sheets dynamic drop down list is fairly simple to create and is almost identical to the standard method. But, there are two key differences between Multi-Row and Standard Dependent Dropdown lists—The formula used.
The Formula
In the screenshot above, I have used the formula:
=ARRAYFORMULA(IF(LEN(A10:A),TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A)=A2,INDIRECT(“Shirt_Color”),IF(TRANSPOSE(A10:A)=B2,INDIRECT(“Shirt_Size”),IF(TRANSPOSE(A10:A)=C2,INDIRECT(“Shirt_Type”),INDIRECT(“Shirt_Year”)))))),””))
The standard formula I used before only supports a few values, whereas this formula supports multiple values and can create multiple rows of Google spreadsheet dynamic list options.
Data Validation
In the screenshot above, I have selected a horizontal cell range E2:H2 since this formula displays the data horizontally as opposed to the previous formula, which did so vertically.
Also, as you can see, there are multiple rows with dependent dropdown lists, here’s how i created them:
Step 1. I went to each cell going down from Cell A10 to A13 and added data validation into each of them individually. The data validation was the same as the standard method, meaning I selected List from range and then the cell range with the headings A2:E2.
Step 2. Similar to before, I went to each cell going down from Cell B10 to B13 and added data validation into each of them individually. The data validation was List from range, and the cell range was E2:H2 for Cell B10, E3:H3 for cell B11, E4:H4 for cell B12, and E5:H5 for Cell B13.
If you’re having trouble following along, here’s a template for you.
Having trouble? Check out our other article where we discuss in detail How to Make Multiple Selections in Drop-Down Lists in Google Sheets.
Important Consideration
While the drop-down list feature provides many advantages when working in Google Sheets, you cannot have data validation show a drop-down list when it refers to a formula that relates only to a range of cells.
I also covered how to make a drop down list in Google Sheets. You’ll want to make sure you’re familiar with that process before you move onto more complex subjects like conditional drop menus.
The best way to solve this problem is to make as many lists as there are drop-downs. For example, if you have five drop-downs, you’ll need five sets of lists, each referring to the corresponding drop-down. This could be a problem if you wanted to create drop downs and their dependent drop downs for many rows. For now, we can only hope that Google provides us with a solution to this.
Frequently Asked Questions:
How Do I Create a Dependent Drop-Down List in Google Sheets for Multiple Rows?
In order to create the dependent drop-down list. You must use the following steps:
- First, Go to the cell where you want the list to appear and select Data > Data Validation from the menu, just as you did for the first list.
- Navigate to Criteria in the box that appears.
- In the drop-down box, select List from a range, and then enter the cell range containing the list items displayed by the INDIRECT function.
- Check the box next to Show dropdown list in cell, fill out the invalid data and appearance settings as desired, and click Save.
You have now completed creating your dependent drop down list.
How Do Is a Sub Category Drop-Down List Different in Google Sheets and Excel?
Although both Google Sheets and Excel offer the drop-down list feature, there are a few things that differ in the function. Whilst Excel offers to create a subcategory, Google Sheets does not. The correct way to do it in Excel is to
- Select the cells that you want to contain the lists.
- On the ribbon, click DATA > Data Validation.
- In the dialog, set Allow to List.
- Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.
And now you have your very own subcategory drop-down list.
Can You Have Multiple Data Validation in One Cell Google Sheets?
If you’re wondering whether you can have dynamic data validation in Google Sheets, the answer is yes. All you have to do is:
- Click on the Data Validation dialog box, and make sure the Cell range refers to the cell where you want the drop-down.
- Once you come across Criteria, you must select List from a range and choose the range with the items you want to show in the drop-down.
- After you have done everything, simply click on Save.
Conclusion
Google Sheets has always proven to be one of the best platforms to work with when using a spreadsheet. The drop-down feature is one of the many other valuable features Google Sheets offers that makes our work easier. In this article, we showed you how to make a dependent drop down list in Google Sheets, both Standard and Multi-row dynamic.
In addition, to fully take advantage of the convenience Google Sheets has to offer, learning Google Sheets Conditional Formatting is a must, as it also compliments dropdown list functionality. I hope this article provided you with all the information you needed. See you next time!
