Make a Search Bar in Excel to Find Anything!
HTML-код
- Опубликовано: 3 июн 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.
Check out our Excel course!: www.careerprinciples.com/courses/excel-for-business-finance
Can i get a discount code?
Really cool video Kenji! Always thought there were so many cool opportunities with the Partial Match function and this may be the best one!
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.
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!! made my life a whole lot easier!! Great content, seamlessly explained ... Legend :)
🎉 thank you for sharing this kind of tutorial. It really helps me to learn Excel formulas. You explain it well.
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(..............))
These are very valuable skills, please continue to share so everyone can learn from you
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...
This is the most useful Function i have seen for EXCEL. Thank you so much for this information
That was extremely helpful and well explained, thank you
Such a powerful tool/method and clearly explained. Can you use it to pool data from more than one Table or Sheets?
Great! can you search across multiple sheets using this?
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
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.
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
Thank you so much for a helpful video and detailed explanation.
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.
Great tutorial! Is it possible to protect the sheet (data/formulas) while still making the search box editable?
Thanks kenji bro for awesome trick to create search bar simple and easy
Love this idea! Well explained!
Great! it is what i needed. Thank you Kenji very much !
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
so good that i subscribed. i love how simple this is.
thanks, we d love if u make it with previous versions that doesn't support the formula FILTER
Bonus trick: Ctrl+F :) Great video, thanks.
This is great. I am going to use this in an upcoming assignment I have.
This is a super relevant content! Thank you! 🎉
Glad it was helpful!
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?
Thanks for the explanation
thank you! best wishes from Pakistan
How to search on different sheets?thank you for the answer
THANK U SO MUCH, ITS WORKED
Nice, very good. How can I make the results selectable, clickable or keep the hyperlink ?
Beautiful explained ❤❤
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 😅
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?
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!
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!!!
Its wonderful...... I applied in my file👍👍👍👍👍👍👍
This is so helpful and I love how Kenji explains everything.
Thank you!
Thank you, very helpful
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.
Sir, video is good. How this can be done with "OLDER VERSIONS OF EXCEL"
Superb ❤
.
Thank You So Much 💕
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, 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
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
That's amazing
But can we apply it to Excel Online ??
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?
can we adjust formula to make filter table empty wen we don't but anything in H2 ???? it will be cool 🎉🎉
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
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.
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!
Thank You sir
Can we update the filtered result like modifying the data after filtering it
Sir, Which Excel Version do you use?
Thanks it helps me to create me own search bar
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
Thank you for this
What we update the inventory list with new items should we rewrite the codings again
Thank You Master 😊
Excellent 💯
Dear sir,, what filter result only selected columns, for example only showing cell first and third column?
Quite insightful
works great but does not bring any hyperlinks that may be in the source data. can that bne corrected?
classic, thanks for sharing
thanks alot Kenji
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?
Is this ok to be used on a different sheet?
Do you have tutorial how to make hotel reservation calendar
Thank you sir ji!
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...
Cool, what about searching in another sheet in the same workbook?
Hey Kenji, thanks for the video, but how to avoid the downside of highlighting entire table when nothing is there in search box.
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...
Wowww what a cool function!!
It really is!
How can I do the dropdown list with data validation but with unique values only?
kenj, one question, why it doesn't show all of the data after entering the name? only the name appears
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?
on my mac, the developer option , doesn't show the "insert", what do i do? thank you
Thanks a lot man !
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 🙏🙏🙏
Than you so much 😍
The problem I see here is that most sheets if not all contain formulas to perform calculations as in a Register transaction sheet. Converting to a Table seems to destroy the integrity of a sheet - I also have a drop down combo which populates upon mouseclick. Cannot this be done without converting to a table. cr:
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. 🙏
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.
Thank you 🙏
You’re welcome 😊
Great content but my excel does not have the filter formula
hello i have a problem with that my office is 2019 what is alternative for this ? can you help ? please.
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!
Awesome 👌
Im working on a project that I need the searched data linked to another excel tab. I cant seem to set a formula for that.
Thanks kenji
Thanks for watching :)
Hi Kenji,
I am trying Filter formula but it return all value to 0 every time! using office 365
any suggestions?
I just worked follow yours. But due to want to add function more if isblank, it shall return the true & fale argument. Ex. My column 0%, it returns to 0%, if column/cell empty shall be show empty not to 0% especially for Important tax. May you advise pls
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?