Easiest Multi-Level Drop-Down Lists in Excel!

[@ziggle314]

Thanks Mark. Looks pretty simple. Already tried it on one worksheet.

[@ExcelOffTheGrid]

Great - let me know if you get any issues.

[@TacereMors]

Thanks Mark - awesome and resolved a conundrum I was working on.

[@cmbocc]

I agree that’s one of best and easiest methods to use, and well explained. Thanks Mark!

[@ExcelOffTheGrid]

Thanks - I hope you can put it to good use. 👍

[@omarnader776]

again and again, thank you so much for your easier and useful explanation

[@ExcelOffTheGrid]

You’re welcome - I glad you found it useful.

[@PaulEBrownbill]

I sort of followed that through, what a formula you end up with, thanks Paul

[@ExcelOffTheGrid]

If you’ve not see these formulas before, it can be a little tricky.

[@kebincui]

Very creative. Excellent as always. Thanks Mark👍

[@ExcelOffTheGrid]

Thanks kebin.

[@MethiniBongchomphoMKTP.Mariya]

Thank you so much. I search a lot of method how to do that. This video very useful.

[@ExcelOffTheGrid]

Great news, glad I could help. 😁

[@warrennalty6599]

I found it easier to use unique(sort(filter() with the filter referring to the higher level selection.

[@ExcelOffTheGrid]

I believe UNIQUE/SORT/FILTER only works if you output the result to a range. Therefore, it's only suitable with a single chain of dependent lists. If you have multiple rows of cell requiring drop-downs, I don't think it works so well.

[@anillpatel]

Excellent video presentation. Very useful. Thanks Mark.

[@ExcelOffTheGrid]

Thank you. I'm glad you enjoyed it 😁

[@alexrosen8762]

Great stuff 👌

[@ExcelOffTheGrid]

Thank you! Cheers!

[@chrism9037]

Awesome, thanks Mark

[@ExcelOffTheGrid]

Thanks Chris, My pleasure!

[@sledgehammer-productions]

As I didn't have DROP and CHOOSECOL at the time (about a year ago), I've managed to make 4 level dependent dropdown, with some helper cells and filter arrays. It could be 5, I don't remember, would have to look at the file, but it was a nice challenge. Not sure anyone using the model really appreciates it, but hell, that's the live of a Excel model builder 😄

[@McIlravyInc]

I almost understood why this works! Ha! It does make more sense as a solution than the other methods I've seen. I'm looking forward to part 2 where we can choose any one of the categories and the others bwcome dependent... because sometimes I need a certain color shoe vs a certain style! 😊

[@ExcelOffTheGrid]

Part 2 would be very difficult - maybe impossible. You would need to create different sorted areas for every possible order. Then use an IF inside the Data Validation to select the relevant area depending on which other options are selected. Maybe something for the membership - probably too much for YT.

[@ximox]

Very useful, I used to use "indrect" formula. This look interesting too.

[@ExcelOffTheGrid]

As I’m sure you’ve experienced, INDIRECT is a pain to manage. The method in this video, takes a bit more skill, but is significantly quicker to setup and maintain.

[@williamarthur4801]

I have done dependent drop downs but they always had helpers and were a bit fiddley, really great use of these newer functions.

[@iincitr]

Thank you for your very good explanation. I like it.

[@Hortster]

Interesting technique, I'll have to compare it to the indirect function I'm using to see if it's more efficient. Thanks for sharing.

[@ExcelOffTheGrid]

With INDIRECT you have to create separate ranges for every items and for every combination of items. With this method you don't have to do that. So it should be significantly easier to keep up-to-date.

[@stevereed5776]

Very nice Mark. The problem with these solutions is that if you choose a different shoe, it would be nice to blank out the other two cells. I still like this solution though.

[@ExcelOffTheGrid]

By design, a hardcoded value in one cell cannot change a hardcoded value in another cell - so it's unlikely that will ever be possible. Unless of course you sprinkle in a bit of VBA to achieve it for you.

[@RichardJones73]

Wow. Excellently explained as usual. I wonder how performance will be affected if using large data sets though

[@ExcelOffTheGrid]

A data validation list only calculates when the button is clicked, it's not part of the main calculation chain. So I believe performance impact should be insignificant.

[@chta2010]

Hi Mark! Your tips are great; I bet, you're also dreaming in rows and columns ;-)

[@IvanCortinas_ES]

Excellent suggestion, Mark. I like it because the INDIRECT function does not appear. Thanks for sharing.

[@ExcelOffTheGrid]

It’s much easier to manage than INDIRECT because we don’t have to add new named ranges for each new item.

[@adhamm5503]

Simply genius 👏 Thanks bro. I was thinking what if we select shoe type from first list and below it displays a picture of the type of toe and how much qty available and same for colors.

[@ExcelOffTheGrid]

Sounds like a beautiful solution to me. Great idea.

[@pkrempf]

Thank you! This is an excellent video showcasing a range of new features. However, I'd like to highlight one point: when I tried the formula with # in Data Validation today in Excel 365, I couldn't validate it. After updating my Excel 365, I was able to validate the formula in Data Validation. So, while the process is fantastic, I’ll hold off on distributing data validation with # references until it’s more stable across versions.

[@ExcelOffTheGrid]

The issue is not the #, which is available as far back as Excel 2021. The issue is DROP and TAKE functions, which are in Excel 365 and Excel 2024. With such a large percentage of users on Excel 365, I see less and less reason to state which versions things work on.

[@linhuu957]

Hello, thank you for sharing this cool tip. Btw do you have any solution for Excel version 2019?😢

[@hollydunne2687]

Fantastic video! Definitely helped me start understanding more about how the dynamic arrays work and really useful practical application. I was wondering if there's any difference in omitting the "-" part of the XMATCH arguments and just putting the ampersand? ie K6&L6,$F$6#&$G$6#

[@ExcelOffTheGrid]

If your lookup array contains the following: A1 | 1 A | 11 The only way to select the correct value is by using a space character between the values (which is what the "-" does). So, it's a good idea to always include one, just in case.

[@hollydunne2687]

@ExcelOffTheGrid thanks very much!

[@carolynbauman6670]

Question: To select the color you would have to have already selected the toe? If I wanted to select the color second after the shoe, would I need to redo the order of the dependent formulas?

[@ExcelOffTheGrid]

You could have any order you want - you just need to understand which formula to use for the - Drop-down - Dependent drop-down - Dependent-dependent drop-down You can then keep adding to the formulas and create as many levels as you wish.

[@DimasFajar-ns4vb]

wow and peace be upon you sir from me

[@ExcelOffTheGrid]

Thank you 😁

[@hugocristini9433]

"No tricky formula" : proceeds to use formulas I've never heard about with array brackets hahaha The solution was fairly easy to apply though !! Many thanks !!

[@ExcelOffTheGrid]

You're the first person to identify that I said "No tricky formulas" and then proceeded to pull out some reasonably tricky formulas. 10/10 for paying attention. 😁

[@panoskatotriotis]

Hi is there a way to user the filter function results on a table?

[@AdamMarks]

When I do the Data Validations, it doesn't give me the unique listing you have, but instead duplicates all the values in the range if the are multiple instances of the value. Any idea why that is?

[@KO1967]

There are always ways in Excel to do things creatively as you have discovered. It's just too bad that Excel can't allow the FILTER function within a named range formula.

[@354dw]

Great technique. The next step is to reset the dependant list selection if the parent is changed….

[@ExcelOffTheGrid]

A cell cannot change the existence of a hardcode value in another cell, so you would need VBA for. You can always use conditional formatting to highlight the items which are wrong.

[@FrancoisBothaZA]

Surely FILTER() is much simpler and achieves the same for the dependent lists, or am I missing something?

[@ExcelOffTheGrid]

How are you going to use FILTER, if you have 3 columns of dependent drop-downs across 100 rows?

[@ozgur937]

Thanks, I remember there was a total brainf.k way for depended dropdown lists.

[@Al-Ahdal]

1st comment

[@ExcelOffTheGrid]

Speedy 😀

[@LauraGonzalez-jo8wh]

Excellent Mark, I used =SORTBY(Data,Data[Shoe]) to sort the table, the other part was amazing.

[@ExcelOffTheGrid]

You need a multi level sort to ensure the ranges for the sub levels work. So you need to sort in the Shoe, Toe, then Color. You can achieve this with SORTBY, but the SORT syntax is shorter. If your data is already grouped correctly, you don’t need to sort at all.

[@LauraGonzalez-jo8wh]

@@ExcelOffTheGrid, =SORTBY(Data,Data[Shoe],1,Data[Toe],1,Data[Color],1) 😃

[@ExcelOffTheGrid]

Yes, that will do it.

[@Hemendra3]

Use indirect formula also much easier than this.

[@ExcelOffTheGrid]

Really ??? But, if you have 100 possible items, you have to create 100 named ranges. Plus it takes constant maintenance for new items being added. I’m guessing you love manual work 🤣