How to Create a Dependent Drop Down List in Google Sheets

Here, I discuss how to create a dependent drop-down list in Google Sheets. This allows you to show a list of options that affects what a user sees on the next menu. Follow along for my step-by-step advice on dependent drop-down lists in Google Sheets. I provide screenshots and practical examples.

How to Create a Dependent Drop Down List Google Sheets

I already discussed the basics of a drop-down list in Google Sheets. That’s fundamental, and it allows you to show a few options a user might choose from. To make the most of it, though, you’ll want to use a dependent drop-down list in Google Sheets. That’s absolutely vital for showing only relevant information.

More specifically, you 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. My process here uses data validation, which is pretty fun.

Ready to dive in?

Below is an example of a dependent drop-down list in Google Sheets (also called a conditional drop-down list):

In the above example, I show that the options in Drop Down 2 change based on what is selected in Drop Down 1. It happens as Drop Down 2 is dependent on Drop Down 1.

In this guide, I show how to create a dependent drop-down list in Google Sheets (also called conditional drop-down list). Keep reading for the step-by-step instructions. Note that you may also be interested in my article covering conditional formatting.

How to Create a Dependent Drop-Down List in Google Sheets

So here’s my step-by-step guide. I’ll start with the five-step process, then discuss the specific formula needed to make it happen.

1. Select your cell range with target data and define the named range
2. Use Data validation option to create a drop-down list for different categories
3. Select list from range and select the cell range with the headings
4. Create a drop-down for the options
5. Input the following formula

=IF(EXPRESSION,INDIRECT(“CELL-REFERENCE-IF-TRUE”),INDIRECT(“CELL-REFERENCE-IF-FALSE”))

You’ll notice that this is an IF statement. It asks that, if a cell reference is “true,” it gives one option. If not, it gives the other. Let me give a practical example.

Creating a Dependent Drop Down List In Google Sheets

I created this practical example to show how the conditional drop-down list changes based on the data you choose. For this, we’ll talk about fruits and veggies. Here’s the dataset I used to create a dependent drop down list:

Ready to get started? Follow along to create a dependent drop down list in Google Sheets:

1. Select the cells that have the fruits (A2:A5).
2. Go to Data –> Named Ranges

.

3. 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’.
   

   

4. 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

.

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

.

8. Select the cell where you want to create it
9. Go to Data > Data Validation.

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

    

12. Make sure ‘Show dropdown list in cell‘ checkbox is checked
13. Click on Save.

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

    

15. Go to any empty cell in the worksheet, or create another worksheet if you want, and enter the formula =INDIRECT(D3).
16. This would list all the items from the category selected in Drop Down 1.

This is a dynamic 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.

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

    

19. 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).
20. 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.
    

    

21.  Click Save.

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

Related Reading: I also discussed how to create conditional formatting based on another cell in Google Sheets.

Creating a Multi-Row Dependent Dropdown List

A multi-row-dependent dropdown list is mostly similar to a normal dependent drop-down list. The difference comes in with the formula used. If we want to create a dependent drop-down list in more than one row, we can’t simply copy the formula to the rest of the rows.

The multi-row dropdown list uses an array formula, a transpose formula, and the IFLen formula, while a normal dependent drop-down list only needs the INDIRECT formula.

This is because we are going to have multiple rows, each with dependent drop-down lists, which makes it slightly more complex.

Here’s how to create a multi-row dependent drop-down list:

Step 1: Create your column for the drop-down lists and the data preparation. Make sure to name your ranges using the headers.

Step 2: Under type, create the first drop-down list.

1. Go to Data > Data validation.
2. Click Add rule
3. Choose dropdown from range.
4. Select cell A1:B1
5. Click Done

Step 3: Add the formula for the second drop-down list.

1. Go to the column for data and paste the following formula.

=ArrayFormula(IF(LEN(D2:D),TRANSPOSE(IF(TRANSPOSE(D2:D)=A1,INDIRECT(“Fruits”),IF(TRANSPOSE(D2:D)=B1,INDIRECT(“Vegetables”)))),””))

Step 4: Add the second drop-down list under product.

1. Go to Data > Data validation.
2. Click Add rule
3. Choose dropdown from range.
4. Select cell G2:Z2.

Tip: Use a larger range than your data for the drop-down so that you can make it dynamic and add more data later.

5. Click Done.

Step 5: Copy the data validation to the rest of the rows by clicking and dragging down the square at the bottom right corner for each column.

1. Before you copy the second drop-down list, go to the rule for that list.
2. Go to the range and remove the dollar signs in the range.

3. Click Done to save changes.
4. Drag the drop-down list to copy it to the rest of the cells in that column.

You now have a multi-row dependent list that is also dynamic, meaning it will update with any changes you make to your data.

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 is 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:
  

  

Frequently Asked Questions

How Do I Create a Dependent Drop-Down List in Google Sheets?

1. Select your cell range with target data
2. Use Data validation option to create a dropdown list for different categories
3. Select list from range and select the cell range with the headings
4. Create a dropdown for the options
5. Input the following formula:

=IF(EXPRESSION,INDIRECT(“CELL-REFERENCE-IF-TRUE”),INDIRECT(“CELL-REFERENCE-IF-FALSE”))

What is a Dynamic Drop-Down List?

This is when you select a value in a dropdown menu, and you want the data or values to be updated in another dropdown list. The second drop-down list depends on the value you pick in the first drop-down list and updates accordingly.

How Do I Link a Drop-Down List to Another Worksheet in Google Sheets?

1. Choose the cell you want the dropdown in
2. Right-click and select data validation
3. Click the grid image in the criteria input box(in the dialog box)
4. Click on the tab for the sheet you want in the What data? Sub menu
5. Hold shift and select the desired cells
6. Click ok

Can You Have Multiple Data Validation in One Cell Google Sheets?

Yes. Right-click on the column and select Data validation then, you can add your rules for data validation.

Final Thoughts

I love talking about conditional drop-down lists in Google Sheets. That’s why I created this guide. It showed practical examples of how to create dependent drop-down lists, how to use multiple data points, and how to use data validation in Google Sheets.

So that’s how you create a conditional drop-down list in Google Sheets (i.e., a drop-down list based on another cell’s selection) and also how you create a dynamic drop-down list. If you found this article useful, let me know. You can also sign up for once of my spreadsheet courses.

Related:

• INDEX MATCH Google Sheets Guide
• Google Sheets IF Function Guide
• How to VLOOKUP From Another Sheet
• How to Create a To-Do List in Google Sheets
• Google Sheets Attendance Templates