@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? :)
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!
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 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
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.
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.
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.
@@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(..............))
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)?
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.
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 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 “&”.
@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.
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?
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 😅
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.
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!
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..
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
@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?
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!!!
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?
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!
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. 🙏
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
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.
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
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.
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?
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?
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.
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, 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!
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?
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?
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
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?
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 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...
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.
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...
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
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:
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!
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.
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 🙏🙏🙏
Check out our Excel course!: www.careerprinciples.com/courses/excel-for-business-finance
Can i get a discount code?
@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? :)
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!
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
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.
Sir, video is good. How this can be done with "OLDER VERSIONS OF EXCEL"
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.
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(..............))
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
i have the same problem =(
Great! can you search across multiple sheets using this?
Wow!! Nicely explained!! Happy with what I was looking for!! 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!
Bonus trick: Ctrl+F :) Great video, thanks.
Thanks Kenji!! made my life a whole lot easier!! Great content, seamlessly explained ... Legend :)
You literally taught me more in 5 minutes than any other video on here thank you!!!!
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.
Such a powerful tool/method and clearly explained. Can you use it to pool data from more than one Table or Sheets?
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
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?
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 😅
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.
Love this idea! Well explained!
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 ❤
This is so helpful and I love how Kenji explains everything.
Thank you!
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!
Hi Keji n This one best explanation i have see please continue
This is the most useful Function i have seen for EXCEL. Thank you so much for this information
Amazing .. You are the ONE "EXCEL MASTER" 》》 THANKS 👏🏼👌🏼👍🏼
You just made my job 10x easier, thanks!! Subscribed
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!
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..
Kenji... I am learning a lot with you with those cool Excel features... Thank you for sharing the knowledge...
thanks, we d love if u make it with previous versions that doesn't support the formula FILTER
Excellent way of using Filter function..
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
This a valuable tutorial for my work!! Thank you so much...
@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?
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
This is awesome! Loved it and use it for my projects.
Great videos, but when I attempt the second formula, it throws up errors. despite my copying the exact formula you use.
That’s so great that I will try it.Thank you very much Sir.
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!!!
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 ?
kenj, one question, why it doesn't show all of the data after entering the name? only the name appears
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?
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!
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. 🙏
These are very valuable skills, please continue to share so everyone can learn from you
Thanks for the awesome vid, man! I just can't seem to use it on more than one table. Can it be done? Thanks!
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
Great! it is what i needed. Thank you Kenji very much !
🎉 thank you for sharing this kind of tutorial. It really helps me to learn Excel formulas. You explain it well.
That's amazing
But can we apply it to Excel Online ??
This guy is a wizard ✨️
Dear sir,, what filter result only selected columns, for example only showing cell first and third column?
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.
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
so good that i subscribed. i love how simple this is.
Thanks for this! Such a big help
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.
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 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?
can we adjust formula to make filter table empty wen we don't but anything in H2 ???? it will be cool 🎉🎉
Thank you so much for a helpful video and detailed explanation.
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!
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.
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?
Nice, very good. How can I make the results selectable, clickable or keep the hyperlink ?
First comment from India ❤🎉 Thanks kenji...
Thanks for watching :)
Its very helpful but i would like to know how to copy the list of data in search bar for another sheet to print
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, 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!
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?
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?
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.
Can we update the filtered result like modifying the data after filtering it
works great but does not bring any hyperlinks that may be in the source data. can that bne corrected?
That was extremely helpful and well explained, thank you
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?
How to search on different sheets?thank you for the answer
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
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?
Its wonderful...... I applied in my file👍👍👍👍👍👍👍
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
Cool, what about searching in another sheet in the same workbook?
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...
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.
Thank you for this amazing content
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...
thank you! best wishes from Pakistan
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
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:
This is great. I am going to use this in an upcoming assignment I have.
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!
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.
which office version use it Because i am use 2021, but not show me as suggested in down table.
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 🙏🙏🙏
Does it work for Google spread sheet?
What we update the inventory list with new items should we rewrite the codings again
But the filter function is not available in non-office365 excel. Is there any other alternative?