🏫 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: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
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!!!
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. 😭❤️
@@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!
Thank you so much! The good thing, I got it working on my desktop excel (Microsoft Office 365) the bad thing, why is all this not auto working? I mean we are in 2023, right? lol. Thanks again, much appreciated
This was a great tutorial. I’m just wondering if there is a way so that the lines below don’t show anything. In other words, if you are ordering two lines, you don’t want more cookies showing in lines below
Thank you so much! this is so helpful! you kinda resolve one of my biggest issues in excel. But what if my customers or products are in separates master sheet? which is the only document that is regularly updated.
Hi kevin, i am just wonderimg if the same method can be done for multiple column search? Hope you notice my comment and thanks in advance! 😁 All the best!
not available for my dektop version of office 2021. workaround - insert a pivot table. add the variable you want as a filter. the filter dropdown list is searchable
Thanks for great job but i could not find filter function in fx. I could not go on I stopped at the min 9:00 in the clip. Can you help to do something for that. My Excell is 2016 edition. Othman from Libya
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💪
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!
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.
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 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...
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.
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!
Hi, Great Video. At 16:59 you type in "butter" and are able to immediately click on the dropdown. I am finding I need to press enter before the search is activated., which is a bit of a pain as I then need to click back into the cell to activate the dropdown. Any idea why?
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&"*"))
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!
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.
Hi Kevin, Nice job, I Like it!! First I'm on Win 10 , Microsoft 365, and my system is fully updated, with I7 Processor, and high-end graphics card, and 16GB!. power user from corporate with a background. I think you have an error in both examples I have rechecked all code 4 times -no errors! In both examples as long as you advance down with your searches, ALL is WELL! But if you try to back up the list with a search it falls, by only finding 1 or staying lock a the last search input. Might waant to take a look? Thanks, keep up the great work!! Rod
Hi Kevin, Nice job, I Like it!! First I'm on Win 10 , Microsoft 365, and my system is fully updated, with I7 Processor, and high-end graphics card, and 16GB!. power user from corporate with a background. I think you have an error in both examples I have rechecked all code 4 times -no errors! In both examples as long as you advance down with your searches, ALL is WELL! But if you try to back up the list with a search it falls, by only finding 1 or staying lock a the last search input. Might waant to take a look? Thanks, keep up the great work!! Rod
An excellent example of why some of us wants to have some private time with a baseball bat and the managers responsible for Excel. Why would it EVER take 2+ years to migrate the searchable data validation onto stable desktop? It's still not in the longer term stable branches.
when creating the drop-down list, when i click source, there isn't any $ sign, it only comes when i press f4, and when i press # and try to remove the $ sign.. it says that my formula evaluates to error. P.S i did make sure that the error alert is not included.. PLEASE HELP ANYONE 😭😭
this doesn't works even in Office 2021. when i tried to work this on office 365 web version it worked. anything which can make this workable on office 2016 and later versions apart from office 365. Also the video was supercool and very helpful ! So thanks for that. but i tried dozens of times without having knowledge that this way it won't work on other office versions.
Hi, coming back to Excel after 20 years...your videos have been great! I'm now using a form to enter data. I got the drop down menus to work fine on the spreadsheet (thankyou), but I can't get them onto the form. Can you help?
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.
Now, I would order from Kevin's cookie company. This is great. I had to watch and rewind and repeat, til I undesrstood. My question now is this, how do I perform a horizontal search to have my other columns added automatically? I have about 5 columns that need automatic filling in.
No I would not Order Cookies from the Cookie Company as there is no Product Data Sheet information... Thanks for the presentation; used this process to deduct a product from a specification from the customer.. I found from 15.00 most useful as the written formula.
I'm clearly missing something. I was able to follow this step=by-step. But in the end, the cell with the drop-down list only shows whatever was the last filtered result! Then when I try to type something different, it doesn't work. What am I missing??
Hi just wondering if anyone - who has tried this solution - can confirm the "search function" will work with excel desktop? ... Understand that creating it must be done online.
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?
k so I did the 19th like and the 8th comment. Keep growing Kevin!!! You have certainly helped me a lot! I actually had a question? I do some gaming on my channel form which I am commenting. But I don't get enough responses on any of my yt videos. What can I do?? Do I switch to a new topic like technology??? Plz help!!!
Hi Kevin, as always that nice tutorial video. Im looking for similar thing like this but i want to get the result in 2 dropdown list, in your case here for example - same cookie type & customer = result how mush quantity. if you know what i mean.. in my table have a same customer but he order 2 dif type of cookie, i wanted to know in specific search result base on this 2 criterial for "quantity".
🏫 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: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
I love the way you kick start your videos with “To-day”… 😄 Thanks so much for all these useful lessons!!🙏🏻
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!!!
I have a Excel class now and I needed this video thanks for uploading this video Kevin!
I just spent 2 hours on this video...and...well....it solved my problem! Thank you!
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. 😭❤️
As always, brilliantly delivered Kevin!
Thank you Charles!
I've learnt 3 more formulas and combine in 1, thank you
Another fantastic training video! TY!!👍
You are the best bro, thank you so much❤❤may you live a happy life😊
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.
Excellent video, thank you Kevin
Kevin, it's a bit complex for a beginner, but very innovative, advanced and practical approach it is I should say. Thanks!
Thank you so much!!
Thanks for helping me, Kevin
Cool tricks!
Very useful and informative 🌼🌼🌼🌼🌼
Thank you 😊 Kevin you're my Official "goto" for answers on Excel. Van you please make a video tutorial on Microsoft SQLite please.
Kevin should now give us some cookies, he is not just a TH-camr he is a cook in Seattle. 🌎
Looking into starting up some cookie sales for the Kevin Cookie Company with a commercial kitchen. Stay tuned! 🤣
@@KevinStratvert Hey man you should make a top of best phone emulators for pc ,also great videos
@@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!
Definitely shall order Cookies from the Kevin Cookie Company!
Thank you so much! The good thing, I got it working on my desktop excel (Microsoft Office 365) the bad thing, why is all this not auto working? I mean we are in 2023, right? lol. Thanks again, much appreciated
Very very good 😊
Very much use full video
Hi Kevin, thank you for all this training that you are providing.
Gud afternoon professor. Wow i m first . Front seat🧐
This was a great tutorial. I’m just wondering if there is a way so that the lines below don’t show anything. In other words, if you are ordering two lines, you don’t want more cookies showing in lines below
Thank you so much! this is so helpful! you kinda resolve one of my biggest issues in excel. But what if my customers or products are in separates master sheet? which is the only document that is regularly updated.
Check out Power Query in Excel as a potential solution: th-cam.com/video/0aeZX1l4JT4/w-d-xo.html
@@KevinStratvert thank you! This may give me an idea. Have a nice day!
Hi kevin, i am just wonderimg if the same method can be done for multiple column search? Hope you notice my comment and thanks in advance! 😁
All the best!
It's 2:40 am that I'm watching this....going downstairs for some Oreo cookies
not available for my dektop version of office 2021. workaround - insert a pivot table. add the variable you want as a filter. the filter dropdown list is searchable
Woah!! You look exactly like the character "Adam Groff" from the Netflix show 'Sex Education'.
Thanks for great job but i could not find filter function in fx. I could not go on I stopped at the min 9:00 in the clip. Can you help to do something for that. My Excell is 2016 edition. Othman from Libya
First comment
The web way is not working. Has something changed?
I have no FILTER function
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?
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 😁
thanks the only thing i don't like on data validation is the dropdown list, the text i gets pretty small and then you have to zoom in.
Hi brother! I would like you to teach how to make website and earn money. I always follow your channel. Great work
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
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💪
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!
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.
I wouldn't hold your breath.
@@charlesxix lp
Seems like I'm being punished with this long winded complicated way just because I pay for the subscription????? Makes no sense!
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.
hi sunil, can you please explain how can i apply cell function instead of transpose??
@@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...
@@chandrusunil Clarification please? Perhaps provide the complete suggested formula and where it would be entered. Thanks in advance!
@@Sparqcorporatetrainingin the search function of the "isnumber" column you can change the cell reference from A6 to =cell(contents)
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.
Hii bro,Love from india❤
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!
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
Thank you. I already knew all of it except for the transpose. That is amazing. I just didn't know about transpose. Thanks again.
Hi, Great Video. At 16:59 you type in "butter" and are able to immediately click on the dropdown. I am finding I need to press enter before the search is activated., which is a bit of a pain as I then need to click back into the cell to activate the dropdown. Any idea why?
I have the same issue. Would love to hear if you find any solution to this?
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&"*"))
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!
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.
Hi Kevin, Nice job, I Like it!! First I'm on Win 10 , Microsoft 365, and my system is fully updated, with I7 Processor, and high-end graphics card, and 16GB!. power user from corporate with a background. I think you have an error in both examples I have rechecked all code 4 times -no errors! In both examples as long as you advance down with your searches, ALL is WELL! But if you try to back up the list with a search it falls, by only finding 1 or staying lock a the last search input. Might waant to take a look? Thanks, keep up the great work!! Rod
Hi Kevin, Nice job, I Like it!! First I'm on Win 10 , Microsoft 365, and my system is fully updated, with I7 Processor, and high-end graphics card, and 16GB!. power user from corporate with a background. I think you have an error in both examples I have rechecked all code 4 times -no errors! In both examples as long as you advance down with your searches, ALL is WELL! But if you try to back up the list with a search it falls, by only finding 1 or staying lock a the last search input. Might waant to take a look? Thanks, keep up the great work!! Rod
An excellent example of why some of us wants to have some private time with a baseball bat and the managers responsible for Excel.
Why would it EVER take 2+ years to migrate the searchable data validation onto stable desktop? It's still not in the longer term stable branches.
👍
when creating the drop-down list, when i click source, there isn't any $ sign, it only comes when i press f4, and when i press # and try to remove the $ sign.. it says that my formula evaluates to error.
P.S i did make sure that the error alert is not included..
PLEASE HELP ANYONE 😭😭
this doesn't works even in Office 2021.
when i tried to work this on office 365 web version it worked.
anything which can make this workable on office 2016 and later versions apart from office 365.
Also the video was supercool and very helpful ! So thanks for that.
but i tried dozens of times without having knowledge that this way it won't work on other office versions.
Hi, coming back to Excel after 20 years...your videos have been great! I'm now using a form to enter data. I got the drop down menus to work fine on the spreadsheet (thankyou), but I can't get them onto the form. Can you help?
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.
Now, I would order from Kevin's cookie company. This is great. I had to watch and rewind and repeat, til I undesrstood. My question now is this, how do I perform a horizontal search to have my other columns added automatically? I have about 5 columns that need automatic filling in.
No I would not Order Cookies from the Cookie Company as there is no Product Data Sheet information...
Thanks for the presentation; used this process to deduct a product from a specification from the customer..
I found from 15.00 most useful as the written formula.
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 👏👏👏
I'm clearly missing something. I was able to follow this step=by-step. But in the end, the cell with the drop-down list only shows whatever was the last filtered result! Then when I try to type something different, it doesn't work. What am I missing??
Do you have other method if my excel don't have FILTER function 😅.. thanks in advance
Kevin ! Can you make a video on "2D Animation"?
Well done :) Thank you.
do you have an idea - why I cannot find numbers like: 2664 asdfasdf?
Hi just wondering if anyone - who has tried this solution - can confirm the "search function" will work with excel desktop? ... Understand that creating it must be done online.
I’ve been looking for something like this for a while now. Thank you!
If you did get some detention then many schools would want you as the computer science teacher😅
But for Kevin's level of teaching, he should be paid $100,000 per day, at least.
@@Ryuzaki2100 Yes, numbers ain't a doubt
Hi Kevin, I don't have FILTER function in my excel, what should do for the last step
Thanks Kevin! great video! How can I apply this searchable dropdown to multiple rows? Could you make a video about it?
I HATE THE DATA VALIDATION LIST IN EXCEL, YOUU CAN'T EVEN SEE IT, YOU HAVE TO ZOOM THE WHOLE PAGE
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?
hi Kevin, I am using excel2019 and filter function is not available there, what can be done?
can anyone tell me if a file created in Microsoft 365 (with functions like filter etc) will work in office 2016.
Been searching for the multiple rows with sreachble dropdows for over 3 montnhs now. This is great! Than you so much
Hello I followed the steps to create a dropdown list but when I type, it doesn't filter the result based on the search. What could it be the problem?
I don't understand what I'm doing wrong but this isn't working. I've watched the tutorial 4 times
Great video as always Kevin! Waiting for a Windows 365 video :)
Coming tomorrow Harry! Thanks for the suggestion!
The problem is when you have multiple data. Excel will not respond properly.
Is there any way to add validation to it ie won't accept just Oat must be something from the list?
Does it work in Excel 365 for Mac? Does not seem so. Am I making a mistake?
How can you automatically search in a list of data based on the result of multiple dropdown lists?
Many thanks, Meantime I was unable to see Filter functions with my excel
Hey Kevin Cool Video, by the way can you create a video on how to use vsdc video editor.
If there is no filter function in the version of Excel I'm using what are the alternative function that I can use?
Would this work for a protected sheet? I don't seem to be able to click down arrow.
Hi Kevin thank for this lesson,
I want learn Excel from scratch to advance....can you please suggest me for that
Look at Excelisfun channel Mike did an amazing and complete job since 2008
@@Anthony_Lecoq thanks dear
I am still waiting for the Kevin Cookie company to open in Cape Town!
this is not an elegant solution. If I have a thousand rows of data, that means I need to create a thousand rows of drop down list.
@Kevin Stratvert You are a very good teacher 👍
what's the advantage of transposing that column to several columns?
Great learning thankyou, but can you slow it down just a little bit..
What is it with Kevin and Cookies?
I'm guessing it's a legit business of his
Congrats on 835 K subsribers Kevin🎉
1 million soon!
k so I did the 19th like and the 8th comment. Keep growing Kevin!!! You have certainly helped me a lot!
I actually had a question? I do some gaming on my channel form which I am commenting. But I don't get enough responses on any of my yt videos. What can I do?? Do I switch to a new topic like technology??? Plz help!!!
Wow! I thought someone would be able to respond to questions? Guess not!
Awesome techniques you are teaching us.. appreciate your efforts.. thank you.. 🙏
Hi Kevin, as always that nice tutorial video. Im looking for similar thing like this but i want to get the result in 2 dropdown list, in your case here for example - same cookie type & customer = result how mush quantity. if you know what i mean.. in my table have a same customer but he order 2 dif type of cookie, i wanted to know in specific search result base on this 2 criterial for "quantity".