Create Multiple Dependent Drop Down Lists in Excel

Create multiple dependent drop down lists in Excel. Each lists options are dependent upon the previous list selection.
Learn loads more Excel skills - bit.ly/Ultimat...
It is vital for your Excel reports that data is accurate. Assist users when entering data by providing drop down lists of the possible options. If you have large lists of options then break it down into several cascading drop down lists.
To create multiple dependent drop down lists in Excel you will first need to set up your lists as named ranges. The Data Validation tool and the INDIRECT function can then be used to reference the named ranges creating dependent lists.
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Join the Audience:
★ Twitter: / computergaga1
★ Facebook: / computergaga1
★ / 18737946
View more awesome FREE Excel tips
www.computerga...

[@johnt1330]

This is the ONLY video on youtube about this topic that made sense. You explained it and walked us through EVERY step. Thank you very much

[@Octrooi1]

4 years later, and still the best explanation! Sir I salute you, thanks for saving/helping!

[@Computergaga]

You're welcome Didier. Thank you for your comments.

[@sylviaislam]

*6 years later :3

[@willjones9396]

You are an absolute hero. I know this is an old video but I want you to know that this brilliant work is still being used today. Thanks for your help

[@Computergaga]

Much appreciated. Thank you Will.

[@shrutisuryanarayana5955]

I wanted to create a very detailed expense chart but didn't know how to create dependent lists. Thanks to you, i did it in just a few minutes. The way you explain stuff is very good, even someone with very less excel knowledge can do it easily.

[@Computergaga]

Brilliant to hear. Thank you Shruti.

[@martinbrady60]

Brilliant explanation. Everything you need to know how to do it simply. Thank you

[@Computergaga]

Thank you, Martin.

[@lfltube]

I'm so happy I have learned this amazing solution. For some years I've been struggling with work around works, never satisfactory. Thank you so much for this video. Channel subscribed!

[@Computergaga]

Thank you, Luis.

[@nycollassilva3558]

Ggyjetrfedrudy11vdftvctgt11cyggb urgência

[@nishthaaiyer2200]

Excellent......Never got a better working than what you had shown. Thanks for this

[@Computergaga]

Your welcome Nishthaa, thanks.

[@tgriffin91]

Thanks for this! Much easier to understand than other examples that I have seen around!

[@Computergaga]

Happy to help Tom. Thank you.

[@vincerivas1070]

Very helpful video! I was personally experiencing an issue with a dependent list I had created, in which I had a bunch of blank cells after each list. Your demonstration on use of the name ranges in the indirect formula helped to resolve this. Much appreciated!

[@Computergaga]

Thank you Vince.

[@ThomasParkes]

Wonderful video, you saved my life, and you explain it much easier than others.

[@leebakerman282]

You're a life saver! I have tried so many other ways (interesting logically, but total wastes of time)... and this was so simple! Thanks.

[@ridelrio8428]

Very helpful! I am taking a complex course on financials and this will help me break down my reference guides!!!

[@Computergaga]

Great to hear Ri.

[@sabenaedwards2143]

Thank you soooo much!!! I have now been able to make my bra sizing calculator the way that I want it, I have learned more valuable knowledge about excel functions, and completed all of this before having to cancel my free trial of Microsoft Office. Total Score!

[@christaylor7465]

THANK YOU for this video! I was able to create my first MDDL by watching this. VERY informative , nice, easy & calm walk-through. 👍👍

[@Computergaga]

Excellent! My pleasure, Chris.

[@davemonaghan3883]

Actually understandable presentation very well explained and not assuming viewer has extensive experience with subject matter, like most other presentations on this subject. Thanks for help.

[@Computergaga]

You're welcome Dave. Thank you very much.

[@lliinnkkxx]

Best tutorial regarding the subject so far...Thanks a Lot!

[@Computergaga]

Thank you very much iLx.

[@arnisdaddy7905]

Beautiful video! Very concise and well explained. You solved a problem I previously thought might be unsolvable. I will probably owe you a new car after I get my spreadsheet up and running, since it will be used for making me a lot of money. Thank you so much!

[@curtisrace6169]

Thank you. This was completely explained and I was able to save hours with my badge order form creation! Shannon

[@Computergaga]

Great to hear, thanks Curtis.

[@GerardoNuno]

Very easy and simple explanation! Thanks so much Mr. ComputerGaga

[@Computergaga]

You're very welcome Gerardo.

[@Yankee-Jr]

So explicit! Now I feel great knowing how to do this that I've so desired to learn for a while now. Thank you so much!

[@Computergaga]

You're welcome Viktor.

[@sandymenace5278]

Well done, really clear and explained in a much clearer way than other videos I have seen - life saver!

[@Computergaga]

Thank you very much, Sandy.

[@AdChaFF]

Great Video Sir! I have been struggling to get my list to work correctly and the "$" tip is probably the key! Learned 2 new things. Thanks for this video.

[@Computergaga]

You're welcome Mark. Thanks!

[@betsadi5891]

THANK YOU! I just couldn't find how to make this work on multiple lines and it was just that second $ sign!

[@Computergaga]

Glad it helped! You're welcome.

[@howardleslie1430]

Good, easy to follow tutorial! Thanks. Can you tell me how to achieve this (three dependent listboxes) within a Userform?

[@dawncyrikcaparas7575]

OMG! I have struggled with this for quite some time! I've been working on my spreadsheet for 3 hours! you are amazing!

[@Computergaga]

Thank you very much Dawncyrik. You are most welcome.

[@Sixpot48]

Excellent explanation. This really helped me today - I was going stir crazy trying to make this work today because I wasn't removing the dollar sign. Many thanks!

[@Computergaga]

Your welcome Welsh Woodsman, thanks.

[@syd3692]

Bro, thank you, I've been looking for something like this for hours.

[@Computergaga]

Excellent. You're welcome David.

[@ahmedshakebzubairi25]

Another Excellent video. Very well explained.

[@Computergaga]

Thank you, Ahmed.

[@AbanaBaKristo]

Best 10 min 39 Sec of the day

[@Fenrir23]

This helped me so much, I've been stuck trying to look up a table from a parsed list from a drop down.

[@Computergaga]

Excellent!

[@dennydaniel84]

Thank you! This is exactly what I need for a template I'm working on at my new job!

[@Computergaga]

Great!

[@Tanushree00099]

Superb .Have seen many Videos on multiple dependent drop down but they were sooooo complicated & confusing .Your explanation & presentation has made it crystal clear.Thank you so much for the video.Cant thank you enough.:)

[@Computergaga]

Thank you Tanushree.

[@amitnandan1]

Excellent! Exactly what I was looking for. Thanks

[@Computergaga]

You're welcome Amitnandan1.

[@davidjmoyesphotography1234]

Excellent demo and really useful. Thanks for sharing.

[@samanhaturusinghe9127]

This video helped me solve a part of a program that kept bugging me. Thank You!

[@rickbailey6084]

Super clear presentation, thank you!

[@Computergaga]

You're welcome Rick.

[@craigfrides9133]

That was brilliant mate. I've seen some very complicated ways of achieving this. This was a nice stepped out process.

[@Computergaga]

Thank you, Craig.

[@antonstrydom7108]

Thank you so much this was a very good lecture exactly what i was hoping to find

[@Computergaga]

Thanks Anton.

[@bobcaine9277]

WOW! I would have never known that was possible. Thank You

[@Computergaga]

You're welcome Bob.

[@pavelpanfil2196]

Thank you very much. It was very helpful. Have a great day!

[@Computergaga]

Thank you Pavel.

[@nithyachandran1406]

superb.. It was very very helpful. Thank you

[@Computergaga]

You're welcome Nithya.

[@kyawsoe8340]

I really love the way you explained. Great teacher. Thank you.

[@Computergaga]

Thank you very much Kyaw.

[@zarcode5677]

Excellent video indeed, what will happen if you want to changer the value in the first drop down list ? The second and third selection will remain.

[@Computergaga]

Thank you. Yes, it is not a formula so will not automatically update on value change. But the options available will be dependent on the changed value from that cell.

[@stevegroot8148]

Thank you. Very easy to understand.

[@Computergaga]

You're welcome Steve, thank you.

[@sircorn4248]

Another pearl that does to my Excel knowledge collection. Thanks

[@Computergaga]

You're very welcome Sir Corn.

[@ignasiusbagussatriosuhardi235]

Love this! Can't describe how it helps! Super thank you :D

[@Computergaga]

You're welcome Ignasius.

[@jasonjacquet7643]

Thank you excellent video and instruction. It worked perfectly. May I ask how do you use as a data base, specifically allow a user to access the spreadsheet, use drop downs, then save their selection into a data base that collects each entry?

[@leenasamman]

Thank you! This was very helpful

[@Computergaga]

You're very welcome Leena. Thank you.

[@MrDaveroberts100]

Super presentation and explanation. Thanks for the help.

[@Computergaga]

Your welcome Robert

[@thearchdeacon]

Thanks. A solution I found that works is to convert the spaces to underscore, then use a substitute formula on completion to convert back to a space.

[@Computergaga]

Mr Mike M Nice!!

[@KimShope]

+Mr Mike M Can you please explain this in a bit more detail? Or suggest a tutorial to help me? Thank you!

[@thearchdeacon]

+Kim Shope I didn't find a tutorial, however the basic premise is that you set up your INDIRECT formulas as usual, and use _ instead of a space. Once I had the dropdown with the format, e.g. Example_Format, I used SUBSTITUTE(H12,"_"," ") to convert the _ back to a space. I did the substitute in another column, as I don't know if you can nest it in the cell with the dropdown. I hope that helps

[@grandadcrypto]

GREAT JOB GREAT JOB. I CAN USE THIS RIGHT NOW.

[@Jadzia170]

Very easy to follow along. Thanks

[@Computergaga]

You're very welcome Leigh.

[@tilankajayamanne427]

Hi, Thanks for this. The only issue I have is after choosing the conditional values and selecting to display in the Lists after concluding the logic building in your video, even if I delete the initial content in List, the other content remains unchanged. For example, When List 1 is Films, List 2 is Animation, List 3 is Cars - even if I change Films to Books, List Animation and List 3 Cars remain unchanged. Help?

[@roberthatcher2773]

Excellent tutorial, thanks!

[@Computergaga]

You're very welcome, Robert. Thank you.

[@tashindonmoodley908]

Enjoyed your style and method,

[@Computergaga]

Thank you, Tashin.

[@yussifneto8849]

Excellent video! Extremely helpful! Thanks a lot!

[@BrandonofRedemption]

This is exactly what I needed. Appreciate the info!

[@Computergaga]

You're welcome B_Ontic

[@vikashsharma7350]

OMG! This was great! Thank you so much for this video. Just 1 question, I am not sure if its possible in excel. Can once cell have multiple selection? Meaning to say i can select multiple categories like Book & Films and then further select multiple categories from their sub levels. So i need multiple data in the same field which can be separated by a comma.

[@EuReGme]

this was a very helpful video. thank you so very very much.

[@Computergaga]

You are very welcome.

[@stevenh3992]

Love the videos! You have taken me from complete hopeless novice to somewhere in the intermediate bracket, really helpful!!Do you have an instruction on how to turn this dependent drop downs sheet into a form with dependent drop downs?

[@Computergaga]

Thank you Steven. Great to hear. Putting it into a form would just be a case of layout (depending what else you may want in it too). This sheet shows the two columns/cells side-by-side but they can be wherever you want on the sheet. It could easily be structured to look like a form.

[@davidmorello9229]

Thanks. Very clear. Is there a way to clear out the 2nd list when you change the value of the first list? When I change the value of the first list I can see the new list in the drop down but the previously selected value is still in the cell.

[@Computergaga]

For it to automatically change when the cell is changed, you would need VBA. You can use an IF function in the dependent list criteria. So for example, we could clear the second list, if the first list is changed to blank. But if the first list is changed to another value. I cannot see how we could automatically detect this without VBA.

[@ewingsibbaluca]

thumbs up... one question... how to automatic clear the list of list 2 and list 3 if i changed the list of list 1? thank you.

[@Computergaga]

This would require a macro on the worksheet change event. So when content is cleared from list one it automatically clears others.

[@evac6365]

Computergaga, I folled your instruction. The underscore works before numbers, but did not let me use apostrophe, to do length or height (5'x10' or 6'6"). What am I doing wrong? Thanks again for all your help. All of your videos are excellent !

[@Computergaga]

Thanks Eva. Ok, the apostrophe is an invalid character for range names. The range name may have to be set up as _5X10 for example. Then in the Data Validation list (assuming the apostrophe is in the cell data) this needs concatenating in also. This becomes more difficult now because of the variation of when and where the apostrophe is stored. I'm afraid the complexity probably goes beyond these comments.

[@RasikBihariTiwari]

Thank you. very nice explanation. I was looking something exactly like this. But it seems excel doesn't allow to give name of the range to begin with a number. That's a limitation for me :(. How can I accomplish it if I've some items in my first drop down list which start with a number?

[@Computergaga]

You are correct about this limitation. The only way around it is to create some clever workaround. I have a video here - kzbin.info/www/bejne/l5rHgIqFqLKXepo This video will solve the problem about not having spaces in a named range. We will need a similar approach which depends on how your data is structured. But maybe use a word, instead of number i.e. one instead of 1. Then do a formula like in the video with SUBSTITUTE but replacing 1 with one to change the cell value to named range for matching. You may need a lookup if you have lots of numbers. I hope this makes some sense.

[@GajendraSingh-kj8fs]

Thanks a TON... U really saved much of my time...

[@davidleckie7469]

Excellent presentation, was exactly what I was looking for. Thank you so much.Question: Can the list be set up so as you type the characters the words associated will display? Example if under Category there was also the word Food. When you typed F both Film and Food would appear for a selection. Books and Games would not appear.

[@Computergaga]

Thanks David. I have created this effect in this video - kzbin.info/www/bejne/qaO3pZiPpNaWgNU This shows the list appearing as you type like you ask. This example shows a single list created with a combo box and not a dependent data validation list as in this video though. They are different.

[@GrimacesGameNuggets]

Excellent guide. Thanks mate!

[@Computergaga]

My pleasure.

[@siamexport2743]

Ok thank you, are there another way, for open dependent dynamic menu? (drop down list are not open) or can drop down list be open?? Thanks

[@vadardosky]

Thank you for this amazing tutorial!

[@Computergaga]

Your welcome Vadar.

[@thuytrinhnguyen8497]

Thank you very much! You saved my life today at work! P/S: I really enjoyed English accent : )

[@sanjaymagar1312]

Excellent video! Dear sir, i would like to put forward a problem that I have at present. would you mind helping me sir? Actually, I want to create two drop down lists in two different sheets, and when i change the value from drop down list (say from sheet 1) the value in sheet 2 should change automatically to match value the first drop lists and vice versa. Note : I have the same lists in both of the drop down lists)

[@lucyedge2298]

Great how to video- very helpful. Thank you :-)

[@Computergaga]

You're welcome.

[@pn060693]

YOU ARE THE MAN!!! THANK YOU SO MUCH!!

[@Computergaga]

Thank you, you're welcome.

[@올겐매니아-g7c]

Thanks for the explanation.

[@Computergaga]

You're welcome.

[@tejaspunjani2447]

Great efforts and well explained.. Keep it up.!

[@Computergaga]

Thank you Tejas.

[@selmainan795]

Thanks for help. It was really helpful.. Super explanation...

[@Computergaga]

Thank you Selma.

[@darshanabalasubramaniyam7641]

Excellent Tip

[@prashantmore7141]

Thank you so much. Very well explained 👍

[@Computergaga]

Thank you Prashant.

[@tonyhall217]

Thank you very much for this - very helpful video.I am after doing this, but in the second list, rather than a drop down menu, I wanted a new list to populate elsewhere. Is this possible?

[@Computergaga]

Yes this is possible. Probably using the Advanced Filter. Might be worth Googling that if you have not used it before. By selecting an option from the drop down list, it could filter a list elsewhere (creating the effect of a list generation). Other options could be a macro, or some lookup functions. I think Advanced Filter would be simplest.

[@brintmontgomery8323]

Very helpful. English accent makes it sound highbrow too!

[@Computergaga]

Thanks Brint

[@vishalshirke6653]

Hi.. Computergaga. Very Helpful. Thanks for sharing. However, i'm in unable to link the 3 list with 2 list. Please advise.

[@Computergaga]

Hard to say without seeing what is going on. I would check the ranges names and the references used in the data validation.

[@shrisabale]

Good presentation ..thanks

[@Computergaga]

Thanks Shrikant.

[@igotkavorka]

Is there a way to have multiple, different drop-down lists (in multiple cells) that are all dependent on the same 1st drop-down list?

[@evac6365]

Love your detailed video. Exactly what I need to make an order sheet for custom build trailers. My only problem is that I need the NAMES to be number. (Sizes and heights of the trailers, like 5'x10'). How can I make this happen? Please help! This would make our life so much easier. :). Thank you.

[@Computergaga]

You can use numbers within range names, but you cannot start with them. There are also specific characters which are not allowed. Therefore the range name may have to be created as _5'x10' for example. Assuming the cells in the list would display the size as 5'x10'. The INDIRECT function in Data Validation would then need the _ concatenated in such as INDIRECT("_"&A2)

[@evac6365]

Thank you so much for your help.

[@Computergaga]

Your welcome Eva

[@tejashreedeokar1309]

Thank you!

[@Computergaga]

You're very welcome Tejashree.

[@Blackend65]

Awesome video. Thank you Alan!

[@thearchdeacon]

Useful video. It almost works for me except that some the list headers are 2 words, and the indirect formula doesn't like spaces or non alpha characters. Any suggestions on an alternative solution? Thanks

[@anthonyyio4481]

Mr Mike M Any update on this? I was stuck with the space and non alpha character too.

[@Computergaga]

anthony yio +Mr Mike M You cannot use spaces in a named range so may wish to use the underscore instead. However you can include spaces in INDIRECT if necessary and if "" are used to enclose it.

[@suriakalakarunamoorty3072]

Thanks!! I managed to complete the task given by boss with the help of this tutorial. The only challenge for me was how to group & name the list if the there is an addition item. took me few more mins but managed- I just renamed the group (eg- Book , became books with latest list). I am not sure if there is other option. Do let us know if any, Thanks =)

[@Computergaga]

You're welcome, Suriakala. You can rename a group by clicking Formulas > Name Manager. You can also edit the source of the group here, if there are additional entries.

[@suriakalakarunamoorty3072]

@@Computergaga ohw great. Thank you 💕

[@Simon6776]

Very Clear....thanks

[@Bromoteknada]

Great and helpful video!I want to create a data sheet (which will be updated constantly) and which is going to return a descending list based on the choices made in the dependent drop down list.Is it possible?For example let's say I have a table with employee's names,their division,their responsibilities and their ages.Can I make a series of choices that will give me the top 3 ,for example,based on these choices?Which of your videos adress that need?Thank you in advance! PS : I think this video is solving many of my problems but it seems that I have to combine this knowledge with something else in order to get that "top 3" names.

[@Computergaga]

+Bromoteknada The dependent lists can be set up in the same way as this video. You will need to set the list up to show top 3 though. PivotTables have a feature for this. Or you can use the Large function. I don't have a video demonstrating this.

[@johngordonchinonye5186]

Thanks. it helped alot i have a question? i noticed spaced words are not allowed in this drop down list. please is there a way i can include words with space e.g weight variance

[@Computergaga]

No spaces in named ranges so you would need to set this up without e.g. WeightVariance. On the spreadsheet you can write it with though. Then for the formula you could use INDIRECT(SUBSTITUTE(A2," ",""))

[@Corte9]

Very cool. Is it possible to add a description that is automatically inserted into the sheet when I've made my choices in the drop down lists? Let's say I've chosen Film->Animation-> Cars and wish to get a summary of the film to automatically appear in the spreadsheet. Is that possible?

[@Computergaga]

+Corte9 If you have the description in a column by the film titles then absolutely. You could use a VLOOKUP to look for the film title and return the description.

[@entertainmela]

Ur explanation is awesome sir... But I can't understand ur language ... But I use to see no.of times... Then I will understand ... Thnq sir

[@Computergaga]

You're welcome Padma. I hope that you begin to understand me better.

[@davidbentley1324]

worked a treat Sir! Thanks

[@bpkingcobra]

This was very helpful Thank you. I'm creating a similar list but I have a question...using your example, you have Books under Category...what if you also had Books in another column, Would it cause problems when naming your ranges? Also, how would naming ranges work if for example instead of Games you had Computer Games under Category? Do you have to use underscores to separate the words?

[@Computergaga]

The named ranges would need to be unique, so you may need to be clever with the names. With the space issue, yes an underscore can be used to separate words. But you don't need the underscore in the list item, just the named range. You can replace it with the SUBSTITUTE function shown in this video to solve this problem - kzbin.info/www/bejne/l5rHgIqFqLKXepo

[@bpkingcobra]

@@Computergaga Thank you!

[@mrashad1988]

Thank you dear. but there is a problem when I modify an entered data in a main cell, the sub cell remain e.g. if the written data was (Books-Romance-elephant moon) and I modified books to films, Romance-elephant moon would remained. Any ideas for this problem? Thank You

[@bogdanudovicic391]

Very useful, thank you!

[@bige8549]

Very very good. Thank you! My experience is that the named range cannot contain weird characters or spaces. Maybe you can mention this in your video.

[@Computergaga]

+Eric Santaiti Yes this is true. No spaces, characters such as % or to begin the name with a number.

[@Dragonfire1x]

+Computergaga Hello, thanks for the video. I also couldn't figure how to use this on lists with more than one word. Can I simply link it to the list? Thanks in advance.

[@vanderleynhantumbo1413]

Thanks it worked perfectly

[@Computergaga]

Great to hear this Vanderley.

[@E.s3_old]

Very Good, Thank YOU