Dynamic Dependent Dropdown Lists in Excel: create and manage all the categories you need

Dynamic Dependent Dropdown Lists in Excel: create and manage all the categories you need - T0032

Рет қаралды 21,950

Күн бұрын

This video shows how to create dependent Dropdown lists in Excel to easily add new categories and subcategories and have them immediately available in the dropdown lists.
IF YOU PREFER A READY-TO-USE SOLUTION, get your files here and skip the tutorial:
solveandexcel.ca/dynamic-depe...
Dropdown lists are a fundamental feature to ensure clean data input in Excel.
When a solution uses Data Validation in multiple columns, very often, there is a dependency between the values we want to enter in those columns. Dependency means that the list of valid values to enter in a column depends on the values we entered in previous columns.
Get the EXERCISE FILES here:
snapreportschamp.com/exercise...
___________
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: snapreportschamp.com/course
Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
solveandexcel.ca/dynamic-depe...
Subscribe to my NEWSLETTER for exciting news about the Excel world: bit.ly/learnfromcelia
0:00 - Introduction - Explaining Dependent Dropdown Lists
1:10 - Introduction - Explaining how Conditional Formatting is used to point invalid entries
1:42 - Introduction - Explaining the elements in the workbook supporting the dependent dropdown mechanism
2:31 - Demonstrating that the dynamic dropdown structure is dynamic
4:21 - Celia introduces herself, and the Excel Snap Reports training program
5:02 - Explaining Table of categories and subcategories, examples of applications
5:47 - Excel versions that support this solution
7:38 - Building data validation for the first column (categories)
9:58 - Building data validation for the second column (subcategories)
25:15 - Setting Data Validation for when the category and subcategory values are not a valid combination
27:41 - Possibility of replacing names with their formulas
------------------
Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
Solve & Excel Consulting - solveandexcel.ca
LinkedIn: / celiajordaoalves
Facebook: / solveandexcel
Twitter: @celia_excel
Instagram: @solveandexcel
KZfaq: https:www// / celiaalvessolveexcel
#excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation

Пікірлер: 110

@CeliaAlvesSolveExcel 3 ай бұрын

Since this video was published, this technique was simplified. Watch this session to learn a much easier way to implement up to 3 dependent dropdown lists. kzfaq.info/get/bejne/b7-qq7Cmzs7DZ3k.htmlsi=3EUWBU9l8wdAscIj

@zacharymbradleyjr 11 ай бұрын

I am 4 minutes and 24 seconds into your video and I had to pause to send you a HUGE thank you! I have watched so many videos regarding this subject and yours is the only one that is the most benificial. Granted, there are various ways to set up dependent drop down options, but yours is so easy and straight forward and the flexibility is amazing!!!!!

@CeliaAlvesSolveExcel 11 ай бұрын

There you go! Glad to help, Zacharym! Enjoy! :)

@AccessAnalytic Жыл бұрын

Nice hack with the # at the end of the forrnula to pick up the array!

@CeliaAlvesSolveExcel Жыл бұрын

Thanks, Wyn! I was kinda surprised that worked in a name. 😁

@zahoorsarbandi2982 Жыл бұрын

A very informative lecture on dependent dropdown list with a simple and easily understandable method of teaching. Thanks for the efforts.

@CeliaAlvesSolveExcel Жыл бұрын

Your very welcome, Zahoor. :)

@IbrahimNajjar-by2dk Жыл бұрын

Absolutely amazing! just solved an issue I have been struggling with for months. Thanks for the in depth explanation Celia!!!

@CeliaAlvesSolveExcel Жыл бұрын

Your very welcome, Ibrahim! Glad that it was helpful to you.

@KalaniR-dt6lt 3 ай бұрын

This is very useful and it worked. The way you teach step by step makes the all the difference compared to other videos. Thank you so much!

@CeliaAlvesSolveExcel 3 ай бұрын

I am glad that you made it work for you. Well done!

@EricHartwigConsulting Жыл бұрын

Magic! This is really awesome! Thank you for creating and posting this video!

@CeliaAlvesSolveExcel Жыл бұрын

Awesome to see you are a Champion too! Thank you for watching and your feedback. 💪🏆🔥

@iankr Жыл бұрын

Many thanks, Celia - this is ingenious and very well explained. PS: Your new hairstyle suits you!

@CeliaAlvesSolveExcel Жыл бұрын

Thank you, Ian. :)

@ziggle314 Жыл бұрын

Magic. Nice job, as always. Thanks for all your efforts.

@CeliaAlvesSolveExcel Жыл бұрын

Your very welcome! Thank you for watching till the end. 🔥🏆

@amandeepchandhok7166 Жыл бұрын

I really want to thank you for this. i have been looking to come up with a solution and yours is the best.

@CeliaAlvesSolveExcel Жыл бұрын

You're very welcome, Amandeep. Enjoy! 😊

@rodyeltabaa6871 5 ай бұрын

Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then list the selected table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊

@CeliaAlvesSolveExcel 5 ай бұрын

That's awesome! The INDIRECT function is very useful and unavoidable in many scenarios. When possible, I prefer not to use it because it is a volatile function , meaning that it is constantly recalculating with every change in the workbook. The technique you describe is what I used before the dynamic array functions. Another disadvantage of it is that the item names cannot have space character because neither can table names. There's ways to work around that, but extra work. Take a look at the video by Wyn Hopkins in his channel where he simplified the technique I presented in this video.

@wayneroberts7429 3 ай бұрын

Magic! Thanks so much for this solution Celia.

@CeliaAlvesSolveExcel 3 ай бұрын

You're welcome, Wayne! Glad that it helped. In the meantime, I've evolved to using a simpler technique that does not involve creating names. If interested, please see here kzfaq.info/get/bejne/b7-qq7Cmzs7DZ3k.htmlsi=xBMadi7-K7yBZIuA In this presentation I explain dropdowns from creating one up to creating 3 dependent ones.

@olanrewajubello5881 6 күн бұрын

Thank you very much for sharing your expertise

@CeliaAlvesSolveExcel 6 күн бұрын

You're very welcome! Thank you for stopping by. Glad to help.

@ExcelUpNorth Жыл бұрын

Very helpful video! I am a big fan of Named Ranges as well 😁

@CeliaAlvesSolveExcel Жыл бұрын

Thank you, Darryl!

@Fabi_terra 4 ай бұрын

This tutorial is incredible! Thank you so much for taking the time to make it. I really appreciate your effort. 🤗🤗

@CeliaAlvesSolveExcel 4 ай бұрын

You're welcome! Make sure to check the comments. There's a simplified version published by Wyn Hopkins that is easier to implement.

@Fabi_terra 4 ай бұрын

@@CeliaAlvesSolveExcel Thank you! Is it possible to add a function to prevent someone from entering the same data twice on the source list?

@CeliaAlvesSolveExcel 4 ай бұрын

@@Fabi_terra add a column to the table with countif. Range is the column where people enter the data. Criteria is the cell in that same column. Then put that inside IF and say is countif result is greater than 1 than "duplicate" else "".

@Fabi_terra 4 ай бұрын

@@CeliaAlvesSolveExcel Hey Celia, thank you so much! I appreciate it! Have a lovely day.😘

@CeliaAlvesSolveExcel 4 ай бұрын

@@Fabi_terra you're welcome! A lovely day to you as well.

@eduardomunoz2764 Жыл бұрын

Excelente video, gran explicación. Gracias Celia, saludos

@CeliaAlvesSolveExcel Жыл бұрын

De nada, Eduardo. Gracias por asistir y oferecer tu feedback. 🙏

@AjayDhole 4 ай бұрын

Superb! I have wanted exact solution for last couple of days, really this solution made my day... :)

@CeliaAlvesSolveExcel 4 ай бұрын

That's awesome! So glad that it helped you, Ajay.

@NathanAshe-xj7zv 8 ай бұрын

Trying to do this with 37 categories with 3-6 subcategories each, while adding another row for "titles" with ~5 titles per subcategory was not as enjoyable as the video. A great video all the same, appreciate the lesson Celia!

@CeliaAlvesSolveExcel 8 ай бұрын

You survived and learned! So glad that you made it! 💪🔥

@louisivara498 11 ай бұрын

Appreciate this so much. God bless your great heart

@CeliaAlvesSolveExcel 11 ай бұрын

You're welcome, Louis! Good luck in your projects.

@ClaudeBalleux Жыл бұрын

Very helpfull! Thank you!

@CeliaAlvesSolveExcel Жыл бұрын

Glad for that. Thank you, Claude!

@mestrecris1 Жыл бұрын

Magic! Repliquei a explicação com um tema do meu interesse. Muito obrigada

@CeliaAlvesSolveExcel Жыл бұрын

Excelente, Ana!

@markp8600 4 ай бұрын

Magic!!! Great video Celia 😎😎

@CeliaAlvesSolveExcel 4 ай бұрын

Thank you, Mark. Glad that you enjoyed it!

@nurfareehabintiahmadfisol7223 3 ай бұрын

Very helpful and clear !!!!

@CeliaAlvesSolveExcel 3 ай бұрын

Awesome! Glad that it helped. And thank you for your feedback. :)

@johnschroeder2795 2 ай бұрын

I like the examples!

@CeliaAlvesSolveExcel 2 ай бұрын

Thank you :)

@SishGupta 4 ай бұрын

THANK YOU!!!!

@CeliaAlvesSolveExcel 4 ай бұрын

You're welcome! Check a simplified version of this technique on Wyn Hopkins channel.

@davebickers7209 Жыл бұрын

Magic. Very impressive. Which link has the practice file?

@CeliaAlvesSolveExcel Жыл бұрын

Hi, David. Sorry for the mistake and thank you for asking. The link is now available. Glad that you enjoyed the tutorial till the end. 💪👌🔥🏆

@Mohamed.GadAllah 5 ай бұрын

Thanks a lot, Queen Celia for this amazing video, please, I've 4 columns in a table, and each column has several subcategories, please, how do you apply your technique for the 4 columns compared to your video with 2 columns? Thanks

@CeliaAlvesSolveExcel 5 ай бұрын

Hi, Mohamed. Please watch this video with this technique simplified: kzfaq.info/get/bejne/i5mHobBjr6nWhpc.htmlsi=nuPA4IgpLboih_WK

@kevinnguyen5698 5 ай бұрын

Magic! Do you have a video for ones that would have up to 10 subcategories with some subcategories having the same cell names in different main categories.

@CeliaAlvesSolveExcel 5 ай бұрын

I do not. But I've dealt with something similar. Please check my presentation to the event Excel Virtually Global in 2023

@CeliaAlvesSolveExcel 5 ай бұрын

kzfaq.info/get/bejne/o9SRnpeb3J3LpWg.htmlsi=K8we5cgg_f7myw9a around min 49:40

@Picla_Peremohy Жыл бұрын

Wonderful video Celia! Thank you for putting it together for us. Also, thank you for mentioning Ukraine in the Europe category ❤ ❤ ❤ Is there a reason for not including a space between "South" and "Africa"? Would that break the solution? I'll test when I get off this tablet and thought I would ask. Again, thank you for your giving nature to take the time to share your knowledge with the community.

@CeliaAlvesSolveExcel Жыл бұрын

Hi, Jim! Glad that you enjoyed the video. South Africa without space was a mistake. The ability of using spaces is in fact an advantage of this solution compared to other traditional ones. In the video description there's a link for a ready-to-use file in case it interests you. Good luck! :)

@Picla_Peremohy Жыл бұрын

@@CeliaAlvesSolveExcel Thanks Celia. This is so good to know that spaces are allowed and a great feature of this solution. 1000🌟

@cynthiasloan3169 2 ай бұрын

Need this for Google Sheets! Using a Named Range in Google Sheets doesn't respond the same as Excel.

@CeliaAlvesSolveExcel 2 ай бұрын

Cynthia look through the comments and find Wyn Hopkins video that I mention. He simplified this technique in a way that may work for Google Sheets.

@courtneygeorgie5758 9 ай бұрын

Thank you so much for this Celia! I do have 1 part I'm struggling with - I got all the way to the conditional formatting and have triple checked my formula, however the subcategory cells stay red after correcting the drop down category and do not return to unfilled. Is there a way to fix this? Thanks a bunch!

@CeliaAlvesSolveExcel 9 ай бұрын

Hi, Courtney. Conditional Formatting applies a format based on a condition. In this case, it is painting the cell in red if the values selected in the two columns are not a valid pair. You have to clear or amend those values manually. Conditional Formatting does not clear the cells from invalid values automatically. To do that automatically, you would have to use vba.

@markbuys5726 11 ай бұрын

hi, Thanks for the info is it possible to do this in reverse? example, By selecting a town , you'll get a city , state , country the continent to auto populate

@CeliaAlvesSolveExcel 11 ай бұрын

Yes. For that, you still need a list with all the towns and corresponding states and countries. Then, if you'd like, you can set the first dropdown for the user to select the town. In each of the other columns, you can have a formula with VLOOKUP or XLOOKUP to populate the state and country.

@gorga1709 11 ай бұрын

Hello Celia, this is so helpful, thank you very much!, i want to know more about dependent dropdown lists, especially making multilevel dropdown lists maybe 4 to 5 levels. Do you have the materials? I will be very happy if I can get it

@CeliaAlvesSolveExcel 11 ай бұрын

Hi! :) I have a ready-to-use file for 3 columns of dependent dropdown lists that you can get here: solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/ For more levels, we need to repeat the process by adding the extra supporting lists.

@CeliaAlvesSolveExcel 11 ай бұрын

If you are going to build that many levels, I recommend you watch this video by Wyn Hopkins who found a way of simplifying this method: kzfaq.info/get/bejne/i5mHobBjr6nWhpc.html

@gorga1709 11 ай бұрын

@@CeliaAlvesSolveExcel thank you

@DroneEyes Жыл бұрын

Oi celia o link para baixar o XLS mencionado acima nao esta funcionando :( Voce poderia concertar e nos avisar para que possamos baixar o arquivo? Muito obrigado!

@CeliaAlvesSolveExcel Жыл бұрын

Olá, Vicente. Pode por favor verificar se agora já funciona? Depois de introduzir nome e email e confirmar, deve ter acesso a uma página onde está o link para um zip no one drive contendo 2 arquivos.

@DroneEyes Жыл бұрын

@@CeliaAlvesSolveExcel eu consegui baixar os arquivos, porem a coluna "H" nao esta mostrando os valores no dropdown, voce tem que digitar o nome para que o campo seja preenchido. nao sei se foi essa a sua intencao mas muito obrigado

@CeliaAlvesSolveExcel Жыл бұрын

@@DroneEyes estão dois arquivos no zip. O que tem YT no final do nome está a funcionar correctamente porque é o que construí durante o tutorial. Tem que ter Excel para Microsoft 365 ou 2021 para poder usar no desktop. Ou se tiver OneDrive pode salvar os arquivos lá e abrir no Excel online que vai funcionar.

@ABellaLuna Жыл бұрын

Wonderful - but do you have a way to make multiple dependencies? In this example Category is selected from a list, Subcategory is dependant on what is selected in list. But what if you want to keep going with multiple sub categories? e.g. I select a service, then in sub category 1 it shows me where that service is available, sub category 2 is companies offer that service on that location, sub category 3 is if we have a current contract with the vendor selected, and keep going for a few more.

@CeliaAlvesSolveExcel Жыл бұрын

You can create as many subcategories as you want with repeating a similar reasoning. Your table needs to have more columns with all the combinations possible. For the 3rd option selected for example, the category would be concatenation of the first two values selected. I have a ready-to-use file that takes up to 3 columns in the items list. solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/

@CeliaAlvesSolveExcel Жыл бұрын

Although in your case, it seems that not all would be a subcategory in the same table. For example indicating if there is a current project with the selected client would probably be a column with a formula looking up the client in your list of projects. This list is separate from your list of services.

@Nelson10409 3 ай бұрын

Hello Celia, I inserted my dropdown lists in between other columns and noticed that if I apply filters; I lose the content of my dropdown lists...is there a way to apply filters wo losing them? Thanks

@CeliaAlvesSolveExcel 3 ай бұрын

Hi, Nelson. I tested on my end and I do not get that behavior. I wonder if it had to do with filters at all. I recommend checking the formulas associated to the data validation. The $ need to be in the correct place. Give it a review and good luck. Thank you

@Nelson10409 3 ай бұрын

@@CeliaAlvesSolveExcel , Thank you for reviewing this. 1. Found an error on the first data validation...I did not roll the formula to all table - this one is solved 2. Since I inserted the data validation formulas between other columns, when I apply filters; I only get them in one side of the columns...example have other data in columns "a & b" then data validation formulas in "c & d" and other data again in "e & f"; can I get them in both sides? Thanks again for your help.

@CeliaAlvesSolveExcel 3 ай бұрын

@@Nelson10409 you control where the data validation applies. It all has to do with how you set the rules. Since I published this video, a sonogram technique came along. I suggest that you check the comments to find that other way which is much easier to implement.

@CeliaAlvesSolveExcel 3 ай бұрын

Check this one: kzfaq.info/get/bejne/b7-qq7Cmzs7DZ3k.htmlsi=3EUWBU9l8wdAscIj

@user-dk8no3xz6x Жыл бұрын

If subcategory is not unique, what formula should I use? Please help.

@CeliaAlvesSolveExcel Жыл бұрын

Do you mean you then have a sub-subcategory? You would have to repeat the same reasoning. In the video description there is a link to a page where you can buy a file that has another level of dependency. The file is ready to use you only need to fill in the data. I hope it helps. solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/

@cammog87 9 ай бұрын

Hi thanks for your video sorry if this comment appears twice. I have a question can i draw information from 2 seperate dropdown lists and combine them into one list with selection from both lists?

@CeliaAlvesSolveExcel 9 ай бұрын

Not sure i understand what you mean. Are you referring to having a third dropdown where the options depend on the values chosen for the first two columns? Is that's the case, check the link in the video description. I have ready-to-use file for that. Basically, you repeat the same reasoning where the category is composed of list of all possible pairs of columns 1 and 2 concatenated.

@cammog87 9 ай бұрын

So i have 2 drop down lists both with thier own information but i want to be able after first selection in thier own individual drop downs i want to be able to draw both sets off information into 1 seperate droplist. Is this possible.

@CeliaAlvesSolveExcel 9 ай бұрын

@@cammog87 I am still unsure of what you mean. It might be helpful if you provide an example. Did you check the link I mentioned on my previous comment?

@CeliaAlvesSolveExcel 9 ай бұрын

Also, please see if this video helps: kzfaq.info/get/bejne/mMd6gcyXv8CnXX0.htmlsi=2KmM1x8wR0uXBbdf

@cammog87 9 ай бұрын

So the purpose im using it for is not for what you are using it for in your video example but i have use your example to get me to the point where i am at. So i have one 2 tier drop drop list. I want to create a second drop down list that is the same but with seperate information to the first. Once i make a selection in both lists is there a way to draw from both different list after i have made a selection in them to put them into its own seperate list createing a new list with choices from the 2 seperate lists. I know this is hard to explain im just trying to see if its possible in excel

@louisvanderhorst4022 6 ай бұрын

Terriffic solution Thanks very, very

@CeliaAlvesSolveExcel 6 ай бұрын

You're very welcome. Glad that you found value. :)

@ggbin8092 11 ай бұрын

I'm using google sheet, and I'm stuck at 'CategoryInRow'.

@CeliaAlvesSolveExcel 11 ай бұрын

Check the video I mention in my reply to @gorga1709. It shows a simplified version of this method that might be compatible with Google Sheets.

@roshanjacob5894 9 ай бұрын

its quite confusing when its in subcategory

@baterdenepurevsuren7687 6 ай бұрын

hello, my # is not working :( how to fix it.

@CeliaAlvesSolveExcel 6 ай бұрын

Which version of Excel do you have? It needs to be Excel 2021 or Microsoft 365.

@adamtabor6754 8 ай бұрын

No exercise file.

@CeliaAlvesSolveExcel 8 ай бұрын

Please check the video description.

@shravanidarekar132 3 ай бұрын

TOOOOO Lengthy ....lost focus

@Colin-Fenix 7 ай бұрын

Seems outdated and complicated compared to many other solutions to this problem.

@CeliaAlvesSolveExcel 7 ай бұрын

I only know one solution better than this one that was inspired in this technique and then improved. Check Wyn Hopkins KZfaq channel for that one. A solution that uses very recently added Excel dynamic array functions cannot be so outdated. :) Complex, yes, but any pervious solutions I've seen were not as dynamic as this one, allowing to create as many lists of subcategories you need and with space characters in the category name. Depending on your case, you may not need this level of flexibility.

@shravanidarekar132 3 ай бұрын

Toooo Boring same thing to many repetative

@roshanjacob5894 9 ай бұрын

its quite confusing when its in subcategory

@CeliaAlvesSolveExcel 9 ай бұрын

I know. Two suggestions: - check Wyn Hopkins KZfaq channel and find his last video about dependent dropdown lists - he simplified my technique. - you can buy a file that I prepared with all set up where you only need to enter your data: solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/