How to Create a Dependent Drop Down List in Google Sheets

By

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:

Dependent Drop Down List in Google Sheets - Dataset

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

.

Dependent Drop Down List in Google Sheets - named ranges
  1. 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
  1. 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
  1. Now click on the Add a range option
  2. Use the same steps shown above to create a named range for Vegetables.
  3. 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
  1. Select the cell where you want to create it
  2. Go to Data > Data Validation.
Dependent Drop Down List in Google Sheets - Data Validation
  1. In the Data Validation dialog box, select the ‘Criteria’ as ‘List from a range
  2. Specify the cells that contain the names (Fruits/Vegetables).
    Dependent Drop Down List in Google Sheets - DV Criteria
  1. Make sure ‘Show dropdown list in cellcheckbox is checked
  2. Click on Save.
Dependent Drop Down List in Google Sheets - DV Done
  1. This will create a drop-down list in cell D3.
    Dependent Drop Down List in Google Sheets - DD1
  2. Go to any empty cell in the worksheet, or create another worksheet if you want, and enter the formula =INDIRECT(D3).
  3. 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.

Dependent Drop Down List in Google Sheets - Indirect
  1. Now the last step is to create a drop down list in E3 (which is our Drop Down 2).
  2. To do this, select cell E3 and go to Data > Data Validation.
    Dependent Drop Down List in Google Sheets - DV
  3. 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).
  4. 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
  5.  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 1: Create your column for the drop down lists and the data preparation

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 2: Under type, create the first drop down list.

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 3: Add the formula for the second drop down list.

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.

  1. Click Done.
Step 4: Add the second drop down list under product

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.
Go to range and remove the dollar signs in the range.
  1. Click Done to save changes.
  2. Drag the drop-down list to copy it to the rest of the cells in that column.
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.

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:
    Dependent Drop Down List in Google Sheets - warning

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:

Popular Posts

28 thoughts on “How to Create a Dependent Drop Down List in Google Sheets”

  1. This is good for one cell, but most people are using more than 1 cell on their sheet for data validation.
    If you are trying to use your phone for inputting data, scripts will not work.

    In xl you can use
    “=OFFSET(B1,MATCH(F1,B1:B9,0)-1,1,COUNTIF(B1:B9,F1),1)”
    For your dependent dataVAL
    You can, enter formulas in sheet dataVaL list though.

  2. Hi, thank you for your post! But if you use FILTER function, is very fast and simple metod to get result to dependent drop list.

    See you again

  3. What about the entire column? I am unable to do in entire column. What is the solution.

        • Regardless of the number of items you have for that list, using “A:A” will allow any number of items for your first category.

          ie. if you had A1:A10, you will be limited to only 10 items, but using A:A, you can now have any number of items for that list.

    • Hi Rafael, I need this thing for multiple row. how does it work. i applied same thing one row which is working and copied in other row. but it doesn’t work. pls help. suppose i hv 10 students which have to give the option either fruit or vegetable and then they will choose 2nd drop down list.

  4. What I did to solve some of the problems in the comments:

    1) For each row get the the validation data in different columns, but in the same row (I used indirect, but you can use vlookup, etc..)
    2) Get google sheets to validate the list of values in the row
    3) Strangely, google sheets doens’t let you use formulas, so when i copy the cell format to the rows below, it gets stuck with the first row values. So I just did this formula on excel and uploaded to google drive and opened with google sheets. Then copied this sheet to the worksheet I was using previously. Then I just copied the cells to the cells I wanted the validation format.

  5. Don’t use named ranges for dependent dropdowns. They have their limits (https://support.google.com/docs/answer/63175), it’s difficult to create and manage many of them, etc. Instead use new sheet with generated by formulas (like, INDEX, FILTER, QUERY, ARRAYFORMULA etc.) dependent dropdown options, and use these ranges. This is very powerful and flexible. You can also hide this sheet if necessary later.

  6. Thanks very much! I saw an option with Scripts but that option is not being shown in my Google Sheets. This worked for me!!
    Awesome explanation

  7. Is there a way to do the dropdown on every row? which will depend on the value beside it. For example column A will hold the first dropdown (fruits or vegetables) and B dropdown will depend on column A.

  8. Hi

    I am having issues. I have a project listing that has individual Project Managers. I can get Project Manager to work to see their Projects but I need to Select a Project and have correct Project Manager show

    Wayne

  9. Thanks for the article! I’ve created a very simple dependant drop down list for my in excel using the above method. The two columns are “Heading” and “Subheading”, with subheading drop-down options being dependent on the value in Heading. I have tested it in both excel and WPS spreadsheets, and it works fine.

    However, when i upload to google docs, instead of showing me the correct dropdown list in subheading, it displays “loading”… Any idea why this is occurring and how to fix this?

  10. After reading the comments, I am not sure if I have a similar problem.
    I want to create a list of dependent drop downs and then do a data validation for all.
    If I am using your example, you have the data in D3 as vegetables/fruits and then the dependent drop down in E3 based on the selection in D3.
    I would then want to have in D4 – D10 all cells with data validation allowing me to select fruit or vegetables and then have in cells E4 – E10, dependent drop down lists based on the selection in D4-D10.
    Is there any way to do this?
    Thanks

    • Did you ever figure this out, I am having a similar issue with only the first cell of the dropdown list one working with dropdown list two. I thought I could just copy and paste the down the sheet but not sure how to do it. I hope I am making sense.

      • I’m having the same issue – it only works on the top row that I set the data validation – and when i copy/paste down the data validation as you mention @Yvonne it’s also failing for me. I’ll have 100,000 data rows requiring a dynamic drop down, and it doesn’t prove successful. I don’t know Scripts, so this is seemingly out of reach. I miss Excel!!

      • same issue for me – it is only successful in the top row. everything i’ve googled seems this is not feasible in sheets. I miss Excel! I’m not familiar with Scripts, so seemingly out of luck.

  11. 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?

  12. 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?

    Am I making sense?

    Thank you!!

    • Hey Jenna,

      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.

Comments are closed.