NEW Excel Drop-Down Lists That Adapt to Your Data

[@MyOnlineTrainingHub]

❓How do you currently manage long lists in Excel? Share your approach! Learn Excel with my courses: bit.ly/ddlists24courses

[@shakiraasfoor7599]

amazing Beter for cell function

[@shivambhola373]

Hi Mynda Treacy, I have does same working in MS Office 2021 but On part of Circular reference drop down list didn't showed up, i can see the drop down button but it didn't clicked like it has been blocked, does that function works only in Excel 365 ?

[@mshi5826]

@@shivambhola373 @MyOnlineTraingHub I use Office 2021 too and for Method 2, the Countries duplication didn't remove in drop-down list.

[@ismailrajiwate9565]

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

[@MyOnlineTrainingHub]

Thanks so much!

[@patrickschardt7724]

I like how easy drop down lists are becoming Thanks for the detailed explanation

[@MyOnlineTrainingHub]

Glad it was helpful!

[@josh_excel]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@bigbyrd057]

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.

[@MyOnlineTrainingHub]

I'm so glad it was helpful!

[@ctibornovotny5591]

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.

[@MyOnlineTrainingHub]

Great to hear!

[@leelawrence3379]

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.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

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

[@MyOnlineTrainingHub]

Glad you can make use of it!

[@theluckcoach4592]

🤯wow that 2nd option!!!

[@MyOnlineTrainingHub]

So pleased you liked it!

[@Trucpq]

It is great ! Thanks for sharing with careful explanation ❤

[@MyOnlineTrainingHub]

Glad you liked it!

[@davelaff73]

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

[@MyOnlineTrainingHub]

Wow, great to hear!

[@ezefrank3138]

This video solved the problem many other videos I spent time watching did not solve.

[@MyOnlineTrainingHub]

So pleased it was helpful!

[@tibibara]

Brilliant tutorial! Thanks!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@chrism9037]

This is great Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@williamarthur4801]

Loved adding the # outside of Xlook to get the spill, really useful to know.

[@MyOnlineTrainingHub]

Great to hear!

[@murijansulemana5270]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@melbruning]

wow, definitely need to practice this one. this is great!

[@MyOnlineTrainingHub]

Great to hear!

[@dougbrown3607]

Brilliant. Thanks for sharing!

[@MyOnlineTrainingHub]

Glad you liked it!

[@Jojo-gf5qb]

Great Dependent drop down list has become easier!

[@MyOnlineTrainingHub]

So pleased you liked it!

[@ivanbork4175]

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

[@MyOnlineTrainingHub]

Thank you so much!

[@yulinliu850]

Love it. Thanks for sharing 👍👍❤❤

[@MyOnlineTrainingHub]

My pleasure!

[@mdhknowledgeshare]

Really very nice and helpful.

[@MyOnlineTrainingHub]

Thanks, glad you found it helpful!

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

[@MyOnlineTrainingHub]

Thanks for watching! In this case because we are only using INDIRECT, OFFSET and CELL in one cell, it won't have any performance implications irrespective of the size of the dataset. 😅 If we were to use them in the data validation source, then we could have issues, but thankfully that's not required here.

[@grimaldasgrydas]

@@MyOnlineTrainingHub Thanks for replying! I suppose I was too tired when writing my comment. I should have been more careful to point out that my point should apply only if one applies this to a *huge* set of data (referenced with these functions) AND plenty of drop-downs - just a few there and here probably wouldn't harm the performance unless the references are gigantic or the PC/Mac being old enough to have performance issues easily. I have some files where I have tables of some ~8 million cells, hundreds of drop-downs etc. In such cases my PC would certainly hiccup. In such files I'm always using a slightly different version of your first approach (named the references and made use of # so they always stay dynamic to avoid errors), mainly to avoid potential errors like forgetting to update the references when I expand the sources. By the way, do you think it could be possible, at least in theory, to add a third layer to the first approach? So far, I have deemed it impossible, but I wonder if there were tricks and workarounds to achieve that. I think I will try experimenting on it, but it will most certainly be inconveniently complicated and messy, if even possible in the first place. (Sorry for the lengthy reply 🙈)

[@sahralsahri7180]

Thanks for your effort🌹

[@MyOnlineTrainingHub]

My pleasure!

[@jitendratrivedi7889]

As usual amazing video , too much information, explained in a very simple way

[@MyOnlineTrainingHub]

Thanks so much!

[@Petrus_Mi]

This is magic! thanks!

[@MyOnlineTrainingHub]

Glad you like it!

[@mouhammadwasseemshaabani47]

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)

[@MyOnlineTrainingHub]

Yes, that would be a nice change, but I doubt we'll get it, unfortunately.

[@BoraHorzaGobuchul]

Yes, multilevel dropbox lists should def be included as a standard feature...

[@ruzaqir354]

Thx for the knowledge

[@MyOnlineTrainingHub]

My pleasure!

[@muhammadtambawala6379]

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.

[@MyOnlineTrainingHub]

Yes, unfortunately they're not as robust as they could be.

[@muhammadtambawala6379]

Hopefully in few years time 😊

[@muhammadasad729]

A conditional format trigger may be what you're looking for. This vid has a method towards the end: kzbin.info/www/bejne/eKGzgINne8uehpI And that '#' sign. Simply magical. Makes you wonder was it always there the whole time.

[@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 H3#=SORT(UNIQUE(TblRegions[Country])) 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 anywhere....................... 😉✌🏼

[@MyOnlineTrainingHub]

Yep, I stumbled upon your comment recently and thought I should do a video about that 😁thanks again for sharing it way back then, Peter.

[@Excelambda]

@@MyOnlineTrainingHub You are very welcome!!! 🙏🏼🙏🏼 I am not Peter 😉Cesar is my name !!

[@MyOnlineTrainingHub]

Ah, sincere apologies, Cesar! I don't know why I thought you were Peter. 😳 I've put a note in the video description with a correction.

[@Excelambda]

@@MyOnlineTrainingHub No problem at all !!!✌🏼😉

[@dkarthikr123]

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

[@MyOnlineTrainingHub]

😁 💪

[@sachinkishore4115]

excellent . thank you

[@MyOnlineTrainingHub]

Glad you liked it!

[@michaelbrown8821]

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!

[@MyOnlineTrainingHub]

Glad it was helpful! The only concern as with all multi-level drop downs is you can change a selection upstream that no longer relates to the downstream selections, so you may want to add some validation to highlight any occurrences of this.

[@leonidasgoniotakis164]

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!

[@MyOnlineTrainingHub]

It simply references the array. It doesn't need to know it's horizontal or vertical. That information will be passed to it as part of the # operator.

[@ianl1052]

Nice one, Mynda (and Bartholomew of course). More Excel magic. I love it. I could have downloaded the file, but where's the fun in that? If it had been a large file with hundreds or even thousands of entries, that would have been a different matter, of course. I'm not lazy, but I'm not stupid either. ;-) Many thanks for your time and effort in making this video.

[@MyOnlineTrainingHub]

Glad you liked it, Ian!

[@ianl1052]

@@MyOnlineTrainingHub You don't need to worry about me not downloading this particular file. I have downloaded others in the past and am, as a result, on your mailing list.

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@jorgelara3116]

Thanks! so useful topic ! Gby

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@TimEllisWW]

That is an interesting one adding the # after the ), what other tricks are out there like this one?

[@MyOnlineTrainingHub]

😁glad you liked it, Tim!

[@DingusBatus]

❤ an emoji for when words can’t describe it.🤩

[@MyOnlineTrainingHub]

🥰🙏

[@fankitty929]

Thanks for the video @MyOnlineTrainingHub, super helpful. I'm wondering if there's a way it can be dynamic in the sense that it'll only show regions that haven't been selected before as the input expands? Trying to use this as a rostering tool for a small team - as such we need to have the ability to only select staff that haven't been assigned on that day and time. (Dependent factors: location, time and day as some staff can't work certain days/hours)

[@MyOnlineTrainingHub]

Yes: www.myonlinetraininghub.com/reducing-data-validation-list

[@SianDoherty]

Tasmania is also a region in Australia. Misses out yet again😭

[@MyOnlineTrainingHub]

Yes, and most of the US states and UK counties. It wasn't personal, Tassie. Just a small dataset to illustrate an example 😉

[@haych-u3w]

Hi Mynda, great video. How would I add a 4th level based on the other 3?

[@MyOnlineTrainingHub]

Replicate the last example where I created 3 levels and make a 4th. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@mogarrett3045]

excellent

[@MyOnlineTrainingHub]

Thank you!

[@tabithaowens348]

Will the second method work in a table formatting?

[@michaelbrown8821]

I've used the 2nd method in tables, and it works the same. A life saver.

[@tabithaowens348]

@@michaelbrown8821 sweet! Thank you! Can’t tell you how many hours I’ve spent figuring out streamlining data/lists lol 😂

[@jamiegoodwin6667]

FYI AL is the code for Alabama, the code for Alaska is AK.

[@jamiegoodwin6667]

Great tip.

[@MyOnlineTrainingHub]

Thank you!

[@tazguy371]

I was going to say the same. Great demo, @MyOnlineTrainingHub!

[@dannywood7450]

I like the double click instead of F9 to change the cell address

[@MyOnlineTrainingHub]

Thanks for your feedback.

[@Roy-Chirodeep]

Following your method #2, i am getting duplicate entries in my countries drop down .. downloaded your sample files, but still the same .. is this a excel setting or are others similarly challenged as well?

[@walterplaessmann5865]

Yes same here. Maybe we’re on a slightly older release of Excel 365? Just speculating.

[@yabkhezr]

great

[@MyOnlineTrainingHub]

Glad you liked it!

[@stefankirst3234]

Thanks for these straightforward concepts! However, I personally would´t use the Cell function because it tends to break if the workbook is shared between users with different language settings. Or is there a trick to avoid that?

[@MyOnlineTrainingHub]

When using "address" in CELL it should be fine because it's only returning a cell reference.

[@stefankirst3234]

@@MyOnlineTrainingHub the german version is =ZELLE("adresse") and while ZELLE will translate to CELL when an english user opens the workbook the parameter in doublequotes won´t translate properly causing the function to break

[@kylelane630]

The issue I always had, and resorted to the old convoluted method, was due to the use of the INDIRECT function in the second list in Data Validation (and therefore range names) was you couldn't have spaces in your visible text e.g. Primary_Function

[@MyOnlineTrainingHub]

Hopefully, you can make use of one of the other methods that don't use INDIRECT.

[@arpwable]

I used to get round that by nesting a SUBSTITUTE function within the INDIRECT to replace spaces with underscores so that the INDIRECT matched my named ranges properly.

[@huthmann]

Wow!

[@MyOnlineTrainingHub]

Thanks for watching!

[@paolojavier9653]

What if you want to make a drop-down list that is dependent on 2 variables? Hoping to hear your expert advice. Thanks

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@HammerHeid1]

When I copy the formula across the columns (time reference 2:19) the original table lookup column is changed (table column name to original cell +1 position to the right) on each copy, this seems rather strange behaviour and unexpected, any suggestions as to why my Excel desktop is operating differently?

[@MyOnlineTrainingHub]

The key to copying and keeping the referenced correct is to CTRL+C copy the cell and CTRL+V paste. If you left click and drag, the table references will change to the original cell +1. If you CTRL+C copy the cell and CTRL+V paste, the Table references will remain the same.

[@ahmetozlemis7694]

I think method 2 is easily breakable, since h4 returns a list according to last cell that was edited. If i choose a country in first line and a region accoring to that, and repeat it for the second line, region dropdown menu for the first line will show the regions of the second line, because last country that was selected was in second line, not the first.

[@MyOnlineTrainingHub]

The solution to that is to use the double-click variation for method 2.

[@kevinbush4300]

Oooohh!! It's about time this game changed!!

[@MyOnlineTrainingHub]

Hope you find it helpful, Kevin!

[@kevinbush4300]

@@MyOnlineTrainingHub thank you... always do, Mynda!

[@brighttriangle]

At 2:04, you mention the "Array of Arrays limitation." I tried this workaround in H4: =BYCOL(H4#,LAMBDA(curVal,FILTER(TblRegions[Region],TblRegions[Country]=curVal,""))) Sadly, that did not work. It must be the same principle. Anyway, thank you for another great video.

[@MyOnlineTrainingHub]

Glad you liked it! The array of arrays limit has no workaround 😁

[@workstuff5253]

I think they are the same, but, is there any advantage to using TRANSPOSE instead of TOROW?

[@MyOnlineTrainingHub]

No, both work 😉

[@slickz77]

Hi...on 2nd method, after I defined the name for countries column, the drop down list showed duplicate names as well (there were many names on the drop down list). Tried to put unique on the Data Validation's Source field but still no luck. Could you kindly advise on how to fix this issue?

[@MyOnlineTrainingHub]

Sounds like you have an older version of Excel that doesn't automatically remove duplicates from the list. In which case, create a separate list containing unique values in the worksheet cells and reference that in the data validation.

[@slickz77]

@MyOnlineTrainingHub many thanks... My excel came free with my laptop and its a home & student edition.. Perhaps this is the reason

[@MyOnlineTrainingHub]

That wouldn't make any difference. If you open my file, does it show duplicates?

[@benbowyang]

The folks in Tasmania are not going to be happy!!!

[@MyOnlineTrainingHub]

😂🙈

[@Chris23168]

Are they ever?

[@kevinbush4300]

😂

[@vida1719]

All these methods are great. However, some users change values in the original list and then don’t understand why the chart still shows old values. The green triangles in the cells with drop-down lists indicate this issue. It would be easier if Excel automatically updated these values.

[@MyOnlineTrainingHub]

Sure, that would be easier, but it can't do that. You can modify the source for the first drop down to an IF formula that checks if the second drop down cell is empty. If it's not empty it only displays the text "clear region cell first". e.g. in the data validation source for the country drop down: =IF(C5="",$H$4#,$J$3) Where cell J3 contains the text "clear region cell first"

[@andrusmihail5660]

Buna ziua, Te urmaresc mereu cu multa, multa placere! Desi am cunostinte limitate despre excel si am 67 de ani, inteleg APROAPE TOT. Nu ma las si vreau sa stiu mai mult, asta datorita tie. Te rog sa-mi recomanzi tutorialul in care descrii cum se scriu formulele imbricate, sau functie in functie. Cu recunostinta, Sorin

[@MyOnlineTrainingHub]

Please see this tutorial on nested IF: www.myonlinetraininghub.com/excel-nested-ifs-explained

[@mohammadj.shamim9342]

It is awesome, but It has one limitation. If someone clicks another cell, the filter gets erased.

[@MyOnlineTrainingHub]

Only until you select the country again, or in the case of the last example, double click.

[@northside76]

I am working in Excel and need to create a data validation drop-down list using data that is scattered across different rows and columns. The solution must: Allow the drop-down list to reference multiple non-contiguous rows and columns directly. Be dynamic, so changes to the source data automatically update the drop-down list. any ideas?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@MarkSmith-bj2gf]

Is there a way to have a drop down within a drop down?

[@MyOnlineTrainingHub]

No, you can't have nested dropdowns.

[@MarkSmith-bj2gf]

Thanks for your reply 😊

[@felixstraube2784]

For the second method, lets say no country or state are selected yet. Then select a country in B5, and then a state in C5. Then I go to B6 select another country, but then I decide to change the state of the previous country C5... it will show the states of the country selected in B6, not B5 as it should, wouldn't it?

[@MyOnlineTrainingHub]

Yes, this is how conditional formatting has always worked, so I suggest adding some conditional formatting to highlight cells that become out of sync like this so you can easily see and fix them.

[@felixstraube2784]

I mean the list in the drop down will be the incorrect list.

[@felixstraube2784]

With these named ranges you don't neet helper cells for the dependent dropdown Countries =TblRegions[Country] Regions =INDEX(TblRegions[[Region]:[Region]], MATCH($B5, TblRegions[[Country]:[Country]], 0)):INDEX(TblRegions[[Region]:[Region]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5))) City =INDEX(TblRegions[[City]:[City]], MATCH($B5&$C5, TblRegions[[Country]:[Country]]&TblRegions[[Region]:[Region]], 0)):INDEX(TblRegions[[City]:[City]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5)/(TblRegions[[Region]:[Region]]=$C5)))

[@ahmedshalaby9343]

what's # ?

[@MyOnlineTrainingHub]

Explained here: www.myonlinetraininghub.com/hash-sign-in-excel-formulas

[@SothearithKONGMrMuyKhmer]

❤

[@MyOnlineTrainingHub]

🙏

[@wiernixie]

I get an error when accessing the page to download the practice file. This site can’t be reached The web page at might be temporarily down or it may have moved permanently to a new web address. ERR_FAILED

[@MyOnlineTrainingHub]

I just tested it and I don't get an error. Please try again with a different browser: bit.ly/ddlists24file

[@dbruce3254]

AL=Alabama, AK=Alaska

[@MyOnlineTrainingHub]

Doh! I had a feeling I got that wrong 😂

[@njzzb]

都不是非常完美，第一个相对实用，开发出来给别人用的时候。至少不出错。

[@MyOnlineTrainingHub]

Yes, the first one is perfect especially if you don't expect to add more data to your table.

[@isaacakande2537]

Insightful as always. For some reason, my data validation is not able to accept formula =XLOOKUP(B5,$H$4:$P$4,$H$5:$P$5". Is there a reason why? there error message says "this entry leads to an error. Try entering different values to continue. would really appreciate a feedback Tracy. Thank you.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@isaacakande2537]

@@MyOnlineTrainingHub alright Thank you

[@vesaruusunen5685]

Great viedo. Thanks. One minor think. Sort should be done before unique. Excel works faster that way. (source: kzbin.info/www/bejne/fHWleaJ6lq6Ui6c) In this example it doesn't matter, but to get it in to the flesh memory it would be better to do it allways the correct way. Like this: =transpose(unique(sort(TblRegions[Country)))

[@MyOnlineTrainingHub]

Thanks for sharing!

[@ahmetozlemis7694]

Interesting. I have always assumed that it is faster the other way.