How to Create Searchable Drop Down Lists in Excel with ZERO Effort!

Рет қаралды 572,402

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Finally we get SEARCHABLE drop down lists in Microsoft Excel WITHOUT writing complex formulas. There's no VBA, no filter function. The autocomplete functionality is there by default.
The ability to search for values in data validation was released on the web (online) version of Excel last year but now it's available in the Excel desktop version as well. To search the drop-down list you just have to start typing in the cell. A list of search results will appear and filtered down as you type. It also automatically suppresses empty cells.
🔍 Key Takeaways:
▪️ Learn how to create searchable drop-down lists without any complex coding.
▪️ Understand how to use Data Validation for creating dynamic, user-friendly drop-down menus.
▪️ Discover two major improvements: Auto-Complete in drop-down lists and automatic exclusion of blank cells.
▪️ Explore the new Search Box option for cells, enhancing navigation and functionality in Excel sheets.
You can use this to create multiple searchable drop-down lists or data validations in a single sheet.
This update is for Office/Microsoft 365 subscribers for Windows and Mac.
00:00 Searchable Drop Down Lists in Excel
00:29 Create Searchable Drop Down Lists Without Formulas
03:33 New Search Box Option
05:27 Wrap Up
🎬 LINKS to related videos:
Searchable Drop Down List in Excel: • Searchable Drop Down L...
Many SEARCHABLE Drop-Down Lists in Excel: • Many SEARCHABLE Drop-D...
📖 Complete blog post: www.xelplus.com/default-searc...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel

Пікірлер: 836

@LeilaGharani 2 жыл бұрын

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter Drop-down lists now automatically exclude duplicates, showing only unique values. Not Excel related: who's a fan of chips & yoghurt here (5:14)?

@mohammadj.shamim9342 2 жыл бұрын

I think we have the same feather. I eat yogurt almost every day. Chips are of course always on the work desk.

@nanimanibog 2 жыл бұрын

🙋🏻‍♂️🙋🏻‍♀️🙋🏻

@walking_on_by 2 жыл бұрын

I'm eagerly anticipating the new Leila cooks course 😁

@omnipop4936 2 жыл бұрын

I'm a fan of seeing _you_ do chips & yogurt. Still haven't tried it myself though. But, I've always loved dipping my french fries into my vanilla shake, so,... _kinda_ similar.

@erkaexcel 2 жыл бұрын

Only excel 365 ?

@ryanpeterson7998 2 жыл бұрын

I'm super pleased to see this finally headed to Windows. As much as i'm not a fan of Google/Android i will give them kudos for always being first to the market on a lot of useful items that Office should of had years ago. Thanks for the amazing tutorial videos!

@SeanGHOB 2 жыл бұрын

Thanks for letting us know, Leila. This is how drop-down lists should have always worked. Glad they finally fixed it.

@LeilaGharani 2 жыл бұрын

Very true!

@shakilash11 2 жыл бұрын

Should also add Most recently used value

@accountantzone5581 2 жыл бұрын

@@LeilaGharani It is not working in my office 365. Please can you guide me on what are possible reasons?

@cassioalexan 2 жыл бұрын

Great news, Leila!! And it's even better to know this new feature from you! Finally Excel has brought us a change in something so simple but extremely useful. Thanks for sharing!

@LeilaGharani 2 жыл бұрын

My pleasure!

@nanimanibog 2 жыл бұрын

Omg. This is a very much needed feature. I'm so excited, I can't wait anymore!!!

@SPARCHE 2 жыл бұрын

I added searchable dropdown lists to my sheets thanks to you, but the number of steps to generate a list with unique entries and then reference it was making my sheets very heavy to run. Now it'll be so much easier =)

@user-rf4cy8bu1j 2 жыл бұрын

Hi Sparche, I am using Microsoft 365. However I can't find this new feature. Is it possible to let me know how to add this searchable dropdown list to sheets? I really don't know how to find it.

@SPARCHE 2 жыл бұрын

@@user-rf4cy8bu1j kzfaq.info/get/bejne/kJOYZbh2t5nGj2Q.html This is Leila's video on the subject before the changes. The changes are yet to be implemented for general public, so you'll have to settle for this old method.

@raghujagwani3164 2 жыл бұрын

hi Leila, Thanks for the tip, one of the annoying thing about drop down list was that if we had blank rows at the bottom, for an empty row it would try to match and start at the bottom of the list, hopefully the automatic blank skip with elevate that issue.

@linacastleofmystery 2 жыл бұрын

i just wrote a formula for multiple dependent drop down menus that were NOT searchable and it took me a while to set up with what I needed lol I then realized that the menus were a pain because I did not set them up as searchable......so this is nice to see :)

@josecoss5897 2 жыл бұрын

Love how simple you make it to understand.. Thank you.

@ChadrickNurn 2 жыл бұрын

Even with as hard I worked to get my lists to automatically remove blanks, and to make them searchable, I am not upset to see this. I am delighted that I don't have to worry about those insane formulas and named ranges anymore. I am so excited to see this.

@LeilaGharani 2 жыл бұрын

👍

@hoofdstukgemist1703 2 жыл бұрын

We just spent months learning and using all different types of options, this is a blessing in the sky, unfortunate it couldn't be here sooner.

@ruthngmj 2 жыл бұрын

Finally! Thank you for sharing. Can't wait for it to be available soon.

@CielBieDee 2 жыл бұрын

Your tutorials are a life saver. Keep these vids up

@JashimUddin-zb5qn 2 жыл бұрын

The simple method in less time is great, you explained it very nicely and it felt good

@mattschoular8844 2 жыл бұрын

Fantastic. I can't wait for MS to release this to the masses. Thanks Leila

@LeilaGharani 2 жыл бұрын

You're most welcome!

@mariafriend644 2 жыл бұрын

I always thought that should have been included, finally!! Thank you for the heads up.

@kuuuyajim 2 жыл бұрын

This is great news! Hopefully, creating a dependent drop-down list also get easier. 😅 Thank you Leila! ❤️

@user-iq4gv8vb6f 3 ай бұрын

But it's not fair... why did I learn the old method with the search function and everything and in the end it became easy for everyone so what did I do with it? Where is my advantage?

@kuuuyajim 3 ай бұрын

@@user-iq4gv8vb6f Well, it is what it is. Don't worry, we feel the same. 😅

@cavyommmehta 2 жыл бұрын

Wow! Please keep us informed about new features just like this. ✔️💖💯

@tekylyfasharing9995 2 жыл бұрын

Finally we can search for dropllist, thanks Sister Leila for sharing this great video. I'm Sruntek From Cambodia.

@fayekeller7411 2 жыл бұрын

Can't wait for the searchable option this will make sure help.save time and I see using this for product builds for customers and employees

@amandaramsaroop9027 2 жыл бұрын

Awesome!! So excited! Thanks for Sharing this!!

@ExcellentIdeasTips 2 жыл бұрын

Looks good 😀 This will save some extra work to do this😉 Thanks for sharing

@reighbernal188 2 жыл бұрын

Wonderful! I have been waiting for this...

@barteczekz 2 жыл бұрын

Please promise that you will inform us when it moves out of beta! We need this for general use in organizations. Thank you!

@jayawardhana8486 2 жыл бұрын

Is this feature will be available for all version? Or just for 365/above

@surendrapandey4158 2 жыл бұрын

@@jayawardhana8486 for 365 only I guess. For premium users who paid for excel

@mfzasr5665 2 жыл бұрын

LAMBDA has also been on the card for a long.

@jtmh31 2 жыл бұрын

@@surendrapandey4158 Not yet. Enterprise for Apps subscriber here with current channel. No idea when this will be available.

@surendrapandey4158 2 жыл бұрын

@@jtmh31 ya. Waiting hard

@mohammadj.shamim9342 2 жыл бұрын

Leilan is always ahead. Thank you so much. Wonderful news.

@vinyasv1444 2 жыл бұрын

Even Microsoft don't know some features you are explaining to public....great work 🤝🤝👏👏

@nonoobott8602 2 жыл бұрын

Great tutorial as always. Thanks for sharing

@darkoblagojevic7512 2 жыл бұрын

Oooo finally, you are wonderful as always, thank you and greetings for your team

@LeilaGharani 2 жыл бұрын

Thank you! 😃

@hanifcesarioabdullah6468 2 жыл бұрын

Finally ! I really wanted this feature is added on excel. And, here is the answer ! Thank god :)

@paulprobst7728 2 жыл бұрын

Thank you for yet another informative tutorial, @Leila Gharani ! Peanut butter cheesecake sounds delicious!!

@markowor8752 Жыл бұрын

You are the best!! Great material

@tifouraamina1761 2 жыл бұрын

😃finally. Thanks for sharing Liela 🤗you're the best 😊😊

@CreatorOfWorlds 2 жыл бұрын

This was needed for so long. The search feature is also nice. But more wants more. Now give us native cascading/linked validation drop down lists😏

@NiesLife 2 жыл бұрын

Can't wait for this to be available! This is the best things I've seen in a long time 😂

@mamachugh1125 2 жыл бұрын

This is fabulous! I work without the ribbon and this makes it absolutely unnecessary. Perfect!

@aaronphipps2117 Жыл бұрын

Thank you! Do we know when this will be available for all desktop Excel 365 users? My excel is the most up-to-date version, but it's still not available. (I do have it when using Excel online, but my team uses the desktop version more.) Wonderful tutorial!

@Rogue-productionsCoUk 2 жыл бұрын

This is GREAT news.. the one feature I think is still missing is the ability to select multiple items from a drop-down list. For example when a risk impacts more than one project but not ALL projects. Can do it in sheets, but not Excel without clunky VBA. Search and autocomplete are big steps forward though!! Thanks Leila!

@basseyinem5018 Жыл бұрын

Perfectly said

@azmibumci3931 2 жыл бұрын

Finally 😄 thanks for sharing!

@sylviawu9921 10 ай бұрын

Hi Leila! I wanted to say this is finally available today for 365 users today! So excited! Have been waiting for a year for this to transition out of beta and it's finally here! :D

@LeilaGharani 10 ай бұрын

That's great to hear! It really took them a long time.

@arnob216 10 ай бұрын

Are you sure, still cant find it

@ccwoodwerks 10 ай бұрын

I had it for like a week, and now it is gone again :(@@arnob216

@JuanCastaneda 8 ай бұрын

@@arnob216 same... I still dont see this option

@sandraeducation2948 7 ай бұрын

Unfortunately it’s not available in Excel 2021 😢 I would need this sooo much! The more complex formula unfortunately doesn’t help me, as I’m working with a table and the formula doesn’t automatically copy down when the table gets bigger. Any idea?

@wayneedmondson1065 2 жыл бұрын

Thanks Leila! Looking forward to this feature in general release. Thumbs up!!

@LeilaGharani 2 жыл бұрын

Hope it won't take that long for this one 😁

@andrewcharlesmoss 2 жыл бұрын

Finally! Great to see this feature added. Now, let’s wait a few years until we can have a dropdown arrow that permanently shows in a cell (so users know the cell contains a dropdown). 😬

@LeilaGharani 2 жыл бұрын

Not so fast, Andrew 😁

@surendrapandey4158 2 жыл бұрын

@@LeilaGharani @leila: Thank you so much for the news. But in my case, why the SEARCHABLE part is not working. I did a Beta version then I tried doing drop down. Drop down list is working BUT SEARCHABLE is not working. What could be the problem Leila?

@LeilaGharani 2 жыл бұрын

@@surendrapandey4158 There are two levels of insiders. Make sure you're in beta. This is the one they roll out to first. Also they might still be testing with a certain percentage of lucky beta users. So it might not be 100% there yet....

@surendrapandey4158 2 жыл бұрын

@@LeilaGharani yes I am in a Beta channel of office insider. Yet searchable option is not available. Drop down list is working but SEARCHABLE option is not working. Is it sure that they give it away for only certain percentage of Beta users?

@walking_on_by 2 жыл бұрын

@@surendrapandey4158 I'm also on beta and is not available/working for me.

@psykco 2 жыл бұрын

OMG OMG OMG.... It's FINALLY here... Can't wait!!!! Thank you, this made my day. 🤣

@LeilaGharani 2 жыл бұрын

😁

@Raptorbravo341 Жыл бұрын

Finally, it's here! Today I opened my worbook and the dropdown lists looked different, that vivid blue color when you select something was gone, now it's like a greyish blue, and when you type something it shows you only those elements that match.

@shadrackawunyo7786 2 жыл бұрын

Great news. I am soo excited!!!

@drdheerajtumu7419 2 жыл бұрын

Thnq Leila teacher. U saved a lot of my time. U r fabulous

@carlosyaguaratty1873 2 жыл бұрын

Oh wow I can’t wait, thank you so much

@Richardoculus 2 жыл бұрын

Finally…. after working 20 years (or so) with Excel. What a relief! Thank you for the great news Leila!

@martijnfransen9933 2 жыл бұрын

Thanks Leila this is great news!

@vojtechmarecek9071 2 жыл бұрын

Great news, brings a lot of new possibilities for creating user-friendly data input forms, especially in connection to VBA. What I'd like to know is whether you can use a named range or a table column as data validation source? Sure you can extend the range and exclude blanks, but that still doesn't help with moving the source data around (e.g. inserting columns messes up the source range).

@benrogers9092 2 жыл бұрын

=INDIRECT("Table[Column]")

@marshallhosel1247 2 жыл бұрын

Thank you, this was useful.

@ivanmamchych5802 2 жыл бұрын

Thanks for the news, Leila. My opinion: 1. Search of validation list is a very great and cool feature 2. Elimination of blanks... Nothing extraordinary and almost useless. It is a good behaviour to keep lists for validation in Tables. It is easy to refer to them and much more convenient to control them, rather than to refer to a selection with a number of blank cells at the bottom 3. I keep waiting when MS Excel team will add dependent multiple lists as a built-in feature. That would be really fine 4. Another high expectation is a using of "spilled-reference" functions (e.g. SORT, UNIQUE, FILTER) right in Data Validation window, on a List section 5. Right click and general "Search" looks nice and pretty useful

@garynagle3093 2 жыл бұрын

Great video of this new feature. Hope it gets released soon

@gmcguy 2 жыл бұрын

Thank you for all your videos!

@LeilaGharani 2 жыл бұрын

Thank you!

@renataizandra15 2 жыл бұрын

Hello, Leila!! Great video, as always! I just love your voice (and your mouse clicking hahah)!! I have a question about drop-down, please. If I wanted to relate one column to the other with drop-down menu, do I still have to use named ranges and the INDIRECT() formula, or has Excel "fixed" this? Thanks in advance!

@barbora2832 2 жыл бұрын

So cool! Looking forward when it comes out of beta

@1310kumarrakesh 2 жыл бұрын

Great info leila, if feasible pls make a video on pivot table formatting as several time i get irritate doing formatting as it changes with the filter selection (inspite of preserve cell formatting checked). Thankx in advance.

@chasingdogs 2 жыл бұрын

This is fantastic!

@anthonygikuri 2 жыл бұрын

Leila is back again with something to chew!!!! Thank you for keeping us updated

@LeilaGharani 2 жыл бұрын

Trying my best, Anthony!

@anthonygikuri 2 жыл бұрын

@@LeilaGharani Keep going🔥. Any updates on Power BI 😃🤗 I'm wondering how one could sign in Power Bi without having a work email.

@ahmedaboumoslem3017 2 жыл бұрын

So helpful, thanks

@AccroExcel 2 жыл бұрын

The video is good and valuable, thank you

@daliladuf 2 жыл бұрын

Brilliant, as ever!

@eddiemorgan1557 2 жыл бұрын

You don't know how cool you are! You even selected Nutella & Peanut Butter😉 Very much like this new feature. Thank you for presenting it.

@Smarrt78 2 жыл бұрын

Brilliant video as always thanks 🙏

@LeilaGharani 2 жыл бұрын

Glad you enjoyed it

@khanshaz 2 жыл бұрын

Excellent job 👍👍

@OntarioAndrews415 2 жыл бұрын

You are simply great Leila. I think I wrote that before, but it's true no matter how many times I write it. :) So as for which ones I'd pick from your list: Appetizer=Nachos & Chicken and Desert=Peanut Butter Cheesecake

@LeilaGharani 2 жыл бұрын

Good choices :)

@mija1983 2 жыл бұрын

They did it and finally I don't have to do it in VBA. The functionality in the way it works suggests that the methods from the google sheets have been copied. It works well there for a long time, it works as it should be.

@narayank5738 2 жыл бұрын

Hi, I buy one of your e-course at Udemy. So glad that now get more knowledge in excel

@sparcnz 2 жыл бұрын

Great, been wanting this for some time now, now to wait for the general deployment. Q, How can I make the drop-down text larger without increasing the sheet size, I find that the text is too small when I click on the box? Thanks Kevin.

@caryngoldsmith6782 2 жыл бұрын

Love this. Question: is there a way that once something is picked from the list that as an option is taken away from the next person? Only one person can bring an item type. Thank you!

@vishalbharakhada3357 2 жыл бұрын

Awesome, waiting for this feature

@shirleymoreman6725 2 жыл бұрын

This is great! Can't wait to try it out when it's released from beta. Couple of questions.... - Are you still able to use tables and/or named ranges for the lists? - Are you able to add the "Stop" on the data validation so you don't get invalid data after the user presses enter? Being in England I was confused about the "chips" on the choices - US = chips, English = crisps US = fries, English = chips 😂 But pleased to see you spelt "yoghurt" the English way 😍

@wiernixie 2 жыл бұрын

Dang. I am subscribed to the Beta Channel and it hasn't come to me yet@

@chrism9037 2 жыл бұрын

So cool, thanks Leila!

@LeilaGharani 2 жыл бұрын

It was about time too 😊

@W3DRM Жыл бұрын

I was very excited to see this searchable dropdown list feature added to Excel. However, from a user perspective, while I can add to a list by adding a new item to it, it would be great if Excel would automatically add a new list item (or queried the user if they wanted to do so) whenever the user typed-in the new item not currently in the sheet they are working on and not in the referenced list. I'm sure this could be done easily with VBA but an auto-addition feature would be really nice to have.

@hippopotomonstro3713 2 жыл бұрын

Hello, great to see finally this in the Desktop version of Excel. I'd like to use this list system but which allows to enter a value that is not in the list too. Will that work or is there a way to do that?

@kbl437 2 жыл бұрын

KZfaq be listening on my phone calls. I was asking my sister about how to do this and lo and behold it’s in my feed. I guess I can’t be mad right now. 😆 😅

@richardgaynor7396 2 жыл бұрын

You Rock! I love you Leila !

@hockeyfightjudge1 2 жыл бұрын

Leila! Great video - I didn't know this already existed in Excel Online. Are there more features on Excel Online that exist that don't exist in Excel Desktop? Could you do a video on them?? :)

@silviag8091 2 жыл бұрын

excellent content. thx

@dongkey 2 жыл бұрын

This is just so convenient! I hope this goes out of Beta very soon!

@RizwanAli-tp2xk 2 жыл бұрын

Hi your videos are just a moment of surprise everytime I see it.. please if you can make some helpful videos for auditors using Excel for making Thier working papers in excel.. how can auditors utilize the hidden perks in excel and how it can simplify the work .. please your guidance will be highly appreciated!

@helegloselasi5454 2 жыл бұрын

Great work

@chao.m 2 жыл бұрын

Excellent feature excellent video

@lmcoronelPhil 2 жыл бұрын

Thanks Leila.. . hopefully it will be available in beta soon

@wxmyjnsn 2 жыл бұрын

This is fantastic! Now can they fix the not able to change the font size of these and the size of check boxes etc?

@JonLondrezos 2 жыл бұрын

Looks like someone in Microsoft has been listening to our gripes! Good on them.

@LeilaGharani 2 жыл бұрын

Took them long enough 😁

@edj 2 жыл бұрын

These are great!

@ospino1112 2 жыл бұрын

Excellent news!

@ahmetfedai3551 2 жыл бұрын

thanks for sharing ...

@eugeniobottino2846 2 жыл бұрын

That’s great! 😍

@abtotheku 2 жыл бұрын

This is a game changer. 💪

@Xiatrox 2 жыл бұрын

How the hell, did this take so many years to come as a default.... crazy, but aswel super happy its there

@LeilaGharani 2 жыл бұрын

True :)

@drbahsha 2 жыл бұрын

Tq so much.. Great new features.

@vida1719 2 жыл бұрын

Great news for great features!

@LeilaGharani 2 жыл бұрын

👍

@Mixolydian74 2 жыл бұрын

Thanks Leila. Any word on when this feature will be available for desktop version?

@marjovdn 2 жыл бұрын

Thanks for this Leila. Can't wait for this to become available. Can I ask that you also do one on drop down lists that change depending on the value of another cell. Continuing from your example, two lists on the List tab (appetizer and desert). On Sheet1 cell A1 a user selects appetizer or desert and the drop down list changes depending on what they choose. I managed to do this a while ago but it was complex, confusing and it took a long time to get it to work.

@pcwilliambanda8059 2 жыл бұрын

Great works

@hanguyen-my3gg 2 жыл бұрын

excellent , exact my need

@sgk8220 2 жыл бұрын

This is great news!!

@MrSupernova111 2 жыл бұрын

Its nice that Excel finally dropped blanks in data validation cells. I think for older versions you can also select the column (C:C) instead of the exact range and the validation cell will not include blank cells. One issue I have with validation is that somethings I need a validation cell for multiple columns that are part of one table and they can't all be alphabetized. Searching items in a validation cells can be a slight pain when there is no order to the list.

@Saubhagyamaharjan Жыл бұрын

Try Sort once