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

#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 CZcams 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...

[@tonyross7550]

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

[@PKAnExcelExpert]

Glad you liked it!

[@samprime3180]

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

[@marcjacquemet--fotalch]

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]

Most welcome🙏

[@osoriomatucurane9511]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback 🙏

[@jackcurl2005]

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.

[@finguy8572]

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]

Thanks

[@silvestrecamposano6317]

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🙏

[@sakists6870]

Excellent job! Thank you so much!!!

[@PKAnExcelExpert]

You're very welcome!

[@philipjacob1979]

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

[@PKAnExcelExpert]

My pleasure

[@grijalvaescobedo158]

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

[@PKAnExcelExpert]

Gracias por ver

[@debashismandal7945]

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

[@PKAnExcelExpert]

Most welcome🙏

[@zaydarendse2812]

amazing!! Thanks so much for sharing :)

[@PKAnExcelExpert]

Most welcome🙏

[@user-io8it9ke3e]

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]

Thanks for your valuable feedback

[@kdstudiolab]

nice sharing..thank you

[@PKAnExcelExpert]

Most welcome

[@ashusharma9439]

This is Amazing
Thanks PK.

[@PKAnExcelExpert]

Most welcome🙏

[@Pete128]

Simply brilliant. Thank you !!!

[@PKAnExcelExpert]

Most welcome🙏

[@Masdille]

What excel does he using

[@gospelmoto2833]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@lukasolsovsky7299]

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]

Most welcome🙏

[@jaikritbhattacharya4341]

Brilliant and very catchy... learned something. thanks

[@PKAnExcelExpert]

Thanks🙏

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

[@unnikrishnansanthosh]

class one, thank you for sharing

[@PKAnExcelExpert]

My pleasure!

[@bilalsheikh9127]

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

[@PKAnExcelExpert]

Thanks🙏

[@mohammedhassan2808]

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

[@PKAnExcelExpert]

Thanks🙏

[@sculpture2582]

Many Thanks from Germany 👌👍💪

[@PKAnExcelExpert]

Most welcome🙏

[@misdin1]

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

[@PKAnExcelExpert]

Most welcome🙏

[@chandanmhatre915]

Great as always ❤

[@PKAnExcelExpert]

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🙏🙏🙏

[@darshanpatil4347]

Awesome!!

[@PKAnExcelExpert]

Thanks!

[@SaloniAgraDepot]

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

[@PKAnExcelExpert]

Thanks

[@ikinursiyanto-dj7500]

Amazing..great...thank you very much

[@PKAnExcelExpert]

Most welcome🙏

[@querrythis]

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

[@PKAnExcelExpert]

Thanks a lot.

[@user-nl2wd9on7j]

YOU'RE GOOD! From Nigeria

[@PKAnExcelExpert]

Thank you!!

[@TampaRob84]

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

[@irfanchauhan313]

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]

Thanks a lot🙏

[@abdelrhmanmohamed678]

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

[@PKAnExcelExpert]

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]

I'm still in 2007 😁😁😁

[@ab.ak549]

@@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]

@@ab.ak549Yeah.. Ask ChatGPT

[@jasminvaldez6110]

How to save file with activeX in sharepoint ?

[@WaymondJr]

This was fantastic ♥️

[@PKAnExcelExpert]

Thanks🙏

[@info2satish]

Awesome pk bro!!

[@PKAnExcelExpert]

Thanks ✌️

[@simpabar1066]

Amazing video thanks 👍👍👍

[@PKAnExcelExpert]

Glad you enjoyed it

[@sketchtwenty2]

Ingenious method for searching very clearly explained.

[@PKAnExcelExpert]

Thanks

[@hometv-sr]

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]

Thanks a lot 👍

[@learnverntoz]

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

[@PKAnExcelExpert]

Thank you so much 😀

[@6ugggg]

useful work.thanks

[@PKAnExcelExpert]

Most welcome🙏

[@eyeofthetiger8287]

great job perfect lesson Thanks

[@PKAnExcelExpert]

Most welcome

[@muhammadashraf1624]

Very informative video. Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@lebogangmetsileng8969]

wow this was soo informative. Thank you

[@PKAnExcelExpert]

Most welcome

[@md.tofaelahmed6410]

💯 Outstanding Boss

[@PKAnExcelExpert]

Thank you so much 😀

[@SM-tz1hc]

Very beautiful... With respect

[@PKAnExcelExpert]

Thanks a lot🙏

[@RichJB1973]

Thank you that’s going to help me a lot

[@PKAnExcelExpert]

Most welcome🙏

[@freelancing8605]

superb hats off sir.... thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@soyoyeoluwafunso4586]

This is great very helpful

[@PKAnExcelExpert]

Glad it was helpful!

[@1724joshua]

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]

will try to create another video on this.

[@mahargleopenaws]

Fantastic teacher

[@PKAnExcelExpert]

Thanks🙏

[@mpscqa7387]

Excellent 👌

[@PKAnExcelExpert]

Thank you! Cheers!

[@darwinalbo9491]

Thank you friend

[@PKAnExcelExpert]

Most welcome

[@nadahkhi]

Excellent work

[@PKAnExcelExpert]

Thanks🙏

[@Vmeansvijay]

Great sir ji...Really appreciative

[@PKAnExcelExpert]

Thanks🙏

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

Glad it was helpful!

[@jimmytee8913]

Can this be done in google sheets?

[@excel168]

Nice lesson. Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@hassanalianees5530]

Brilliant

[@PKAnExcelExpert]

Thanks 👍

[@akavaramvallabhai4950]

EXCELLENT

[@PKAnExcelExpert]

Many thanks!

[@q8paramedic]

Very good work I learned from you new thing ❤

[@PKAnExcelExpert]

Thanks🙏

[@ksumar]

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

[@PKAnExcelExpert]

My pleasure 😊

[@mohamedlakel7084]

wow greatful

[@PKAnExcelExpert]

Thanks a lot🙏

[@gksnaimisha]

😮 superb demo

[@PKAnExcelExpert]

Thanks

[@sunnindawg]

Nice! No more need for filters.

[@PKAnExcelExpert]

Thanks

[@2000sunsunny]

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

[@PKAnExcelExpert]

Most welcome🙏

[@Edison_mm]

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

[@jothidarsubha.kalaichelvan8068]

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

[@PKAnExcelExpert]

Thanks🙏

[@theexposer9483]

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]

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

[@somenmaji7078]

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.

[@ProviderKnowledge]

Thanks

[@PKAnExcelExpert]

Welcome

[@mohammadilyas2260]

Excellent زبردست

[@PKAnExcelExpert]

Thanks🙏

[@niteshjaiswal2754]

Excellent 👌🏻❤

[@PKAnExcelExpert]

Thanks

[@buahadkhan4795]

Thanks alot

[@PKAnExcelExpert]

Most welcome

[@uktammadraimov]

Thank you Bro

[@PKAnExcelExpert]

Welcome

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

[@rahibsamedov8142]

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.

[@4567snick]

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]

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

[@somenmaji7078]

thxs

[@jackcurl2005]

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

[@zaighamuddinfarooqui1705]

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

[@naresh7615]

My god..Crazy stuff

[@PKAnExcelExpert]

Thanks🙏

[@pandharinathjoshi6565]

Sir reallu ultimate sir

[@PKAnExcelExpert]

Thanks🙏

[@paca-san]

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?

[@adeshraka6668]

Very well explain in this vid..Sirji

[@PKAnExcelExpert]

Thanks🙏

[@adeshraka6668]

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

[@alterchannel2501]

Good job! The best formula based search bar👍👏

[@PKAnExcelExpert]

Thanks🙏

[@pankulbaijal7542]

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😊

[@vacilando86]

That’s amazing 👏

[@PKAnExcelExpert]

Thanks🙏

[@drewsoffice]

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

[@PKAnExcelExpert]

Good point! Thanks🙏

[@harshdas7009]

Wow.......❤❤❤❤

[@PKAnExcelExpert]

Thanks🙏

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

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!

[@user-hd6fq5qb3r]

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

[@jiya2907]

👍 9:11

[@PKAnExcelExpert]

Thanks🙏

[@hafizahrahmat7421]

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.

[@dangerlu7635]

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

[@RhynoTEK]

Javanese Vazquez estaba viral ahora pero cuando la gente que no lo conocen todavía lo busquen en las redes, especialmente las apariciones con Chente, van a darle un apagón brutal que no podrá salir de ese esa.

[@naveenkumarm1229]

Great video, I learned a lot! and I created my wone sheet thanks a lot.
1)I have question when search table is blank all data is displayed how hide that?,
2)if i type some name in search tab and select anywhere option only that data should reflect what will be the solution pls help me with this?

[@tidetootop]

First great thanks ! But any fomular for both capital letter and normal letter Bro?

[@klebersonmoreira1821]

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

[@PKAnExcelExpert]

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

[@klebersonmoreira1821]

@@PKAnExcelExpert 👏👏👏👏

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

[@najeebahmad2897]

Nive video

[@PKAnExcelExpert]

Thanks🙏

[@zbatevp-vlogs610]

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.