🔎 How to Create Searchable Drop Down List in Excel

Рет қаралды 356,605

Күн бұрын

🔥 Learn Excel in just 2 hours: kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how to build a searchable drop down list in Microsoft Excel. First, we look at how to create a searchable drop down list on Excel for the web, which is very easy. All you need to do is to create a drop down list and it's searchable by default. Desktop Excel is a little more involved and requires three functions, including search, isnumber, and filter. First, we start by creating a drop down list that works on one cell. Then we progress and build a drop down list that works across multiple cells. By the end, you'll master creating searchable drop down lists on both Excel on the web and Excel desktop.
👋 Additional resources
- Sample spreadsheet to follow along: 1drv.ms/x/s!AmxrofZZlZ-whMg2w...
- Excel on the web: www.office.com
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
0:45 Create searchable drop down list in Excel on the web
2:53 Create basic drop down list in Excel desktop - no search
4:27 Create searchable drop down list in Excel desktop - one cell
11:28 Create searchable drop down list in Excel desktop - multiple cells
17:30 Best practices
18:09 Wrap up
📃 Watch related playlists
- Playlist with all my videos on Excel: • ❎ How to use Excel
🚩 Connect with me on social
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
- TikTok: / kevinstratvert
🔔 Subscribe to my KZfaq channel
kzfaq.info?...
🎬 Want to watch again? Navigate back to my KZfaq channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert

Пікірлер: 271

@KevinStratvert 2 жыл бұрын

🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com ⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: kzfaq.info/sun/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY

@waydgaming 2 жыл бұрын

By far, the best example of a searchable drop down! Everyone I have seen so far does just one cell, THIS is what most probably needed! Thank you

@Sonia-zq9ek 2 жыл бұрын

I love the way you kick start your videos with “To-day”… 😄 Thanks so much for all these useful lessons!!🙏🏻

@DarkDudesAI 2 жыл бұрын

When you said "Spoiler Alert" that's the moment you nailed the pin down. You've totally upped your game with the thumbnails, the audio the video it's just so much improvement from the past year! Love the way you listen to small people's advice! I would just be grateful for a cookie though!

@damilolaadeogun6507 Жыл бұрын

I've been searching for a very simple follow along tutorial for over an hour now... Glad I found your video.. Thank you for making this so simple 👏👏👏

@AvanishKesharwani 2 жыл бұрын

Thanks for this video Kevin, I have followed the steps from the spreadsheet you have given. Now I learnt how to insert Searchable Drop-Down List in MS-Excel.

@gvinodnair 2 жыл бұрын

Kevin, it's a bit complex for a beginner, but very innovative, advanced and practical approach it is I should say. Thanks!

@renatajesus6769 2 жыл бұрын

Thank you. I already knew all of it except for the transpose. That is amazing. I just didn't know about transpose. Thanks again.

@iviegonzales 2 жыл бұрын

I’ve been looking for something like this for a while now. Thank you!

@swastikszn1781 2 жыл бұрын

I have a Excel class now and I needed this video thanks for uploading this video Kevin!

@saikaushikkothapalli4059 2 жыл бұрын

Been searching for the multiple rows with sreachble dropdows for over 3 montnhs now. This is great! Than you so much

@swastikszn1781 2 жыл бұрын

You are my best online teacher, Kevin Your videos help me a lot Thank you!

@zapchickenhpl3537 2 жыл бұрын

Finally, I found video which really help me. I stuck in spills error many days. this is very clear, very useful for my over hundred lists of raw materials for kitchen. Thank you so much Kevin.

@wncjan 2 жыл бұрын

Great video. The only drawback with the solution is, that you can't type while pull-down is open, and see the irrelevant options disappear 😁

@elisirs Жыл бұрын

You don’t even know how much you helped me, I was going nuts because I had to apply the formula on many cells… This was really really helpful, thank you very much. 😭❤️

@SBFay01 2 жыл бұрын

Superb! Thanks for addressing the desktop solution. I do a lot of work for my Volunteer Fire Dept., but we are rural with spotty internet (so online solutions seldom work for us). This will help me develop some Excel data entry forms for inventory, equipment checks, vehicle maintenance, and incident reporting.

@SavageGothamChess 2 жыл бұрын

Kevin should now give us some cookies, he is not just a KZfaqr he is a cook in Seattle. 🌎

@KevinStratvert 2 жыл бұрын

Looking into starting up some cookie sales for the Kevin Cookie Company with a commercial kitchen. Stay tuned! 🤣

@MadCerCells 2 жыл бұрын

@@KevinStratvert Hey man you should make a top of best phone emulators for pc ,also great videos

@makrieger1968 Жыл бұрын

@@KevinStratvert Kevin, I love your videos about Drop Down Lists...Do you know of a way to include an "alias", "AKA", or "keyword" into the search? For instance, what if you wanted people to find Vegan cookies or Peanut free cookies but didn't want to include those words in the cookie name? THANK YOU!

@bhanuprakashmacha3426 2 жыл бұрын

Awesome techniques you are teaching us.. appreciate your efforts.. thank you.. 🙏

@albou1 2 жыл бұрын

Another fantastic training video! TY!!👍

@CPIVCR Жыл бұрын

thank you. I've been searching for this for a long time. great job presenting the info in a simple and understandable format.

@sharvdhume 2 жыл бұрын

@Kevin Stratvert You are a very good teacher 👍

@anupamchoudhuri 10 ай бұрын

Kevin saved my day, this is the best video I found for a searchable drop down list

@leahguevara7658 2 жыл бұрын

Love the video and how you simplify things

@JIMFX9 2 жыл бұрын

thank you kevin for this video i was looking for this thing on how to create excel searchable drop down list it has really helped me thanks kevin

@kentongonzales1315 2 жыл бұрын

David you are a blessing

@chrisdekeyser7536 Жыл бұрын

That helped me a lot to create some more comprehensive things in Excel. Thank you!

@GoGo2Go2B 2 жыл бұрын

I love this! I think I love you! You explained sth so easily in one video that I've been trying to find out in five separate videos.

@angusred1337 Жыл бұрын

I just spent 2 hours on this video...and...well....it solved my problem! Thank you!

@kuuuyajim 2 жыл бұрын

Love it! ❤️ Hopefully Microsoft is able implement the searchable drop-down on desktop app soon so we no longer need to do this workaround and so it would be easier as well for those who are not Office 365 subscribers.

@charlesxix 2 жыл бұрын

I wouldn't hold your breath.

@ExcelwithJT 2 жыл бұрын

@@charlesxix lp

@trinawidders3535 2 жыл бұрын

Seems like I'm being punished with this long winded complicated way just because I pay for the subscription????? Makes no sense!

@antonisp20 2 жыл бұрын

+2 points for you Kevin. Watched similar video and lacks web solution and multiple row solution which I needed. Thank you very much!

@Krimm664 2 жыл бұрын

Loving the recent excel videos!

@adnanabbasi9745 2 жыл бұрын

Superb, I was just trying different options and you just solved with Transpose! --- Just wonderful.

@charlesrodriguez2176 2 жыл бұрын

As always, brilliantly delivered Kevin!

@KevinStratvert 2 жыл бұрын

Thank you Charles!

@foodietheprincess5766 Жыл бұрын

Thank you so much! This was great and you made it look so easy!

@giestone Жыл бұрын

Superb, You help solve my problem especially with "transpose" fiture for multiple rows. Thanks!

@DarkDudesAI 2 жыл бұрын

I've been lately seeing that you are working on your cookies! It was first back then an example and now it's real! We asked you delivered! And yes, when I will order a cookie as soon as possible, just can't wait to taste!!!

@michaelb1961 Жыл бұрын

Thank you so much!!!! You have saved me from a massive headache of a problem I was having.

@josepena3516 2 жыл бұрын

Excellent video, thank you Kevin

@ashokkumarp7481 2 жыл бұрын

Thank you. Appreciate your tireless effort.

@callililie4 2 жыл бұрын

Thank you so much Kevin!!

@pierrickdefossez3157 2 жыл бұрын

Thanks for the trick, Kevin, very useful. I actually didn't know that excel online was able to filter automatically. I use to set another trick though, combining OFFSET, MATCH and COUNTIF. Basically, if the table of cookies is named "cookies", and you are typing in B6, then the formula of the dropdown list can be set as : =OFFSET(cookies,MATCH(B6&"*",cookies,0)-1,,COUNTIF(cookies,B6&"*"))

@Sparqcorporatetraining 11 ай бұрын

I tried renaming the table, but it wouldn't accept your formula because that name is already in use. I then tried to change your formula above to match the correct table name 'cookiestypes' (yes, that's exactly how it's spelled). Still couldn't get it to work. I then tried to use your formula in the Data Validation - List dialog box. Still no luck. Would LOVE clarification because I couldn't make sense of the TRANSPOSE option and feel like your formula would be simpler. Thanks in advance!

@markus_aurelius_888 2 жыл бұрын

Great explanation, step by step logic 👍

@mohamedbouazzaoui7254 2 жыл бұрын

What do you think about a giveaway of cookies? 😂😂😂 We should create the best cookie order drop down lists, and Kevin will decide which one is the best 😂😂😂 Best Excel tutorial! Thank you so much for help💪

@DexterOG41 2 жыл бұрын

Hii bro,Love from india❤

@michellegage7145 Жыл бұрын

amazing demo, thanks so much for sharing!

@vivacious2349 Жыл бұрын

best excel teacher in the world

@kelvinlee8754 2 жыл бұрын

I've learnt 3 more formulas and combine in 1, thank you

@trossignol11 17 күн бұрын

That was awesome. Thank you

@allancocamas8385 2 жыл бұрын

Very helpful. Thank you very much.

@monicardoso724 2 жыл бұрын

Thank you so much! This was awesome 👏🏻

@steveshutt1881 Жыл бұрын

Great idea, thanks. If you wanted the price or other date to match the user selection would you use textjoin or is there a better option ?

@chandrusunil 2 жыл бұрын

Hi kevin , instead of going for a transpose and hiding the columns of the validated list, I recommend you could use the function "cell('contents")" as the search criteria for the search function initially (instead of "A6" in you formula) so that you can use the same formula on all rows and search gets modified based on what you type in the cell.

@jasirmanakkamburath9685 2 жыл бұрын

hi sunil, can you please explain how can i apply cell function instead of transpose??

@chandrusunil 2 жыл бұрын

@@jasirmanakkamburath9685 instead of referring to A6, you can type "=cells(contents )"...so change made anywhere will be reflected in the formula, thus we can do away with transpose to hide columns that does not show our required data...

@Sparqcorporatetraining 11 ай бұрын

@@chandrusunil Clarification please? Perhaps provide the complete suggested formula and where it would be entered. Thanks in advance!

@chandrusunil 11 ай бұрын

@@Sparqcorporatetrainingin the search function of the "isnumber" column you can change the cell reference from A6 to =cell(contents)

@vanessariedel9206 Жыл бұрын

For sure I'd buy cookies from Kevin Cookie Company! and Thank you so much for this video. I'm creating a form to help my husband with his job. It will be his Christmas gift. Happy Life Kevin!

@coachfoulds3798 2 жыл бұрын

Great help mate, thank you!

@swastikszn1781 2 жыл бұрын

Thanks for helping me, Kevin

@madhavdhungel6211 2 ай бұрын

Many thanks, Meantime I was unable to see Filter functions with my excel

@evertkleynhans2617 2 жыл бұрын

Thanks, Kevin, excellent training, as usual!! Quick sanity check for me: To "future-proof" the list, you could add some empty lines for the list source. As this is a searchable drop-down, the empty lines should not be an issue as it would have been in a standard drop-down, or am I wrong? - Oops, load-shedding moment, I forgot the table option...

@k52178 Ай бұрын

Thank you so much!!

@katherinefrear 2 жыл бұрын

Thank you. Document finally working. I do have one question though, what do you do to add a row to the data validation drop down list and have the table source (with formulas) update?

@snorlaxissleeping3740 9 ай бұрын

this has been very helpful! thank you

@paolabahos9083 4 ай бұрын

You are amazing, I watch all your videos. They are useful☺

@lizjones8793 2 жыл бұрын

Hi Kevin. Your videos are so helpful! Could you show us how to mirror one sheet to another. It would be a major time saver. Thanks for everything

@Ahmed-jf4py 2 жыл бұрын

Power query might help.

@dummycenter7437 11 ай бұрын

Excellent !!

@amilasilva5393 Жыл бұрын

great video kevin..the best drop down method by far..❤💥💯

@jordantomas6575 10 ай бұрын

Thanks for this Kevin. It's really hard to manage massive data in excel and I need to simplify the monitoring before I get out from the university.

@justmie9781 7 ай бұрын

You are the best bro, thank you so much❤❤may you live a happy life😊

@questionmark9684 Жыл бұрын

Awesome! Thank you!

@SymbianYS 11 ай бұрын

Very asome! Saved me a lot of time and pain!!!!!!!!!!!

@ssali123 Жыл бұрын

huge help, thanks man

@ursulamannix7311 2 жыл бұрын

Kevin, you rock!

@sassy2721 2 жыл бұрын

This is good, thank you so much for sharing =)

@user-us7wb5dh2n 10 ай бұрын

This was great! Thanks!

@zalllon Жыл бұрын

Thank you sir, this is way faster than building a pop-up box with a macro. Do you have save me a ton of time, as I’ve attached this search list to an existing table where the search validation items are the named range. This works perfectly and without macros, which is what I was hoping to find and utilize.

@SamiullahEhsan997 9 ай бұрын

Hi, Kevin. This is Samiullah Ehsan from Pakistan. Your videos are ever helpful. Your How-To videos have helped me with my Excel use. I am currently taking a Data analysis course and lately completed a Business Analysis course. I would humbly request you to make a playlist "Business Analysis and Excel", wherein you teach How-To use Excel as a Business Analyst to quantify the business as a number(defining and evaluating business as a metric, as a KPI) and How-To analyse these metrics as business measurements over time (understanding root cause of changes in metrics, building and analysing dashboards and reports, using planar line chart in excel for analytics, predictive analysis, statistical analysis, regression etc) and other important uses of excel that a business analyst should know. Kindly, do make it. Again, it is a humble plea, kindly, do consider it.

@harolda611 2 жыл бұрын

Great video as always Kevin! Waiting for a Windows 365 video :)

@KevinStratvert 2 жыл бұрын

Coming tomorrow Harry! Thanks for the suggestion!

@arthurlesiv4969 8 ай бұрын

Brilliant ❤

@yflix7670 9 ай бұрын

you nailed it thankyou love from india

@kaaba92 2 жыл бұрын

Thank you 😊 Kevin you're my Official "goto" for answers on Excel. Van you please make a video tutorial on Microsoft SQLite please.

@letusfindtruth 2 жыл бұрын

Very useful and informative 🌼🌼🌼🌼🌼

@rahimovassagh4569 2 жыл бұрын

Thanks Kevin! great video! How can I apply this searchable dropdown to multiple rows? Could you make a video about it?

@nipurnagrawal 2 жыл бұрын

your videos are awesome❤❤👍

@alisoltanpoor Жыл бұрын

Very very good 😊

@swastikszn1781 2 жыл бұрын

Congrats on 835 K subsribers Kevin🎉 1 million soon!

@beautifulsingingbird9180 Жыл бұрын

Thanks a ton! Your video is great!*****

@prof.code-dude2750 2 жыл бұрын

WOW Sir!!!! I am glad to know Someone like you who uses their talent from working at Microsoft to help others master their work!!! This will really help in attendance lists and more. Keep up your hard work. You are the best!!🔥🔥🔥👍👍👍👍🙏🙏🙏🔥🔥🔥🔥🔥

@cenevspeed 2 жыл бұрын

Yes 👍🏼

@nithashajil120 Жыл бұрын

Amazing man , loved it

@maiaallman4635 2 жыл бұрын

Hi Kevin, thank you for all this training that you are providing.

@dimasagil90 Жыл бұрын

Cool tricks!

@behzadahmed1420 Жыл бұрын

Very much use full video

@DinoDelight Жыл бұрын

As always....Amazing

@roronoazoro8343 2 жыл бұрын

It helped me, thanks

@snowwhite2146 2 жыл бұрын

Thanks for making it so simple. Simplicity is the best!

@shvamsinghal1717 Жыл бұрын

Thanks a lot

@evesong3408 Жыл бұрын

Hi Kevin I'm using excel 2019 which doesn't have the filter function, do you have a solution to use alternative function to achieve the same result?

@Darkslide820 Жыл бұрын

All they have to do is add a checkbox offering "Show Valid Results" or something like that right under the checkbox offering an in-cell drop down. You'd think it would've been added like 5 versions ago.

@tonyyong 2 жыл бұрын

Great helpful video on Excel Dropdown list, I have done traditional dropdown list in MS Office 95 & 2010, but since changing to MS 365, these dropdown list seems to disappear after saving files and exiting Excel, even checking the button "Apply changes..." Excel does not seem to save the changes. Maybe I will rebuilt the Excel template from scratch and try on my home Mac Mini using Excel 2019. Anyway thanks for all your great tips.

@ospino1112 2 жыл бұрын

Super useful

@MahyLune 2 жыл бұрын

the cookie jokes were amazing!

@DhanushMunasinghe2008 2 жыл бұрын

Hey Kevin Cool Video, by the way can you create a video on how to use vsdc video editor.

@user-lv3ig5gd6z Жыл бұрын

Hi Kevin, I have one question regarding the multiple order form. Is it possible to have the drop down options on a different sheet? Also is this usable on multiple dependent dropdowns?