google sheets - dependent drop down list for entire column multiple levels

Home » How to Create a Dependent Drop Down List in Google Sheets

Nội dung chính Show

How to Create a Dependent Drop Down List in Google Sheets
Creating a Dependent Drop Down List In Google Sheets
You May Also Like the Following Tutorials:
27 thoughts on How to Create a Dependent Drop Down List in Google Sheets
You may also like
Video liên quan

How to Create a Dependent Drop Down List in Google Sheets

Share Share Tweet Email Print

A drop down list in Google Sheets can be usedwhen 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):

google sheets - dependent drop down list for entire column multiple levels

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:

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.
In the Named ranges pane, enter the name for this named range. Note that the name needs to be exactly the same as its parent category. In this case, it would be Fruits.
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.
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.
Now its 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.
In the Data Validation dialog box, select the Criteria as List from a range and specify the cells that contain the names (Fruits/Vegetables).
Make sure Show dropdown list in cell is checked and click on Save.
This will create a drop-down list in cell D3.
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.
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.
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 categoryhas 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.
Click Save.

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

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

So this is how you can create a conditional drop-down list in Google Sheet (i.e., a drop-down list based on another cells selection)

Sumit

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.

Sumit
https://productivityspot.com/author/sumitbansal23/
How to Set Print Area in Google Sheets
Sumit
https://productivityspot.com/author/sumitbansal23/
How to Make a Line Graph in Google Sheets (Step-by-Step)
Sumit
https://productivityspot.com/author/sumitbansal23/
How to Split Text to Columns in Google Sheets (with Examples)
Sumit
https://productivityspot.com/author/sumitbansal23/
How to Convert Time to Military Time Format in Google Sheets?

27 thoughts on How to Create a Dependent Drop Down List in Google Sheets

Dave
October 19, 2020 at 20:35
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.
Andrea
October 7, 2020 at 15:12
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
OM Kumar Chaurasiya
August 29, 2020 at 13:24
What about the entire column? I am unable to do in entire column. What is the solution.
- rafael
  September 10, 2020 at 20:19
  A:A instead of the range
  - Syahmi Amir hamzah
    September 18, 2020 at 20:53
    What do u mean? Care to explain
    - Victor Vicencio
      September 18, 2020 at 22:09
      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.
- Sanjeev Barnwal
  September 22, 2020 at 22:12
  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 doesnt 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.
yojana
August 14, 2020 at 13:33
when i am selecting name i want automatically department and designation change.how to do it.
Funbi
August 13, 2020 at 18:58
I totally would love a feedback on this as well please.
carlos
July 16, 2020 at 21:35
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 doenst 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.
- Prasham Mehta
  October 6, 2020 at 15:06
  Can you please explain this a bit more?
Marilet
June 17, 2020 at 03:13
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
ruzip
June 5, 2020 at 14:39
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.
- Celine
  June 12, 2020 at 05:39
  I second this question help?
  - Romain
    June 12, 2020 at 19:39
    I also second this question help help?
    - Aung Aung
      July 28, 2020 at 14:54
      I also second this question please help?
- Ms. Boop 414
  August 17, 2020 at 06:21
  I want the answer to this also. And when I apply this to columns in row one, the indirect only references the first cell. Not the cell directly to the left.
  Help?
Wayne Poitras
May 7, 2020 at 00:20
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
Richard
May 4, 2020 at 11:31
Thanks for the article! Ive 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?
Jordan
January 14, 2020 at 09:33
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
- YVONNE B TAYLOR
  May 30, 2020 at 05:50
  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.
  - Stephanie Gustin
    June 4, 2020 at 19:50
    Im 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 its also failing for me. Ill have 100,000 data rows requiring a dynamic drop down, and it doesnt prove successful. I dont know Scripts, so this is seemingly out of reach. I miss Excel!!
  - Stephanie Gustin
    June 4, 2020 at 19:53
    same issue for me it is only successful in the top row. everything ive googled seems this is not feasible in sheets. I miss Excel! Im not familiar with Scripts, so seemingly out of luck.
    - Aung Aung
      July 28, 2020 at 16:06
      You got solutions ? I have same issue. Please, share.
Frank
January 18, 2019 at 06:53
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?
Jenna
March 2, 2018 at 22:24
This is GREAT! I have a question though that I couldnt 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 its 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 dependants category list?
Am I making sense?
Thank you!!
- Sumit
  March 2, 2018 at 23:24
  Hey Jenna,
  Hey Jenna Unfortunately, in Google Sheets, you cant 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 havent delved into it yet. Will try and update this tutorial if the script method works.

Comments are closed.

google sheets - dependent drop down list for entire column multiple levels

How to Create a Dependent Drop Down List in Google Sheets

Creating a Dependent Drop Down List In Google Sheets

Important Notes While Creating a Dependent Drop Down List In Google Sheets

Sumit

You May Also Like the Following Tutorials:

Popular posts:

27 thoughts on How to Create a Dependent Drop Down List in Google Sheets

You may also like

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội