How To Create MULTIPLE Dependent Drop-Down Lists in Google Sheets

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-courses
Looking to create advanced drop-down lists in Google Sheets? Dive into this tutorial to learn how to set up multiple dependent drop-down lists efficiently, perfect for intricate data entry tasks.
⬇️ Download a copy of my workbook here: pages.xelplus.com/sheets-multiple-dependent-file
✨ Key Highlights:
▪️ Setting Up the Master Data: Understand how to organize your master data, which forms the basis of your dependent drop-down lists.
▪️ Creating the First Drop-Down List: Learn the steps to create your primary drop-down list, which will determine the content of subsequent dependent lists.
▪️ Using Data Validation for Dependent Lists: Discover how to use Google Sheets' data validation feature to create dependent lists that change based on the selection in the first list.
▪️ Data Preparation for Multiple Lists: Explore how to use the INDEX and MATCH functions to prepare data for multiple dependent lists.
▪️ Transposing Data for Horizontal Display: Find out how to use the TRANSPOSE function to display your drop-down lists horizontally for a cleaner look.
▪️ Dealing with Dynamic Ranges: Get tips on adjusting your data validation ranges for each row to ensure each drop-down list functions correctly.
00:00 How To Create Multiple Dependent Drop Down Lists in Google Sheets
01:25 Create First Drop-Down List in Google Sheets
02:58 Create Dependent Drop-Down List in Google Sheets
04:46 Create Multiple Dependent Drop-Downs in Google Sheets
LINK to Google Sheets tutorials: kzbin.info/aero/PLmHVyfmcRKyy0mPWTS5JL99PySzLPiJYu
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: kzbin.info/door/JtUOos_MwJa_Ewii-R3cJAjoin
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: lgharani
LinkedIn: www.linkedin.com/company/xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#googlesheets

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/sheets-multiple-dependent-file GOOD NEWS! The invisible dollar sign issue is fixed! We can see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference and just remove the dollar signs from the first dependent validation, and then copy the data validation down (Works like Excel now 😉)

[@brookemahnken2480]

Hi Leila, great tutorial! However I tried copying the data validation down and it did not work; it just kept referencing the same row I set as the source for my original data validation. Any ideas? This spreadsheet lives in a google suite account, not sure if that makes a difference or not

[@MECKENICALROBOT]

love the tutorial!, and I'm happy I stumbled on this. I've been milling over this issue for a while now, and I also had the issue where it wouldn't copy relatively, down that dependent's column. But, _that said_ *I found that the solution was to set your **_criteria_** value, then set the **_cell range_** by adding **_...:B(n)_** , with n being the last row of that dependent's column. this makes them all relative, however when you reopen **_data validation_** , the **_cell range_** resets, and is updated for each row down the column.* I did end up using a _transpose_ on an _indirect_ function, and some limited hidden columns (namedtables) between each of a dependent column. As I write this out, Im thinking about just moving them to a separate sheet... _mmmm_

[@palehswanchitrakar]

Also does not work for me!

[@hughandcry]

Darn, I should have read the comments sooner :P. But thank you for this, I have been lookign for this for awhile!! I set the data validation for the first cell. Then edited the data validation, removing the $ next to the row number, then did the copy and paste special and it worked exactly as expected.

[@joycengailing2977]

Hi Leila, if I have 100 over rows is there any quick solution to it instead of updating each row like at 6:50 on wards?

[@biljanapesevska]

It took me a total of 3 hours to actually create a dependant dropdown, and only 10 minutes after stumbling upon your video! Thanks! I wish I found your video first!

[@lovelyraincoat475]

This is brilliant, I'm just starting to learn and there's no way I could have put all those formulas together. Thank you so much!!

[@maeobuyes299]

From all the videos I've browsed yours was the most honest especially on the limitations. Other videos dont talk about it and how to deal with it. Your video was easy to follow and provided the solution to my problem. Thank you.

[@tynerds37]

A huge thank you for these guides! They are really easy to follow!

[@taniknight]

Awesome tutorials! I was SO glad to discover that dragging these vlaidations down now works. I have a big data set to work on (auto categorisation of bank transactions) and this has made a drop down list style of data entry possible.

[@joshedgerly]

You are an absolute life saver - I cannot thank you enough for this!

[@sc0tt_p]

Almost 4 years later, and while they did fix the default hidden absolute references, this is still the only working way I have found to do this.

[@averie6169]

I found an easier way to update all the formulas, instead of manually typing each number into the data validation. Basically, if you save the data validation and then go back, the $'s pop up and you can delete them. This worked for me, not sure if it will work for others. Instructions below. (Edit: Clearer instructions) At 5:42, she talks about invisible dollar signs stopping us from copying the data validation formula all the way down on the "Dep. List" sheet for row B. I followed her instructions from 6:00 to 6:15, and then I copied ONE cell only (unlike how she does all of them at 6:35) and pasted it into cell B4. I reopened the Data Validation for B4 and I could see all the $ signs. I manually deleted each one, and saved. NOTE: Make sure that you click on the text itself when erasing the $s-- do not click on the little box that looks like four squares that says "Select Data Range." If you click on that, it makes a new pop-up window, and it puts the $s back every time. Very frustrating if you keep deleting them. Then I copied the B4 formula all the way down, and it automatically changed all the numbers. I checked each cell; they all worked right. It was much faster than what she did from 6:44 to 6:51. I'm guessing that the "invisible dollar signs" are actually just auto-filled behind your back -- you see them if you reopen the formulas. Hope this helped someone!

[@LeilaGharani]

I just checked. The invisible dollar sign issue is fixed! We can see the $ signs. YAY! This makes it all so much easier.

[@roxanam4630]

Thank you a lot, to both of you! The video and this comment helped me solve my problem and create dependable dropdowns for a 3000rows table! At one point I was almost ready to start learning how to write a script, because I really wanted to have this feature. You've made it so much easier for me!

[@chrism9037]

Wow, extremely useful, thanks Leila!

[@mreighties2860]

Hi Leila, Google now lets you create a drop-down using "Relative cell referencing" like you created using the range "Master!F4:Z4 and now you can drag the drop-down down and the range will change for each row without having to "update" each data validation. When you create the drop-downs for the "Categories" in column A then make sure the first one uses "Absolute cell referencing" so that when you copy or drag the drop-down down into the cells below the "cell referencing" will be locked and not change. But when you create the "dependent drop-downs" in column B then create the first drop-down and then look back at it and make sure the "cell referencing" is set to "relative" by removing the $ signs. Then copy or drag down that drop-down to the cells below. The drop-downs will change without having to update each one. James :)

[@hudaabdullaziz1877]

great , it works for me thank you

[@dillonmears6696]

Do you know if this still works? I am trying to create a budget sheet with dependent drop-down categories and the $ are not visible. Wanted to see if this was true for other people 2 years since your comment. Thanks!!!

[@mreighties2860]

@@dillonmears6696 See my replay below and select the cell that has your dropdown and click "Data Validation" and then remove the $ signs from the rule and then drag the dropdown down and each range for that dropdown will change because of its "Relative" reference. You can click any of the dropdowns and then view its rule to see that the range has change for that particular row.

[@TylerStanczak]

This is awesome! Thanks for sharing this tutorial on drop-down lists :)

[@LeilaGharani]

My pleasure :)

[@Doriane55]

Amazing video!! Thank you for publishing it. It was the solution I needed for my management sheet

[@HikingPisgah]

Great video. Helped me get through a project on google sheets. Thank you so much.

[@calmontoya]

Thanks a lot. Great tutorial! 100% effective

[@ranjansingh3220]

Hi Leila, You can remove the $ sign from validation and copy-paste, it will change the range as expected, which can be used for huge data set.

[@LeilaGharani]

Yes, apparently this changed since I recorded the video. Thanks for the feedback!

[@koktepfertilizers]

Thank you. I'd appreciate your comments.

[@hectoralicea3079]

..thank you Leila for those helpful tips on Multiple Dependent Drop-down list. Happy holidays.

[@LeilaGharani]

Thank you Hector. Happy Holidays to you too!

[@thomasrichmond1259]

this was super super helpful for me! Love your videos

[@luks1207]

If you go into the second data validation, you can actually remove the $ and simply copy and paste the validation. Thanks for the tutorial!

[@vidulaworlikar9784]

Hi, I have a suggestion. After copy-pasting the formula for data validation, instead of manually changing the cell row number in the last step - you may just open the data validation formula from any one of the pasted formulas (say 5th row). Remove the $ sign next to the cell number (keep column frozen) - for example: Sheet1!$K5:$N5 (originally it was Sheet1!$K$5:$N$5). Now you will get a popup asking if you want to make the same change to all the cells - and viola - it changes to respective row numbers. No need to manually change each and every formula.

[@westernugmedia.1305]

Leila, I like the way u speak out your English .. It's, favourable for us all. Even here in Africa

[@gennarobasso144]

Thank you again...really appreciate your videos

[@MsEteria]

This is lifesaver!! I watched 10 other videos and I was going crazy. And you made it super easy. Thank you, thank you, thank you

[@LeilaGharani]

Glad it helped, Nika!

[@BURNINGREDOX]

Thank you so much! Im used to excel so needed some help to get this task done, it worked perfectly =)

[@allthatyouneed6498]

Very very Helpful Thank You!

[@afuzzyrush]

you saved my life so much thanks!

[@sarathnag8626]

Thank you very much, It was very helpful for me....

[@SimmingWithSkeeter]

So helpful! Thank you

[@alexpazaitis]

This saved my day! Thanks a lot :)

[@bobbyman410]

You honestly deserve more than 1 million subscribers.. Let's all make that happen

[@LeilaGharani]

Wow, thank you!

[@h.n.yousaf]

I think number shouldn't matter for her. It mean it shouldn't affect her if 1M+ don't want to learn these tricks. She is changing lives, she is putting a huge positive impact on the careers of the people who are interested. I learned and progressed a lot by watching her videos. Thank you very much madam

[@locpham6660]

OMG, thank you very much, you did save me today!!!!

[@heiditung6203]

Hello! Your previous video on dependent dropdown was very helpful! But how would I formulate the multiple dependent dropdown list if the master database is in a tabular format? Tried using the method you detailed here but I keep getting #N/A return. Thanks!

[@TsiriniainaRakotonirina]

Thanks @Leila! Is there a way to have multiple columns into the Drop down List please? Example: [Col #1] Employee Code | [Col #2] Employee Full Name

[@AzhaguChannel]

Good Leila, i was searching this function only useful

[@joanmayes3604]

Love your videos. You explain things very clearly with good examples. Can a 3rd column be created and linked to the 2nd dynamic dropdown, creating a 3rd choice based on the second choice?

[@lovelyraincoat475]

Not sure if you still need to know but yes, using the same instructions you can make another conditional dropdown in addition!

[@carorestrepo16]

Thanks Leila, you are awesome!

[@kimberlyelliott8314]

I really like your channel and love the way you explain things. I'm great at following directions, but not so great at figuring out formulas to begin with. I have a request. Maybe you've already done a video about it and I missed it? So, here's what I'd like to be able to do: a drop down menu to select a lesson objective. Once the lesson objective is selected, the cells beneath that objective will populate with the entire lesson plan. I can visualize the set up, I think, but don't know the right formulas to use.

[@tedsterrett7204]

As great as your skills are, your personality is just as great... what a combination! thank you Miss. Leila G.

[@LeilaGharani]

Thank you so much, Ted!

[@manishnarayanshrivastav4939]

Very Helpful... Really appreciated 🎉😍😘

[@borisgrushin868]

Curious if you can use a formula with indirect in the data validation in the drop-down tab to avoid the manual work, something like indirect("master!f"&row()+4&"z"&row()+4)

[@wooahaeceo1122]

this solves my current problem, thank you so much!!!

[@LeilaGharani]

Glad it helped!

[@akin-ojoayotomide8909]

Thank you so much!

[@jamesblock8384]

lol I just commented on your other video asking about this video. Good stuff.

[@baskug]

Your tutorials are great... Im just a beginner in learning using formulas. I have some problem with one of my excel item checksheet i hope i could find a formula that could solve it.

[@sllawyer]

A channel I suggest often as an example to be a best youtuber.

[@LeilaGharani]

Wow, thanks

[@prettypinklips91]

Omg THANK YOU!!!! I have been stuck on this for hours! Thought I was going to have to do the indirect function for every single row hahaha.

[@LeilaGharani]

Glad it helped!

[@RahulJauhari]

Very useful video thanks

[@allahanvijay2601]

Amazing video wow so helpful :)

[@sandeeprajvanshy5965]

Its a fantastic Solution !

[@massimoquintano7692]

Great, Thank you! I used your tip for more than 1000 rows :) Lovely!!!

[@jennyelomaa1685]

Hey, how were you able to do it for so many rows? When I try to do that, it only shows me the options for the very first dependent. Hope that makes sense!

[@massimoquintano7692]

@@jennyelomaa1685 you need to use =TRANSPOSE and create a lot of rows, so you will be able to have multiple drop downs ;)

[@mohammadashiq9996]

Like it and its very simple thank you Leila

[@LeilaGharani]

Thank you! 😊

[@naraecho6149]

Thanks the video! 6:42 Should I change every B5:B14 for myself, not automatically?

[@steveblake6110]

Cool Leila. Nice and clear.

[@LeilaGharani]

Thank you! Cheers!

[@OzduSoleilDATA]

GOOD ONE! I'm about to a dropdown lists tutorial. We're thinking alike. 🤗💡

[@LeilaGharani]

Thanks Oz! Looking forward to seeing your take on it.

[@Chander-gy1xz]

@Leila the invisible $ problem in Google sheet can be solved by putting the $ sign for the Data Validation you have done for the first row and then remove the $ and save the Data Validation. Now if you drag down the Data Validation it will automatically extend the selection. I have tried it out and it works

[@neilbisschoff]

Hi, great video, I've done something similar using the filter command. I do want to ask though, is there a way to get the drop-down list items from A-Z?

[@nimtina]

You are my angel!! Thank you so much!!!This is awesome!

[@LeilaGharani]

You are so welcome.

[@ZacJelke]

I'm curious, is there a way to cause the second dropdown list to default to an option within the parameters of the first? In your example in A3 you have Game selected and, in B3, Five Labs; the issue I'm having is that, using your example, if I were to switch that A3 cell to Productivity it would show an error because Five Labs would still be the input in B3. Is there someway to cause the second dropdown list to automatically default to the first available valid option of the parameter set in the first drop down list, this being WenCal in your example. Or will you always have to manually go in and change something?

[@user-nl1zv3mp6h]

Awesome video, and with the fix it's even better... but my issue is I'd like to be able to sort by a column with a date, and if I sort the range it gets messed up because of the hidden tables. Any suggestions?

[@musaabdulrahman5887]

thanks so much Ma

[@tinkomertens7936]

two years after the creation of this its still helpful. and i'm glad i spotted the comment about the dollar sign issue being fixed.i have 1000 cells o_o

[@khowajaghulamsakhisediqy1563]

Hi, It was very useful, I have a problem as below; An agent is filling a form under name of a customer few days ago. So, I need to excel show the report, if employee X process form Y on date Z, system show entire ROW report, else a blank row.

[@h.n.yousaf]

I learned and progressed a lot by watching your videos. Thank you very much madam

[@LeilaGharani]

It's my pleasure.

[@rajganguly9802]

This is great..I used unique + filter instead of index..

[@woodbybill1483]

thank you for your video can you explain why you use the offset function instead of indirect?

[@angghiardian4476]

its really helpful at the start, but how if the row is so many? do you have any solution on this?

[@marvindentzien1555]

Great Video. How would you do this if your data is in tabular format?

[@jessicaayala8127]

Hi Leila! This video was so helpful! Quick question for you (or the group) Since I am using the chip drop down style and love to color code, do you know if there is a way to automatically have my dependent drop down follow the same colors as my first list? For example, if the Productivity division was red, then I would want all the productivity apps to also come up as red in my second dropdown. And then if I switched to the Utility division (which is blue), I'd want all those apps to show up as blue. Right now even if I manually change the colors of the app drop down once I've picked a division, they revert back to gray if I toggle the first drop down and the color clears out. I'm trying to avoid manually setting the color after if settled on the first drop down category. Not sure if its possible or if I made sense but hopefully someone out there has a solution!

[@samiurrehman3654]

Thank you for sharing such a useful trick. 👍 Ma'am, I have a query, 👉 How to change/move specific cell value on the base of drop-down list.

[@sumeetkundnani8513]

Hey Leila, Great tutorials!! I have become your fan. Your tutorials are on point and very helpful. I used the dependent dropdown list for our business, which is very helpful. Thanks a lot!! However, when new rows are created, or any of them are deleted, it creates chaos. 1) When any of the rows are deleted, we lose the cell reference from the data prep. (I was able to solve this problem using arrayformula) 2) When a new row is added, the data validation reference for that new row is the same as the following. Hence, if I select category A from the first dropdown for the new row, I have two rows showing the sub-categories relevant to category A (the new row and the following one). DO WE HAVE ANY SOLUTION TO THIS PROBLEM? IS THERE ANY WAY TO MAKE THE DATA VALIDATION REFERENCES DYNAMIC?

[@kajasutha]

Biggest Thanks Mam..... My long time issues now solved ur video watching after Lot of thanks

[@LeilaGharani]

Most welcome 😊

[@abdallah.kandiel]

Happy Thursday your highness These videos helped me so much This and last week Thanks

[@LeilaGharani]

Haha, it's Tuesday. I'm glad you enjoyed the videos 😊

[@abdallah.kandiel]

@@LeilaGharani 😂😂🤭😚😚

[@abdallah.kandiel]

@@LeilaGharani sorry I was enjoying lambda yesterday 🙏

[@philiphorton1661]

I found that if I created an =index formula in a separate sheet when it came to making the data validation, you could click on the cell - data validation - select from range - then highlight the whole row or column whatever you choose to use, this speeds up the process of having to change the location of each dropdown menu. if you first make the index formula, after you have dragged that down/across the row/column, then you insert a cell to the left and to the top for your headings and description, google sheets will automatically adjust your formula so your titles/description will not appear in your lists

[@farooqtahir538]

You are master in making simple things difficult.This thing is easliy can be done with name range and indirect function in google sheet

[@heckald]

What do I do if I want to add rows in the call out lists? The data prep doesnt automatically update and I get errors on some cells.

[@JohnDeir]

Actually you can put formulas in Google sheets validations. And the best trick is to make name range of cells listed below each category name. So under Productivity you create a name range called "Productivity" and so on. Then you can create a formula in data validation associating the category with the name range. Lots easier and try it out. Great tutotial, just trying to help.

[@JohnDeir]

To add, the function to use is the indirect function. Using a helper column instead of inside of the validation is much easier. It eliminates having to change cell references.

[@LeilaGharani]

Thanks for your input. Agree - name manager is an indirect way to keep formulas. The solution I originally tried with name manager though always worked for the last dependent drop-down. But I couldn't go back and revise a a previous drop-down. If you have a solution for this with name manager I'd love to know.

[@juandiaz9447]

how do i do that please

[@mfonobot5665]

Hi leila after weeks of watch serial KZbin video on how to go about this, am soo happy it finally worked.... thank u

[@LeilaGharani]

You are welcome 😊

[@alexand3rlarsson]

Thank you so much for this video. That´s exactly what I need often. There is another question. Is it possible to use multiple dependent dynamic drop-down list on one sheet if i have my source/input data on the other sheet? It would be nice to solv this problem because the way you explanin in your video doesn´t work.

[@RyanHaeseker]

Hi Leila, Could you explain how to do this if the Master Data categories (in your case; Productivity, Game, Utility) are vertical and not horizontal. My categories are set vertically in column 1 with the data (in your case; Games) in column 2.

[@user-hz9nc2mn3r]

Is there a good way to use this method if the drop down lists are part of a list of data with filters at the top? Sorting the list seems to break the conditional drop down.

[@ankitjha306]

For drag validation go to first row remove $ sign from value then copy it and paste through data validation only to the entire row.

[@abubakaruzairu5752]

Thanks my master this video helf me to understand how drop down work in another sheet .

[@LeilaGharani]

Glad to hear that!

[@norhayatisyahadabintiabdra2195]

hi.. tq for the tutorial. I just wonder, if I want to create multiple dependent dropdown list in multilple spreadsheets, with the same data/same source, can it be done?

[@Dani_K506]

I know it has been a while since this video was posted but this is the closest solution I've found to a problem I've been trying to solve for over a year, so I have to ask just in case someone answers - What if I need to have 3, or more, columns? For instance - Column A is a drop down populated from a data set Column B is a drop down populated based on the selection in Column A Column C is static display, or a drop down, based on the selections in both Column A and Column B In this example Column C can not strictly be determined based on Column Bs selection because the selection in Column A may alter the meaning of the selection in Column B, affecting the final output in Colum C I hope that explanation is clear enough for someone to be able to help me finally resolve this issue. Thank you for the tutorial!

[@michaelangelo9702]

what is the best way if want to do the same thing but over around 500 lines, do i just do it for every row or is there a better way?

[@dustinhodges9987]

Was just recommended here by you. Great video. Do you think it is possible to use this for something like a budget for entering in expenses? In this case, there would be an unknown amount of potential expenses each month that would also potentially reoccur every month. I’m thinking you could do this, but it would be highly inefficient as you would end up using so many extra sheets worth of date prep categories for every month. Is this accurate? I’ve found a work around for this, but this would be a nice aesthetic addition. Quality content as usual. Thank you for the work you put into these videos.

[@kharisharkahfi1851]

I have been trying to do this, but cannot find a simpler way, especially on the part of preparing master data for potential 100+ items of expenses. Let me know if someone find an answer for this!

[@ulisesserna6155]

Question I am looking a way to create chart and I need the chart to use the color in the table cells basic thee color I am using green, yellow and red. How can I get this to work?

[@MarceneiroFDS]

Very Well, thanks! please explaine is it possible to have the content of the drop-down list hosted outside of the worksheet? In practice: I would like to distribute a worksheet which will contain a drop-down list, but I would like to have the controle of the items of the drop-down in case of an update without needing to access all the distributes worksheets one by one. Is it possible? I don't know!!! 😅

[@PK-TECH48]

O thanks alot it's helped me

[@jamespanganiban4823]

To drag down all the data validation relatively to the following cell with cell number sequence, just remove the "$" then drag down. Ex: ='DATA VALID'!$G3:$Z3 just remove the "$". Working in Google sheet

[@claudiaarenas1259]

Hello. How can I create a dependant dropdown list for 500+ rows. I need to add more rows as time passes.. Also, if I use a filter in my master page, the dropdown list doesnt work properly anymore

[@finbran7439]

I love uuuu.. It solves my problem.. I can start my sleepung beauty now

[@b0n3zy]

ΤΗΑΝΚ you for this! I was lucky to read the comments as I need it for multiple rows. My only PROBLEM now is that adding a row ruins the whole sheet. Any solutions?

[@boqizhu4522]

Hi Leila, when I fellow your video until 4:36, there is an error that "Array result was not expanded because it would overwrite data in G3." Do you know any way to fix it?

[@vanik9432]

Hi Leila, I have 1500 rows! while applying the index formula & transpose how shall i do it for all rows now?

[@user-tg6ty2le8c]

Leila, I have a large database for Condition 2 and Condition 3. It's obvious that I won't be able to have that many columns to organise my data that would be ever expanding. Is there a solution for this?

[@rjgittuwala]

Thanks 👍☺️ Mam

[@LeilaGharani]

My pleasure :)

[@RadheRadhe-ln6sw]

But drawback is it also increases rows in prep set. Is there not way that we only have one row from prep which will update according to value selected from main dropdown?

[@zaymarhenriquez-sedra2720]

I would like this but on a table. To have different row with Productivity, Games, etc, Is it possible?

[@JustinColavita]

damn, watch to the end all excited only to realize i'm that one who needs this to work on thousands of rows

[@LeilaGharani]

No, fortunately Google fixed the issue with the invisible dollar sign in the meantime! We can now see the $ signs in data validation and we can remove them. So you can skip the part where I update each cell reference. Just remove the dollar signs from the first dependent validation, and then copy the data validation down. Done! 😊