NEW Excel Drop-Down Lists That Adapt to Your Data

Create self-updating dependent drop-down lists with dynamic array formulas.

Dependent drop-down lists where you choose a country and magically the next drop-down list only displays the states for that country used to be laborious to set up and maintain, but now that we have dynamic array formulas, we can make them dynamic, so they automatically grow with your data.

In this video, I’m going to show you two approaches, one that’s relatively straight forward but has some limitations, and one that overcomes those limitations using a trick that is mind blowing.

CORRECTION: I learned this technique from Ceasr of the @Excelambda channel. Check out his work.

LEARN MORE
===========



⏲ TIMESTAMPS
==============
0:00 Method №1
5:14 Method №2

#Excel #ExcelTricks #ExcelDataValidation

[MyOnlineTrainingHub]

❓How do you currently manage long lists in Excel? Share your approach!

[ismailrajiwate]

Explained the most complicated array and filter functions in a very simple way. Depended drop down list now easy to use. Thank you very much

[bigbyrd]

Wow! Mynda, you explained some of the most difficult formulas and made it seem very easy! This video opened my eyes to possibilities I didn't know I had. These methods will definitely save me a lot of time with large data sets when using the drop-down feature. Thank you so much for sharing your knowledge with us.

[josh_excel]

I did not know you can add # after the XLOOKUP range like that. Very interesting!

[patrickschardt]

I like how easy drop down lists are becoming

Thanks for the detailed explanation

[leelawrence]

That was pretty cool, so many ideas for it's uses now.

Your tips on using the filter function and having it spill the results were a game changer in the past.

[ctibornovotny]

This is super useful for me. You can even use it in the same table where are the data for your drop down lists if you allow the user to overwrite data validation.

[DecrepidOne]

I love this method. Creating dynamic dependent lists has been painful. This will make it so much easier and reduces the chance of the recipient of the file mucking up the lists. Thank you.

[murijansulemana]

This is absolutely beautiful. Struggled setting up a dashboard last week, now i can go back to it 😊

[davelaff]

This is going into my top-ten Excel videos playlist. Nice!

[michaelbrown]

Thanks, Mynda. I hadn't seen any videos on this before, but got the same info from comments on a similar dropdown video, which saved my me a ton of work. Thanks for making this so I can now easily reference the technique. I have used this in Tables with little fuss. Any concerns that you can share? Great video!

[grimaldasgrydas]

Thank you for the great video and handy info - very useful as always! :) I have been using the first trick (with a bit different approach) for a while now - a bit messy but works well. I have noticed that sometimes it's convenient to name both lookup arrays, and in that case you can use # for those without having to rely on pre-expansion.

IMPORTANT NOTE: A word of caution, it may have been worth mentioning, that INDIRECT, OFFSET and CELL (in this case) are all *volatile* functions, so if you are using a huge set of data with the second trick, it's can and likely will cause noticeable lag, especially if you have an older/slower PC. I suppose with the new PCs it's not such a big deal, but I have a powerful PC and some really large tables in some of my projects that can easily cause issues with these functions, especially when nested.

[mouhammadwasseemshaabani]

Thanks for the best excel teacher ever, I just hope excel will consider upgrading lists so it will update the selection depending on another list selection, like to put the first option automatically as example when parent dropdown list change, (without using vba)

[ivanbork]

You are amazing, the # outside Xlookup I have never seen 😊

[Excelambda]

Great video !! Great trick !! ✌🏼
Remembered, 3 years ago you did "Easy Dynamic Dependent Data Validation Two Ways" and I had this comment with 5 replies:

To make it fully dynamic adding new countries with their regions :
DV for Countries at
DV for Region at I3#=FILTER(TblRegions[Region], TblRegions[Country]=OFFSET(INDIRECT(CELL("address")), , -1))
Input data: after you select a country in B4 for example, double click in the cell to the right (C4), click the drop down, you will get the corespondent regions.
As long as the region is to the right of country DV range, you can place them

😉✌🏼

[dkarthikr]

Incredible! Now I'm going to get thru ur tutorial and move 1 level up my EQ (excel Quotient)

[muhammadtambawala]

Great video Mynda, thanks.
The `#` sign at the end of the formula and the cell address are both great.
The issues I encounter with data validation are:
A) if the text selected from the validation list is no longer in the source, it doesn’t trigger an error, so it can display text that isn't in the source list. B) Another issue is that someone can copy and paste values from another cell and data validation will still accept it.

[leonidasgoniotakis]

Thank you for this great and very-well explained tutorial! I have one question on 2:47 . In the data validation window, in "source" box you are inputting $H$4 and then the hash sign to reference the rest of the "horizontal array", so my question is how does excel know that it needs to reference horizontally here ( as we want in this case of course) and not vertically (that it would be wrong for our goal) ? I hope my explanation makes sense, sorry if i missed something clarified here. Thank you in advance!

[Jojo-gfqb]

Great
Dependent drop down list has become easier!

[Trucpq]

It is great ! Thanks for sharing with careful explanation ❤