Make a Search Bar in Excel to Find Anything!

Build a search box in Excel to find anything in your dataset.
Check out our Excel course!: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: hidden-brook-680.myflodesk.co...
In this video, we'll make a dynamic search bar so you can search for any values in your dataset. We will start creating a search box with the developer button. Then, we'll use the filter formula to look for values in a dataset. Following this, to get a partial match, we'll combine the isnumber and search functions to the filter function. Then we'll add all of the other columns we want to search by. Finally, we'll design the search bar with icons, shapes, and conditional formatting so the matching values stand out.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00​ -​ Search Bar with Exact Match Only
2:55​ - Adding Both Partial & Exact Match
5:36​ - Adding all columns to the Search Bar
6:58​ - Search Bar Design
9:36​ - Bonus Trick!
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

[@KenjiExplains]

Check out our Excel course!: www.careerprinciples.com/courses/excel-for-business-finance

[@NEMOSnowtographer]

Can i get a discount code?

[@MarieLemelin]

@KenjiExplains HELP! I do not have the ability to select "FILTER" in insert a function in the 2016 version. I only have "FILTERXML" available. What can I do to solve this? Any suggestions? :)

[@guardianofthemoon]

You literally taught me more in 5 minutes than any other video on here thank you!!!!

[@AMIRKHAN-bj4ny]

You taught the search filter formula in your video in the most simple way ever. 🎉🎉🎉 I really Appreciate your way of teaching. Your video helped me a lot in my projects. Thank you so much.

[@patriciaramirez6150]

This a valuable tutorial for my work!! Thank you so much...

[@Learning_to_Excel]

Really cool video Kenji! Always thought there were so many cool opportunities with the Partial Match function and this may be the best one!

[@Aamir_Updates]

I am from Pakistan and Gas Turbine Engineer, I learnt a lot after watching your videos and now I can make spreadsheet and other calculators for complex calculations. Thanks.

[@tomletcher5127]

Thanks Kenji!! made my life a whole lot easier!! Great content, seamlessly explained ... Legend :)

[@NodictatorsinKZ]

This is awesome! Loved it and use it for my projects.

[@CheatSheets]

Love this idea! Well explained!

[@margaretfarquhar-xb1nk]

That was extremely helpful and well explained, thank you

[@benhermann3038]

Thanks heaps for this solution, it is brilliant! I'd like to offer a solution to the conditional formatting in case the search box is empty: Add another condition that checks if the linked cell is empty. Place it above the one you have introduced. If that condition is TRUE then remove formatting and stop processing more rules. And in general: Your teaching is wonderful! Clear voice and speaking, clear demonstration, not wasting time by fast forwarding during non-essential formatting of boxes etc. Very well done, thank you!

[@user-xl5nd2hj2j]

This is the most useful Function i have seen for EXCEL. Thank you so much for this information

[@naingtun6620]

That’s so great that I will try it.Thank you very much Sir.

[@lovelove4ever]

🎉 thank you for sharing this kind of tutorial. It really helps me to learn Excel formulas. You explain it well.

[@luisllontopbarahona3188]

Kenji... I am learning a lot with you with those cool Excel features... Thank you for sharing the knowledge...

[@khato9720]

These are very valuable skills, please continue to share so everyone can learn from you

[@zzota]

Really neat trick. You could also wrap the whole FILTER function in an IF: IF(H2="","" FILTER....). Then you'd get a blank list if you haven't entered a search parameter.

[@terminator3697]

I followed Ken's instructions from this video and it does not work for me because I have multiple sheets, from Jan-Dec, when I changed table 2 to Jan: Dec, I kept getting errors in my format, little help would be appreciated.

[@kennetharcalaz4843]

I tried this but it diddnt work. Is that thw whole formula needed?

[@dreddph]

Does not work.

[@kenikthyan]

@@kennetharcalaz4843 What @zzota means is that you need to add an IF function before to reference a NULL. The formula is missing a comma unfortunately but @zzota's idea is brilliant (thanks @zzota). Corrected formula: =IF($H$2="", "", FILTER(..............))

[@judithbredy5985]

This is so helpful and I love how Kenji explains everything.

[@KenjiExplains]

Thank you!

[@andreausa123]

Great! it is what i needed. Thank you Kenji very much !

[@Shng1968]

Thank you so much for a helpful video and detailed explanation.

[@Technical_Things123]

Last few days, I was trying to make s search box with VBA coding, but it is very difficult, but your trick just is outstanding, thanku so much sir ❤

[@dougmphilly]

so good that i subscribed. i love how simple this is.

[@RealTakunia]

Bonus trick: Ctrl+F :) Great video, thanks.

[@syedikramullah1114]

Beautiful explained ❤❤

[@arshalmurmu]

Thanks kenji bro for awesome trick to create search bar simple and easy

[@80andromeda08]

Amazing .. You are the ONE "EXCEL MASTER" 》》 THANKS 👏🏼👌🏼👍🏼

[@thedeeb123]

You just made my job 10x easier, thanks!! Subscribed

[@HamdanYouTuber]

Thanks for the explanation

[@naghamsiman6603]

Thank you, very helpful

[@HaroonKhan-gp4vj]

thank you! best wishes from Pakistan

[@NandoGGWP]

This is a super relevant content! Thank you! 🎉

[@KenjiExplains]

Glad it was helpful!

[@MichaelBrown-lw9kz]

This is great. I am going to use this in an upcoming assignment I have.

[@kovilipradeep]

Its wonderful...... I applied in my file👍👍👍👍👍👍👍

[@mozartjohann6886]

Thank you for this amazing content

[@sanjayparmar9988]

Superb ❤ . Thank You So Much 💕

[@Navinselka]

THANK U SO MUCH, ITS WORKED

[@tundesmn1]

Quite insightful

[@vanc5501]

Thank you for this

[@omarnader776]

Excellent 💯

[@HikmahSarjana]

Thank You Master 😊

[@grendbelema]

thanks alot Kenji

[@lauro7861]

Thanks a lot man !

[@dalimiso]

Such a powerful tool/method and clearly explained. Can you use it to pool data from more than one Table or Sheets?

[@unnikrishnansanthosh]

classic, thanks for sharing

[@CavanHaganInvesting]

For anyone working with a large data table, the way I implemented this was I turned the formula calculation option to manual, and used the search bar icon as a macro button that will calculate when clicked. That way it doesn't try and filter the massive table every time a letter is typed.

[@Jyotika4]

Can you make a video on this and paste the link here

[@CavanHaganInvesting]

@@Jyotika4 go to the formula tab, and change calculation options to manual. Then record a macro where you click on calculate sheet. Then insert the search icon, right click, press assign macro and choose the macro you just made. If there's other content on your spreadsheet, you will need to add the macro button to those also so they can calculate since the whole document is manual calculation

[@arbazahmad7177]

Excellent....🎉

[@hassanjatta4257]

Awesome 👌

[@SirajKhan-wr5bh]

Very informative

[@camlex6310]

Wowww what a cool function!!

[@KenjiExplains]

It really is!

[@magdahassib694]

Than you so much 😍

[@RKSiNgH-mx9cm]

Thanks it helps me to create me own search bar

[@CAKimberlyLewis]

👏Bravo! Brilliant!

[@azreenazaharil5217]

thanks a bunch!!!!!

[@abdulhamidalhaddadi2255]

Thank You sir

[@5ssandraa5]

I think this is wonderful! Thank you :) Would it be possible to implement a filter that could search for values within the text, even if they're not at the beginning? It would be really helpful if the filter could locate values in the middle of chapters too.

[@khalidmounir3475]

thanks, we d love if u make it with previous versions that doesn't support the formula FILTER

[@varuncd1096]

Thank you sir ji!

[@SARthushara]

super!

[@mohamedhashem6927]

That's great 👏

[@saravanansomasundaram4598]

Thank you 🙏

[@KenjiExplains]

You’re welcome 😊

[@leo2hari]

First comment from India ❤🎉 Thanks kenji...

[@KenjiExplains]

Thanks for watching :)

[@kenbasilla5819]

Great! can you search across multiple sheets using this?

[@abdulsalam935]

Sir, video is good. How this can be done with "OLDER VERSIONS OF EXCEL"

[@funzonekerala]

Thanks kenji

[@KenjiExplains]

Thanks for watching :)

[@ishmeetsinghsachdeva6846]

Hi Kenji!! Was just wondering if you could show ways in case if we want values greater than or less than in this search as well.

[@Random905]

Super 👍

[@JohnKnorr]

Great tutorial! Is it possible to protect the sheet (data/formulas) while still making the search box editable?

[@yussofalfredofrancomorgado700]

Very nice kenji, thank you for sharing your knowledge, I'm looking for a way to look up a date (using filter ) that is in other table but has contacted with the main one table. ....is possible with filter funcion

[@salvatorva64]

In the search formula, it is more convenient and shorter to concatenate the table fields instead of repeating the search formula: =FILTER(Table1,ISNUMBER(SEARCH($H$2,Table1[First]&Table1[Last]&Table1[Country]&Table1[Age])),"No Match")

[@KenjiExplains]

I haven't tried it but it seems like a great suggestion!

[@salvatorva64]

@@KenjiExplains First, I'd like to thank you for your very didactic videos: they are helpful, clear, to-the-point. For your information, I tried with the CONCAT function; it did not work. It works well with “&”.

[@Static1Bolt]

Can confirm it works with the "&". Great suggestion!@@salvatorva64

[@sebrahim_t]

​@salvatorva64 if the search box is blank, how we can change the result in to a "enter text to search" message instead of showing all the data in the table selected.

[@Dan_De_Man]

​@@sebrahim_tat start of formula enter =if(H2="", "Enter Text to Search", Filter... And enter another bracket at the end

[@shabeerahmed1408]

Hello Kenji, how are you? Your all videos are very helpful for me. Regarding this " Filter" function, it only support in " Excel 365" & " Excel 2021" . What is the alternative function in old versions(like excel 2013, 2016,2019)?

[@rcbov_0243]

this question is very nice

[@rcbov_0243]

help us kentjh

[@adrianconway1339]

Really great and helpful video. One question if I may, my table has variable row heights but I can't seem to get the rows in the results to automatically adjust the row height. Is there any way to do this?

[@Chillmobei]

Hello Kenji, thanks for that nice tool. It works if anything is at the same page. But how to search if there is one main page with hyperlink word (each word connected to another site) is it possible to search at the main page and get the result were to click on and reach the correct page? I think this is high class of searching. Maybe it isn’t possible… but excel = excellent 😅

[@paulvirdee6730]

GREAT

[@lucianlesu1735]

Nice, very good. How can I make the results selectable, clickable or keep the hyperlink ?

[@atlascoo9647]

Super

[@jasiorr]

Hi. Good Instructions. Is there any option to be able to edit data from the search result ? or to highlight the findings on table itself ?

[@artur_momot]

Great video, thanks! Also, I have a question is it there any way to hide all those data that not being in use? For example, hide those list that showing if its empty search bar. Thanks a lot!

[@ahmedeid-bz8gd]

awesome

[@KenjiExplains]

Cheers!

[@omamen]

I have created the search bar and in works, thank you very much!!! BUT....after the resul come out i need to be able to modify some values in the result and save it. How do i do it? Thank you in advance!!!

[@seshkamal7745]

Thanks for the great tutorial. I f the first name is repeating twice lets say Janet is repeating in cell 2 and cell 4 does this formula works?

[@quyduongnguyen9660]

nice

[@MAN0202VAN]

That's amazing But can we apply it to Excel Online ??

[@rotethongvanit]

Thanks for your nice n easy tip.

[@marlonvitotorio3495]

How to search on different sheets?thank you for the answer

[@davek9079]

This is very helpful and works for me with one problem: my filter results don't automatically update (i.e. I have to put the curser in the first cell and then re-run the formula from the formula bar). The results do not auto-populate as I type in a query.

[@robertcortese476]

Cool, what about searching in another sheet in the same workbook?

[@dreddph]

Kenji thanks for this video. It worked for me. However, I want the search results to be blank at first before I enter a search parameter. Any ideas on how to accomplish this?

[@dreddph]

Never mind I did it already. Thanks!

[@HakendaNatan]

good

[@shahabshah6422]

Nice video brother but i want to search from different worksheet and there's multiple sheets and each sheet it has same columns names and i want to search from different columns also sometimes by name some time by their emp id so how I'll get my search data into search bar sheet from multiple columns?

[@giorgimodebadze1869]

Cool

[@julianaramburo2023]

Hi, thanks for this video. It's awesome. I tried using the dynamic search, and I did exactly what you did, but for some reason the cursor and the text don't show in the search box. The search actually works but I cannot see what I'm typing. I can only see what I typed after a click away from the box, anywhere in the sheet. It is weird because the search actually works and I can see the data moving dynamically, but I cannot see what I'm typing and it's really annoying. Can you help? thanks so much

[@ChrisJost]

Hi Kenji. I love this search box, but i had one problem when a tried to apply in my tables. The result not contain different in upper and lower cases (If data is written as ORANGE, the result not find if I type Orange in the SearchBox). How can I fix it?

[@s.d.nmarufi1051]

Very very very helpful video. But I want to search between two or three columns at a time. But it should be optional not necessary. Means when I select the condition than search conditionally otherwise should be searchable unconditionally

[@coronella4051]

Kenji excellent tutorial. A question about the filter formula (G5), is it possible to make it so that when H2 is empty nothing appears? So that the data only appears when I start typing in the H2 search box? Thanks for your help.

[@matteocervelli3786]

Ehy mate, did u find how to make this?

[@coronella4051]

Yes I Find the solution by my self =IF(H2=””,””;FILTER etc… @@matteocervelli3786

[@coronella4051]

In Italian =SE(C2="";"";FILTRO ecc.

[@bikeranand]

I too have the same doubt.

[@memorisingthings6234]

Sir, Which Excel Version do you use?

[@TOOMA2024]

can we adjust formula to make filter table empty wen we don't but anything in H2 ???? it will be cool 🎉🎉

[@dominicgentolia9259]

Hello Kenji

[@vinhdang4020]

Dear Kenji, thanks for the excellent tutorial! I am working with big data excel file [approx 200 columns and 1000 rows], may you advise the best method to filter selected data? Also, the data needs to be maintained and input, I tried to use data entry form in excel but there are too many columns so the data entry form in excel does not work. Thank you very much! Best regards! V

[@danielmontenegro8929]

Nice!! I have the same problem as well, at least something similar. If you find out or come up with something to solver this problem, please share it with me.

[@marifenoval8259]

Its very helpful but i would like to know how to copy the list of data in search bar for another sheet to print

[@biswadeepsarkar5724]

Hi is it possible to put formula only first line of the cell? For example 1. Balance as on 30-9-23 2. Abc.............. 3.Abc............. First line i want to Formulalise in order to change the date automatically in every month.