Make a Search Bar in Excel to Find Anything!
ฝัง
- เผยแพร่เมื่อ 9 มิ.ย. 2024
- 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.
![[TH] VCT Masters Shanghai Playoffs - Grand Finals // GEN vs TH](http://i.ytimg.com/vi/HL-hhLrtctw/mqdefault.jpg)
![[풀영상] 2024 김연경 초청 세계여자배구 올스타전 "팀 스타 VS 팀 월드" [유료광고 포함] #김연경 #국가대표 #배구 #배구올스타전 #팀스타 #팀월드](http://i.ytimg.com/vi/SUKUgH1ltng/mqdefault.jpg)
Check out our Excel course!: www.careerprinciples.com/courses/excel-for-business-finance
Can i get a discount code?
Amazing .. You are the ONE "EXCEL MASTER" 》》 THANKS 👏🏼👌🏼👍🏼
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.
Really cool video Kenji! Always thought there were so many cool opportunities with the Partial Match function and this may be the best one!
Thanks Kenji!! made my life a whole lot easier!! Great content, seamlessly explained ... Legend :)
These are very valuable skills, please continue to share so everyone can learn from you
🎉 thank you for sharing this kind of tutorial. It really helps me to learn Excel formulas. You explain it well.
This is the most useful Function i have seen for EXCEL. Thank you so much for this information
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!
Kenji... I am learning a lot with you with those cool Excel features... Thank you for sharing the knowledge...
That was extremely helpful and well explained, thank you
Thank you so much for a helpful video and detailed explanation.
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 ❤
Thanks kenji bro for awesome trick to create search bar simple and easy
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.
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.
I tried this but it diddnt work. Is that thw whole formula needed?
Does not work.
@@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(..............))
This is great. I am going to use this in an upcoming assignment I have.
Great! it is what i needed. Thank you Kenji very much !
Such a powerful tool/method and clearly explained. Can you use it to pool data from more than one Table or Sheets?
Bonus trick: Ctrl+F :) Great video, thanks.
Love this idea! Well explained!
so good that i subscribed. i love how simple this is.
This is so helpful and I love how Kenji explains everything.
Thank you!
Great! can you search across multiple sheets using this?
thank you! best wishes from Pakistan
Thanks for the explanation
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.
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.
This is a super relevant content! Thank you! 🎉
Glad it was helpful!
Beautiful explained ❤❤
Great tutorial! Is it possible to protect the sheet (data/formulas) while still making the search box editable?
Its wonderful...... I applied in my file👍👍👍👍👍👍👍
Thank you, very helpful
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
Thank You Master 😊
thanks, we d love if u make it with previous versions that doesn't support the formula FILTER
Superb ❤
.
Thank You So Much 💕
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.
Can you make a video on this and paste the link here
@@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
Thank you for this
Excellent 💯
Thanks it helps me to create me own search bar
THANK U SO MUCH, ITS WORKED
thanks alot Kenji
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?
Quite insightful
classic, thanks for sharing
Nice, very good. How can I make the results selectable, clickable or keep the hyperlink ?
Than you so much 😍
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?
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 😅
Wowww what a cool function!!
It really is!
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")
I haven't tried it but it seems like a great suggestion!
@@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 “&”.
Can confirm it works with the "&". Great suggestion!@@salvatorva64
@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.
@@sebrahim_tat start of formula enter =if(H2="", "Enter Text to Search", Filter... And enter another bracket at the end
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!!!
Thanks a lot man !
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!
Sir, video is good. How this can be done with "OLDER VERSIONS OF EXCEL"
Very informative
Awesome 👌
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)?
this question is very nice
help us kentjh
How to search on different sheets?thank you for the answer
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?
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?
Excellent....🎉
Thank you sir ji!
Thank You sir
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
thanks a bunch!!!!!
That's amazing
But can we apply it to Excel Online ??
👏Bravo! Brilliant!
First comment from India ❤🎉 Thanks kenji...
Thanks for watching :)
can we adjust formula to make filter table empty wen we don't but anything in H2 ???? it will be cool 🎉🎉
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.
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.
Thank you 🙏
You’re welcome 😊
That's great 👏
Thanks kenji
Thanks for watching :)
Can we update the filtered result like modifying the data after filtering it
If your search text is found in more than one column within the same row, will you not get repeated rows in the filter formula? You are after all just stacking the rows by adding them up...
super!
Sir, Which Excel Version do you use?
Dear sir,, what filter result only selected columns, for example only showing cell first and third column?
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!
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.
Its very helpful but i would like to know how to copy the list of data in search bar for another sheet to print
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?
Never mind I did it already. Thanks!
What we update the inventory list with new items should we rewrite the codings again
Do you have tutorial how to make hotel reservation calendar
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
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
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?
Cool, what about searching in another sheet in the same workbook?
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.
Ehy mate, did u find how to make this?
Yes I Find the solution by my self
=IF(H2=””,””;FILTER etc…
@@matteocervelli3786
In Italian =SE(C2="";"";FILTRO ecc.
I too have the same doubt.
Hey Kenji, thanks for the video, but how to avoid the downside of highlighting entire table when nothing is there in search box.
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?
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. 🙏
How can I do the dropdown list with data validation but with unique values only?
works great but does not bring any hyperlinks that may be in the source data. can that bne corrected?
Hi thank you for this tutorial. I did exactly the same as you.. but mine only works until the exact match, partial match does not work. it shows value error...
Isn't possible to search directly in the principal table??
Is it mandatory to create another lookup table??
How else do you search directly in the main table??
Thank you for edifying us 🙏🙏🙏
kenj, one question, why it doesn't show all of the data after entering the name? only the name appears
What Version of Excel are you using? I do NOT have a "FILTER function" on Excel 2007. I can't get it to work on 2007. Is there another way of doing this?