Make a Search Bar in Excel to Find Anything!

[@KenjiExplains]

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

[@NemoSnowman]

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? :)

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

[@CavanHagan2]

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

[@CavanHagan2]

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

[@abdulsalam935]

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

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

[@lisaninarisahlei2893]

i have the same problem =(

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

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

[@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(..............))

[@kenbasilla5819]

Great! can you search across multiple sheets using this?

[@thesith-f1t]

Hi! Thank you so much for the very easy to understand tutorial! I was just wondering if it was possible to create a search bar at say the main sheet (search sheet) and search for the location of such target( location of the sheet etc) or even more search for its related information (of the whole workbook) Thank you so much!

[@khalidkhalid182]

Hi kenji, to avoid the default highlight of table as described at 09:36 and have two tables shown we can use this update: in the conditional formatting tab, if the cell contain the text we type the formula with an if: =if($H$2 "", $H$2, "value") this will leave the table without a highlight. this formula will work also: =if($H$2 = "", "",(FILTER(Table1, ISNUMBER(SEARCH(H2, Table1[First])) + ISNUMBER(SEARCH(H2, Table1[Last])) + ISNUMBER(SEARCH(H2, Table1[Country])) + ISNUMBER(SEARCH(H2, Table1[Age])), "no match"))) thank you for your videos they are very clear and helpful!

[@dalimiso]

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

[@micheleschultz6791]

Excellent Teacher, very clear way of teaching and to the point. I am seeking how to pull certain info (line)from the master sheet to another sheet by a code. For Example, in an inventory schedule. All codes starting with MSB to flick to the button sheet schedule one underneath the other.

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

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

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

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

[@guardianofthemoon]

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

[@toddharrison2213]

Excellent teaching method. I like how you explain things slowly and with highlights. I have one question.... Are you able to change values in your search results that will remain changed in the original list. For example: Janet got married and you want to change her last name. Your list is very long and you don't want to scroll down to find her. Can you search in the search bar for Janet, change her last name from Murphy to Smith. Will it change to Smith in your original list?

[@RealTakunia]

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

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

[@henriquebbotelho]

Thanks for the awesome vid, man! I just can't seem to use it on more than one table. Can it be done? Thanks!

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

[@ivyckdotcom]

OMG you saved my career! This is the best tutorial about search bar in excel that I have watched. More power to you! Thank you so much!

[@khalidmounir3475]

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

[@cyberbeast1789]

good day sir! watching from the Philippines. I'd like to ask if you also a tutorial like this on the video that when customer/product is found, details of the customer will list/add/copy to another blank table? thank you..

[@konnojubhavana3207]

Hi, suggest on tip that ,I want to enter same data in different rows which are not continues ,Can we do enter row numbers search bar for enter same data

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

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

[@JohnKnorr]

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

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

[@tomletcher5127]

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

[@newmiziri]

Hi Keji n This one best explanation i have see please continue

[@CheatSheets]

Love this idea! Well explained!

[@MHasnainZia]

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

[@NishantPotdar]

Wow!! Nicely explained!! Happy with what I was looking for!! Thank you so much!

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

[@thedebis]

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

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

[@khato9720]

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

[@arshalmurmu]

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

[@luisllontopbarahona3188]

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

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

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

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

[@patriciaramirez6150]

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

[@NodictatorsinKZ]

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

[@RizavemObo]

Thanks for this! Such a big help

[@hamidabouali463]

Hello, for the last trick in 10:15 min, I have the last version of office 365 updated and It dosn't works for me any help can you provide thanks in advance

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

[@80andromeda08]

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

[@judithbredy5985]

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

[@KenjiExplains]

Thank you!

[@jaffa2023]

Can we update the filtered result like modifying the data after filtering it

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

[@lovelove4ever]

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

[@naingtun6620]

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

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

[@MSExcelSolutions-]

Excellent way of using Filter function..

[@MAN0202VAN]

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

[@KG-xs3ih]

This guy is a wizard ✨️

[@5El3ments]

This is very easy. What about returning the values only between 2 dates with different timings? Like, there were 10 different names in 10 different timings the sale happened on a particular day or between day X and day Y. I have tried many different ways, I don't get all the values returned in the result. Either 1 or 2 are left out or more is included as a result.

[@andreausa123]

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

[@robertcortese476]

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

[@bobcaruthers4427]

Great videos, but when I attempt the second formula, it throws up errors. despite my copying the exact formula you use.

[@MichaelBrown-lw9kz]

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

[@nehalpatel4345]

Hey Kenji, thanks for the video, but how to avoid the downside of highlighting entire table when nothing is there in search box.

[@leo2hari]

First comment from India ❤🎉 Thanks kenji...

[@KenjiExplains]

Thanks for watching :)

[@LedgerBytes]

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

[@punchole]

Dear sir,, what filter result only selected columns, for example only showing cell first and third column?

[@T_iko_S]

Hi, is there a way to make it so when you search, it returns the values its found in the smaller table but is also editable in that table, and then whatever changes are made in the smaller search table are then executed in the original table? I hope that makes sense, any help would be greatly appreciated!

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

[@lucianlesu1735]

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

[@ThalhaYoosuf-w8y]

What we update the inventory list with new items should we rewrite the codings again

[@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? Are there any alternatives?

[@dougmphilly]

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

[@charimainegumarac4834]

kenj, one question, why it doesn't show all of the data after entering the name? only the name appears

[@davidtulk7159]

This works great (even though I use Excel for Mac which doesn't have Active X Controls) but I just use the formula in a cell rather than a search box, however, Once I find a match, I need to be able to edit the data and have it update the original data in the table. Can someone assist with this additional task please.

[@hamzariazuddin424]

i never knew this, amazing

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

[@imadesuparta6705]

Do you have tutorial how to make hotel reservation calendar

[@rosaliobusobuso7248]

Is this ok to be used on a different sheet?

[@memorisingthings6234]

Sir, Which Excel Version do you use?

[@KVIXNodalOffice]

How can we use this search bar method to search data from more than one table for eg. i have three different table is in three different worksheets. And need to use this kind of search for 3 worksheets together. We can not combine 3 worksheets tables into one worksheets, those 3 worksheets have to be kept separate for some other purposes

[@normanrock1362]

works great but does not bring any hyperlinks that may be in the source data. can that bne corrected?

[@Shng1968]

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

[@RKSiNgH-mx9cm]

Thanks it helps me to create me own search bar

[@stefankleinhans4895]

Hi, Hope all is well. I am trying to use your search but with a slicer. Filter the slicer to show what ever I have searched. Is that possible?

[@mozartjohann6886]

Thank you for this amazing content

[@kenneth72570]

Can i do this using macbook?

[@Raj-cf6fe]

I have multiple sheet of students with their certificate number and exam date of different lesson. I am able to find all the subjects done using Find and Replace. I want to create a sheet with a search command to gather all the subject details of a student. Is that possible? Can you tell please?

[@joannagv3728]

Does it work for Google spread sheet?

[@spongebobby188]

OK, I used this successfully on a table with lots of text comments, so the search is helpful. BUT, I now cannot view the contents of the cell as it doesn't appear in the formula bar as that only shows the index formula. This means I have to copy the result of the search into a new worksheet to view the contents! Please advise on how to resolve this. 🙏

[@nataliemccall4573]

this was awesome however, how can I do a multiple search within the search box. For instance in my first cell I have office numbers (ex: 0005, 00061, etc). If I wanted to search in the search box for office 0160 and 1310, how do I do that?

[@MO-di7up]

Fantastic Thank you.

[@amribrahim8912]

Really cool video but I do not understand how to search in the list cell

[@kovilipradeep]

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

[@AburaGamer]

I have a big collective sheet and I want to filter based on search, but with this function it returns ALL columns, also the useless ones. I tried added only those columns to the array area, but then I just get VALUE! back. Any chance you can explain how to search on selective source columns?

[@BernardBarnor]

Great content but my excel does not have the filter formula

[@syedikramullah1114]

How to make a summary from multiple sheets jan to dec with lookup.