Unlock Excel Secrets: Magic Search Bar You NEVER Knew About!

Рет қаралды 494,292

Жыл бұрын

#excelformula #exceltips #ExcelSearchBar
Hello Friends,
In this video we have explained, how to create a magic Search Bar in Excel using Excel Shapes, Form Controls and ActiveX Control. We have not used any VBA coding to create it.
Subscribe our new KZfaq Channel:
/ @neotechnavigators
Click below link to download the practice file:
www.pk-anexcelexpert.com/unlo...
Watch the Step by Step tutorial for Project Plan (Gantt Chart) in Excel
• Project Plan(Gantt Cha...
Download our free Excel utility Tool and improve your productivity:
www.pk-anexcelexpert.com/pks-...
See our Excel Products:
www.pk-anexcelexpert.com/prod...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
WWW.PK-AnExcelExpert.com
Facebook:
/ pkanexcelexpert
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Pinterest:
/ pkanexcelexpert
LinkedIn:
/ pk-anexcelexpert
Twitter:
/ priyendra_kumar
Instagram:
/ pkanexcelexpert
Visit our Amazon Store
www.amazon.in/shop/pkanexcele...

Пікірлер: 339

@samprime3180 7 ай бұрын

It is very very nice to sit and watch the amazing things we can do with excel. Big Salute.

@tonyross7550 11 ай бұрын

Very clever. A lot of relatively simple techniques combined to create a complex solution.

@PKAnExcelExpert 11 ай бұрын

Glad you liked it!

@jackcurl2005 11 ай бұрын

Great explanation, clearly demonstrated. In general, perhaps use IFS or SWITCH(✔) rather than nested IF statements-it's cleaner, easier to debug, easier to read, and certainly easier to program. Both functions became available with Office 2016. If you have FILTER, you have these functions (and a slew of other niftiness). An example for those who might wondering: SWITCH($A$1,1,Search_Left,2,Search_Right,3,Search_Anywhere,"No results") The test/result for the value 3 is included to handle invalid data. It's better to specify than to default. Since A1 is an openly editable cell, any value could appear there. We need to take that into account. Never trust users (even oneself) to always do it right. As the saying goes, An idiot-proof system just hasn't been introduced to the correct idiot yet.

@marcjacquemet--fotalch 11 ай бұрын

Wow what a great video. I've learned a lot. I already knew individual techniques - but in this video everything was put into a clever context - and a lot was also new to me. Thanks!🤩

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@finguy8572 11 ай бұрын

This is probably the best Excel video I’ve ever seen! I am now looking for any excuse I can come up with to use it!

@PKAnExcelExpert 11 ай бұрын

Thanks

@silvestrecamposano6317 10 ай бұрын

Thank you very much!.... ❤

@nadermounir8228 Жыл бұрын

Brilliant PK greatly enjoy the video 📹 👍 keep up the good work. We really thank u for your hard work

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@lukasolsovsky7299 11 ай бұрын

Very well done sir !! I´m used to use it indirect and named_ranges but with FILTER formula it has another level ! Thank you for your video. BR from Czech Rep.

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@user-io8it9ke3e 6 ай бұрын

Wow! This is an incredible combination of quite simple elements! I applaud standing! PS I just don't know where to apply it, but it's a very beautiful and elegant solution

@PKAnExcelExpert 6 ай бұрын

Thanks for your valuable feedback

@philipjacob1979 9 ай бұрын

Thank you PK. Excellent. Keep up the good work.

@PKAnExcelExpert 8 ай бұрын

My pleasure

@irfanchauhan313 11 ай бұрын

Great Sir.... We have improved our knowledge by watching your videos and Practice file..... Thank for you Everything..... I really appreciate your contribution for us and all @ FREE OF COST..... God Bless You Sir 🙏

@PKAnExcelExpert 11 ай бұрын

Thanks a lot🙏

@sakists6870 9 ай бұрын

Excellent job! Thank you so much!!!

@PKAnExcelExpert 9 ай бұрын

You're very welcome!

@osoriomatucurane9511 Ай бұрын

Savvy, outstanding! Unmatched or unparalled skill. PK, the Excel Legend

@PKAnExcelExpert Ай бұрын

Thanks for your valuable feedback 🙏

@cesarminiao 11 күн бұрын

You’re a genius!

@debashismandal7945 11 ай бұрын

You just solve my whole problem. Searched this since 6 months ago. Even chat gpt didn't understand my problem. Thank you sir... ❤❤❤

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@Pete128 11 ай бұрын

Simply brilliant. Thank you !!!

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@Masdille 2 ай бұрын

What excel does he using

@mohammedhassan2808 11 ай бұрын

Superb idea and technique, Great work without using any coding, excellent

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@zaydarendse2812 Жыл бұрын

amazing!! Thanks so much for sharing :)

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@markus_aurelius_888 Жыл бұрын

Great vid, fantastic that you use formulas instead of programing, it makes life so much easier. 👍

@PKAnExcelExpert Жыл бұрын

Thanks for your valuable feedback🙏

@bilalsheikh9127 10 ай бұрын

Totally impressive. Stay blessed. Such excellent and elaborated explanation of each step.

@PKAnExcelExpert 10 ай бұрын

Thanks🙏

@SaloniAgraDepot 3 ай бұрын

ZABARDAST SIR.............

@PKAnExcelExpert 3 ай бұрын

Thanks

@grijalvaescobedo158 Жыл бұрын

Gracias maestro por compartir excelentes tips saludos 🇲🇽👍👋👋👋👏

@PKAnExcelExpert 11 ай бұрын

Gracias por ver

@ashusharma9439 Жыл бұрын

This is Amazing Thanks PK.

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@TampaRob84 11 ай бұрын

FYI, for the helper cells in row 1, custom format of ;;; makes the text invisible rather than white so it won't display if the cell color is changed

@kdstudiolab 11 ай бұрын

nice sharing..thank you

@PKAnExcelExpert 11 ай бұрын

Most welcome

@misdin1 Жыл бұрын

Very nice video, very good, incredible trick. Thank you very much 👍

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@theexposer9483 11 ай бұрын

Very well balanced video in terms of content, speed of delivery, movement of screens and clarity of subject. Take my complement for the good work. This is my first video from your channel. A suggestion which would increase your viewership , since it would help many users for their work. That is , if you can create an equivalent sheet in Google sheet for all your work and place grouped together with excel version. I don’t know your expertise level on Google sheets because both these applications run on different platform and underlying language. If you give it a try it would be wonderful gift to your viewers. God bless you.

@PKAnExcelExpert 11 ай бұрын

Thanks for your valuable feedback. I will definitely try to create.

@somenmaji7078 11 ай бұрын

true, this way less complicated. I tried making a same type, which tells where a name falls in a number of sheets in the same workbook. I did that using table and DGET() function.The one you made is very well balanced and very less complected.

@jaikritbhattacharya4341 11 ай бұрын

Brilliant and very catchy... learned something. thanks

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@gospelmoto2833 11 ай бұрын

Nice video! you got a new subscriber here. I like the way you teach - very clear and straightforward.

@PKAnExcelExpert 11 ай бұрын

Thanks for your valuable feedback🙏

@unnikrishnansanthosh Жыл бұрын

class one, thank you for sharing

@PKAnExcelExpert Жыл бұрын

My pleasure!

@ikinursiyanto-dj7500 8 ай бұрын

Amazing..great...thank you very much

@PKAnExcelExpert 8 ай бұрын

Most welcome🙏

@sketchtwenty2 11 ай бұрын

Ingenious method for searching very clearly explained.

@PKAnExcelExpert 11 ай бұрын

Thanks

@sculpture2582 11 ай бұрын

Many Thanks from Germany 👌👍💪

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@user-nl2wd9on7j 11 ай бұрын

YOU'RE GOOD! From Nigeria

@PKAnExcelExpert 11 ай бұрын

Thank you!!

@eyeofthetiger8287 2 ай бұрын

great job perfect lesson Thanks

@PKAnExcelExpert 2 ай бұрын

Most welcome

@jimmytee8913 Жыл бұрын

This is amazing, I have been looking for something like this, we are are creating a mini database for a running club, with this video it going to be a game changer. Thank u ok u so much for that's awesome video

@PKAnExcelExpert 11 ай бұрын

Glad it was helpful!

@jimmytee8913 11 ай бұрын

Can this be done in google sheets?

@freelancing8605 11 ай бұрын

superb hats off sir.... thanks

@PKAnExcelExpert 11 ай бұрын

Thanks for your valuable feedback

@1724joshua 11 ай бұрын

This opens a lot of ideas on how to improve my work. I hope you can make a video on how to do the same function but searching and gathering data from different sheets.

@PKAnExcelExpert 11 ай бұрын

will try to create another video on this.

@mohammadilyas2260 11 ай бұрын

Excellent زبردست

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@lebogangmetsileng8969 5 ай бұрын

wow this was soo informative. Thank you

@PKAnExcelExpert 5 ай бұрын

Most welcome

@chandanmhatre915 Жыл бұрын

Great as always ❤

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@simpabar1066 11 ай бұрын

Amazing video thanks 👍👍👍

@PKAnExcelExpert 11 ай бұрын

Glad you enjoyed it

@muhammadashraf1624 Жыл бұрын

Very informative video. Thanks

@PKAnExcelExpert 11 ай бұрын

Most welcome🙏

@6ugggg Жыл бұрын

useful work.thanks

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@SM-tz1hc 11 ай бұрын

Very beautiful... With respect

@PKAnExcelExpert 11 ай бұрын

Thanks a lot🙏

@WaymondJr Жыл бұрын

This was fantastic ♥️

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@learnverntoz 11 ай бұрын

Amazing.... As usual....👌👌👍👍

@PKAnExcelExpert 11 ай бұрын

Thank you so much 😀

@abdelrhmanmohamed678 Жыл бұрын

You have to mention that The FILTER function is only available in Excel for Microsoft 365 and Excel 2021

@PKAnExcelExpert 11 ай бұрын

Thank you for pointing that out! You are absolutely correct, and I regret missing that vital piece of information in my video tutorial. The FILTER function is indeed only available in Excel for Microsoft 365 and Excel 2021. I appreciate your keen observation, and I'll make sure to include this essential detail in future content. Thanks again for bringing this to my attention!

@johnx2686 11 ай бұрын

I'm still in 2007 😁😁😁

@ab.ak549 11 ай бұрын

@@PKAnExcelExpert Can you add Filter function in Excel 2019 or earlier via coding in VBA? is it possible for even the VBA function that it's performance would be very similar to Filter function?

@nathanielaboagye2259 11 ай бұрын

@@ab.ak549Yeah.. Ask ChatGPT

@jasminvaldez6110 10 ай бұрын

How to save file with activeX in sharepoint ?

@4567snick 11 ай бұрын

Great video, I learned a lot! Any reason why you use named ranges for the data rather than converting the data to a table? I use both, but I would have probably used a table in this case. Thanks for the great lesson!

@PKAnExcelExpert 11 ай бұрын

We can use the table also. Thanks for your valuable feedback.

@somenmaji7078 11 ай бұрын

thxs

@jackcurl2005 11 ай бұрын

Tables are fantastic! I had the same thought about using them.

@zaighamuddinfarooqui1705 11 ай бұрын

Amazing and well explained. Is there a way to build this search bar using legacy commands?

@darwinalbo9491 4 ай бұрын

Thank you friend

@PKAnExcelExpert 4 ай бұрын

Most welcome

@hometv-sr 5 ай бұрын

Wow wow wow. Never knew some of these techniques. Excellent video. Loved it man. Thanks for sharing these. You earned a follower. Have followed you. Keep it up.

@PKAnExcelExpert 5 ай бұрын

Thanks a lot 👍

@querrythis Жыл бұрын

Highly recommend this dynamic search. The best (hands down) I've come across.

@PKAnExcelExpert 11 ай бұрын

Thanks a lot.

@ksumar 6 ай бұрын

Genius Sir... I salute you! I'll save this video. 👍

@PKAnExcelExpert 6 ай бұрын

My pleasure 😊

@md.tofaelahmed6410 11 ай бұрын

💯 Outstanding Boss

@PKAnExcelExpert 11 ай бұрын

Thank you so much 😀

@mahargleopenaws 11 ай бұрын

Fantastic teacher

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@soyoyeoluwafunso4586 11 ай бұрын

This is great very helpful

@PKAnExcelExpert 11 ай бұрын

Glad it was helpful!

@excel168 10 ай бұрын

Nice lesson. Thanks

@PKAnExcelExpert 10 ай бұрын

Most welcome🙏

@Vmeansvijay 11 ай бұрын

Great sir ji...Really appreciative

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@darshanpatil4347 11 ай бұрын

Awesome!!

@PKAnExcelExpert 11 ай бұрын

Thanks!

@rahibsamedov8142 11 ай бұрын

Sir, thanks for the great video and your effor. Could you please greate a dinamic formula like the name range in this video you created but exclude dublicate names.

@RichJB1973 9 ай бұрын

Thank you that’s going to help me a lot

@PKAnExcelExpert 9 ай бұрын

Most welcome🙏

@ProviderKnowledge 7 ай бұрын

Thanks

@PKAnExcelExpert 7 ай бұрын

Welcome

@mpscqa7387 11 ай бұрын

Excellent 👌

@PKAnExcelExpert 11 ай бұрын

Thank you! Cheers!

@info2satish 11 ай бұрын

Awesome pk bro!!

@PKAnExcelExpert 11 ай бұрын

Thanks ✌️

@nadahkhi 10 ай бұрын

Excellent work

@PKAnExcelExpert 10 ай бұрын

Thanks🙏

@2000sunsunny Жыл бұрын

Thank you for video. I wıll definetely use name manager 🙏

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@Edison_mm 10 ай бұрын

So great! Thank's ¿Can I use Choose function ?

@paca-san 2 ай бұрын

Thank you for this, very informative and helpful. I have a question; would this be usable if for example I have multiple sheets of data?

@gksnaimisha 8 ай бұрын

😮 superb demo

@PKAnExcelExpert 8 ай бұрын

Thanks

@buahadkhan4795 5 ай бұрын

Thanks alot

@PKAnExcelExpert 5 ай бұрын

Most welcome

@pankulbaijal7542 11 ай бұрын

Great Technique sir and very impressive, just a small question that will this this work similarly good when there Merge cells available in the data. Please answer this question. Thanks Happy Learning😊

@mdkamaluddin3539 3 ай бұрын

Brilliant

@PKAnExcelExpert 3 ай бұрын

Thanks

@q8paramedic 10 ай бұрын

Very good work I learned from you new thing ❤

@PKAnExcelExpert 10 ай бұрын

Thanks🙏

@rajsahu1029 Жыл бұрын

Doing very well. Wery interesting, useful and beautiful. I have seen several persons videos but no one is so beautyful work representation of figures/Data and chars I have seen from others. Thanks very much to you.

@PKAnExcelExpert Жыл бұрын

Most welcome🙏🙏🙏

@uktammadraimov 11 ай бұрын

Thank you Bro

@PKAnExcelExpert 11 ай бұрын

Welcome

@sunnindawg 3 ай бұрын

Nice! No more need for filters.

@PKAnExcelExpert 3 ай бұрын

Thanks

@mohamedlakel7084 Жыл бұрын

wow greatful

@PKAnExcelExpert Жыл бұрын

Thanks a lot🙏

@niteshjaiswal2754 6 ай бұрын

Excellent 👌🏻❤

@PKAnExcelExpert 6 ай бұрын

Thanks

@hafizahrahmat7421 11 ай бұрын

Thank you for a very clear tutorial. May i know how to search the specific data within the type. Eg: serch name for under the same supervisor.

@akavaramvallabhai4950 11 ай бұрын

EXCELLENT

@PKAnExcelExpert 11 ай бұрын

Many thanks!

@adeshraka6668 10 ай бұрын

Very well explain in this vid..Sirji

@PKAnExcelExpert 10 ай бұрын

Thanks🙏

@adeshraka6668 10 ай бұрын

@@PKAnExcelExpert how to search eg..Type ..A B C and then Result ADi Bike Care...(Left Mid Right name search)

@pandharinathjoshi6565 Жыл бұрын

Sir reallu ultimate sir

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@drewsoffice 11 ай бұрын

Hey PK I agree with previous comment...converting to a table might have been easier - great video nonetheless!

@PKAnExcelExpert 11 ай бұрын

Good point! Thanks🙏

@rajkumararumugam7449 Жыл бұрын

Hi Pk. Could you pls post video for power bi to create a parameter in report page to pass the date value. Instead of filter the value or data it should highlight Eg. I have the data from jan do dec and I pass the value in the parameter like Aug and then the value should highlight in the column wise

@zbatevp-vlogs610 11 ай бұрын

I'm interested to know how to do this with multiple search category. Not sure if I can do it but i will try.. shortcut would probably be a concatenate of the 2 or more columns.

@ahmedmallook4128 Ай бұрын

Thanks for the tutorial, PK. I have a question: How can I change the font size of the selection buttons? Search from Left, etc?

@jothidarsubha.kalaichelvan8068 Жыл бұрын

Wow...what a smart formulated illustrations...❤

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@asadrauf5902 11 ай бұрын

Great work. I've learned a new way of searching data. I've created a worksheet same as you did in excel 2021. All named ranges are working well except the last one "Search_Data". When I put this in first cell below "Name", it does not work as the formula itself does. Rest of the cells do not fill automatically. It fetches the first Name and that's it. I've downloaded the practice file. Facing the same issue there too. Any idea why it's not working?

@user-je1tc6dq2s 4 ай бұрын

=FILTER(Whole_range;Name=search_text;"") u can use this

@user-hd6fq5qb3r 8 ай бұрын

Wow! This video/function is very impressive.. Just a question.. If this function is available only on 2021 office version, can the file work on a 2019 office version..? Thank you so much and more power..

@naresh7615 11 ай бұрын

My god..Crazy stuff

@PKAnExcelExpert 11 ай бұрын

Thanks🙏

@user-dr1hs3hq4w 11 ай бұрын

hi nice tutorial. do u have an option for excel 2013? thank you

@dangerlu7635 7 ай бұрын

Great video to learn, by the way ,how to make the search icon and shapes unselect in excel?~

@vacilando86 Жыл бұрын

That’s amazing 👏

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@alterchannel2501 Жыл бұрын

Good job! The best formula based search bar👍👏

@PKAnExcelExpert Жыл бұрын

Thanks🙏

@klebersonmoreira1821 Жыл бұрын

Sensacional 👏👏👏👏 Mestre, por favor, ensina como Habilitar e Desabilitar os Botões da Ribbon Personalizada conforme critério. 🙏🙏🙏🙏

@PKAnExcelExpert 11 ай бұрын

irá criar um vídeo sobre este tema em breve.

@klebersonmoreira1821 11 ай бұрын

@@PKAnExcelExpert 👏👏👏👏

@JohnKnorr 7 ай бұрын

Great tutorial. Is it possible to protect the worksheet but still allow the search to work? I want to share a searchable list, but don't want anyone breaking the file.

@soniccheese01 Жыл бұрын

Nice one. Here is another example formula that uses a Table called MyData and searching a column called Company: =SWITCH(A2,1,FILTER(MyData,LEFT(MyData[COMPANY],LEN(SearchText))=SearchText),2,FILTER(MyData,RIGHT(MyData[COMPANY],LEN(SearchText))=SearchText),3,FILTER(MyData,ISNUMBER(SEARCH(SearchText,MyData[COMPANY])))) No need to use additional Range Names, or particularly the INDIRECT function Any thoughts?

@PKAnExcelExpert 11 ай бұрын

Certainly! Your comment is insightful and adds valuable information to the discussion. I appreciate you sharing this alternative method using the SWITCH function and Table. It's always great to see different approaches to a problem. Thanks for contributing to our community!

@mohamedabassi7218 11 ай бұрын

Hi and thanks for this video it was very interesting but can you explain to me how by one entry in the first cell all the other data was transferred and thanks

@pankulbaijal7542 11 ай бұрын

Sir i cross checked that while merging cells it wouldn't work giving spill error so any possibility then please let me know because I have a data which has merge cells. 🙏🙏