Easiest Multi-Level Drop-Down Lists in Excel!

[@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. 👍

[@ziggle314]

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

[@ExcelOffTheGrid]

Great - let me know if you get any issues.

[@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.

[@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 😄

[@kebincui]

Very creative. Excellent as always. Thanks Mark👍

[@ExcelOffTheGrid]

Thanks kebin.

[@TacereMors]

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

[@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.

[@anillpatel]

Excellent video presentation. Very useful. Thanks Mark.

[@ExcelOffTheGrid]

Thank you. I'm glad you enjoyed it 😁

[@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. 😁

[@alexrosen8762]

Great stuff 👌

[@ExcelOffTheGrid]

Thank you! Cheers!

[@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.

[@williamarthur4801]

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

[@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.

[@chrism9037]

Awesome, thanks Mark

[@ExcelOffTheGrid]

Thanks Chris, My pleasure!

[@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.

[@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.

[@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.

[@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.

[@iincitr]

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

[@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.

[@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?😢

[@chta2010]

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

[@panoskatotriotis]

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

[@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.

[@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!

[@excelconuntalleo5280]

Que sucede si ya asignada la 3ra columna, modificas la 1ra o 2da?

[@ExcelOffTheGrid]

That requires a significantly more complex solution.

[@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. 😁

[@GeorgePapaioannou-n2m]

I am following the steps. They work when use them on cell, but when I try to do the dropdown list it show me an error and doesnt allow me to insert it in data validation. It is 4 times multi depented drop down list. I work same formula with filters and works well with more multi depented lists (7) but this one I want to insert it into a table that will always refresh when insert new rows. If you have any advice you are welcome. Just to know it is in the online version.

[@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?

[@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.

[@liquidapathy82]

I dont think i heard why you added the &"-"& parts. I thought i was following. But i feel that part is slipping through my brain. Is it somehow telling excel that you need the data from both cells? I dont ubderstand how its not creating a text value of the cells with a hyphen in between, which would not have any matches...

[@ExcelOffTheGrid]

Let me give you an example. If you want to look up A1 and 1, but your lookup array includes A and 11 , along with A1 and 1. The a lookup without a spacer character will also match against A and 11 (which is the wrong value). So including a spacer character ensures that A1 and 1 only matches against A1 and 1. Make sure the spacer is not a character in the data set.

[@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.

[@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?

[@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.

[@ozgur937]

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

[@Al-Ahdal]

1st comment

[@ExcelOffTheGrid]

Speedy 😀

[@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 🤣