Dynamic Filter in Excel - Filter As You Type (with & without VBA)

Рет қаралды 311,836

Күн бұрын

In this video, I will show you how to create a dynamic filter in Excel (where you can filter as you type in Excel).
I cover two methods to create this dynamic filter:
1. Using the FILTER function - available for Office 365 Users only (0:40)
2. Using a Simple VBA code to create the dynamic filter (7:40)
00:00 Intro
00:40 Creating Dynamic Filter Using the FILTER function
07:40 Creating Dynamic Filter Using VBA
Get Office 365 - microsoft.msafflnk.net/Vk9OR
Download the Dynamic Filter Example workbook - bit.ly/2wuS1Vz
Video on using Filter Function in Excel - • Excel FILTER Function ...
VBA code to filter as you Type:
'Code Created by Sumit Bansal from TrumpExcel.com
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=2, Criteria1:= "*" & [F1] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: kzfaq.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

Пікірлер: 393

@trumpexcel 4 жыл бұрын

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.

@gsssbibipurschoolpatiala6416 4 жыл бұрын

ÁWESOME VIDEOS Dear Sumit sir, sir ,,, I means macros which single click to convert Excel to PDF useful for all worksheets of workbook that encompass FIT all Columns on A4 Page and convert Excel to PDF ... Thanks Again for sharing great stuff. HATS OFF handy macro pops up dialog box and message FIT ALL COLUMNS in A4 and then convert excel to pdf ...

@laxmangiri7204 4 жыл бұрын

Happy to see you here in KZfaq

@abdulrahmanbaamer8624 4 жыл бұрын

Great video. How to do the same in power query (not required to be dynamic), but search string in another query and return all "search" results. Thanks in advance.

@sadeqs7680 4 жыл бұрын

Thanks for your nice videos 👍 There's a little problem, this VBA does not work with numbers! I mean it doesn't filter numbers.

@goswamidillip 4 жыл бұрын

Can I dynamic filter works on multiple data in a one cell ??? Plz reply

@samuelmontielsegovia3739 Жыл бұрын

I can´t believe that after watching multiple videos and not being able to do it, you just gave a perfect explanation, you are the man! Thank you for sharing.

@trumpexcel Жыл бұрын

Glad the video helped 😊

@valdevinoromao5432 Жыл бұрын

After living many years with a dynamic filter that always gave an error, I was unhappy and kept looking for a solution, as many have said I watched many videos and none brought the solution so accurately, you deserve many likes👏👏

@Raining76 4 жыл бұрын

Like the way you are walking us through tips with examples. Highly useful. Thank you.

@SMITDEDHIA96 4 жыл бұрын

Simple, yet powerful! Thank you, boss.

@daleanderson5258 4 жыл бұрын

Your videos are great. They are short and to the point and they are always relevant.

@ilikecookiesforreal 3 жыл бұрын

This is the only VBA code that has worked for my version of Excel so far - thank you!

@khattiyachantrarawiwartkul6372 3 жыл бұрын

Thank you professor. Finally I found my wish tutorial clip. I tried so hard to find how to create serch box as filter+drop down ^_^

@realp.1700 Жыл бұрын

I'll repeat what so many have said in the comments, after looking at multiple videos, yours is by far the clearest, most concise "step by step" on KZfaq. Others seem to jump steps to shorten the tutorial others seem to complicate their formulas to achieve the exact same result...You my friend, are a gem!

@trumpexcel Жыл бұрын

Thank you for the kind words. I am glad the video has been useful 🙂

@imeam 4 жыл бұрын

Absolutely brilliant, keep up the good work. Best wishes

@Nigromancy 3 жыл бұрын

Love your videos you make everything look so easy thank you!

@xkaliba1751 3 жыл бұрын

Thank you for this tutorial it is exactly what I was looking for. How do i then add the search value to the table if value is not found without having to scroll down to the bottom of the table as you may have 500+ entries?

@rickkeev4919 4 жыл бұрын

Great video, I am so glad you consider those of us that do not have the insider 365 version.

@trumpexcel 4 жыл бұрын

Thanks for commenting Rick... Glad you found the video useful!

@krishnajayanti2884 Жыл бұрын

Hi Sumit Really what I was looking for. Thanks a lot !! Additionally, is it possible to add a second filter for another column, and display results based upon the output after 2 filters ?

@user-nm4ul6gr6o 4 жыл бұрын

Thank you so much, dear teacher, for this great video and for this explanation the best.

@torseip 4 жыл бұрын

Good one and very well explained, as usual!

@PrasenJeet 8 ай бұрын

Thank You so much for the tutorial. Can you please provide a way to extract specific rows dynamically on another sheet that meeting a criteria.

@bluesmaster1005 2 жыл бұрын

Thanks for this video! This is wonderful, however how do I apply the filter across an entire table vs a single column?

@8CountLife 3 жыл бұрын

1. Creat copy of data 2. Copy to Excel Table 3. Name Table 4. Insert Text box from Developer tab 5. Link text box with empty cell 6. Go to design mode, double click 7. Paste VBA code and modify

@bhatkiran7 Жыл бұрын

Thanks a lot for sharing a simple and easy VBA code to filter data in excel. Filter formula did not help me as I had to execute it on a older version of Excel. The VBA code did not run in my protected sheet. To resolve this I inserted a code in the beginning of your code to unprotect the sheet and after your code is executed the sheet is protected again with a password. As data is entered in the text box the sheet is unprotected and immediately protected as the filter function is functioning. Had a great time playing around this code to favor my requirement. Thanks again.

@timbabitelor 4 жыл бұрын

Great as always, thank you Sumit.

@javedahmed8343 2 жыл бұрын

This is the most effective application oriented code. It shows ur genius towards making the code application specific

@luciandima9007 Жыл бұрын

Nice tutorial, just I was looking for. Well done.

@jameslynch300 5 ай бұрын

Thank you so much for this video/ I spent hours looking through others which were of little to no help. Your video was clear to understand and so helpful

@johnborg5419 3 жыл бұрын

First time on your channel. This was interesting!!! : )

@ujjwalshaw6889 3 жыл бұрын

Hi Great Video....Can we use textbox with drop-down list ? So that user will get both option either type or select from list.

@RakeshKumar-or5gl 3 жыл бұрын

Hi, very useful video. But is there a way we can put the tables in other sheet and make this filtered data copy to another sheet?

@jessicarios5415 3 жыл бұрын

I've been trying to do this but didn't know what it was called! Ahhhh thank you!!!

@askontai88 2 жыл бұрын

Man, you are a genius! Thank you a lot! I've subscribed and liked it! And please can you help how can I filter in several fields?

@Krsnadino 3 жыл бұрын

Much help. Question: how does it Auto clear filter within the same dynamic filter code

@syedluqmanhakimsyedhassan9977 2 жыл бұрын

Thank you, really appreciate the detailed explanation.

@girishsawant8263 3 жыл бұрын

Thank you very much Sir, for sharing such valuable and useful knowledge.

@sasavienne 4 жыл бұрын

A very useful and nice tutorial. Thanks a lot. 👍 🌟 🌟

@babairecornelius9110 Жыл бұрын

Thank you so much for this tutorial. I have one question though, how can i replace the table name "Data" with cell range? Thanks

@excelisfun 4 жыл бұрын

This is a great trick with FILTER : ) : ) Thanks for the Totally Awesome Video, Sumit!!!!

@trumpexcel 4 жыл бұрын

Thanks for commenting Mike... Glad you liked it!

@excelisfun 4 жыл бұрын

@@trumpexcel Go Team!!!!!!

@sandeepnair8226 2 жыл бұрын

@@trumpexcel Sir what if one name falls in 2 or more regions. like if Rob falls in US, Cananda & Asia at a time? How would be the syntax Sir? Thankyou

@sandeepnair8226 2 жыл бұрын

@@excelisfun Sir what if one name falls in 2 or more regions. like if Rob falls in US, Cananda & Asia at a time? How would be the syntax Sir? Thankyou

@deepanshugarg8480 4 жыл бұрын

Thanks for the trick. But I want to know how can we filter the data and load the filtered data in the combobox in the userform. Kindly guide me on this.

@adilurjollyrahman2455 2 жыл бұрын

Amazing , recently i have started using office 365 , loved it ....BTW there are 63(dislikes)- only GOD can save them

@saptarshi7tube Жыл бұрын

Excellent video.!! Is there a chance to include an OR operation in the textbox? For example "US" OR "AS" to show all is and Asia related data?

@peacespotting 2 жыл бұрын

Thanks for the vid! I want to know if it's possible to use the Filter function (or similar) to actually apply the filter to the original table to display the results (which is basically what the VBA does) rather than reproduce the results in a new set of cells. I am working with tables with 14 columns so it's not practical to have the results side-by-side like in your example.

@mobolajisonubi8556 3 жыл бұрын

Nice video. A quick question, can I do my filter on multiple search box? (that is having 2 filter criteria) I tried including AND or OR function in the SEARCH part but gave an error message.

@vixrulez4ever 2 ай бұрын

thank you. you earned another sub for being straight to the point.

@thedangourytv1020 4 жыл бұрын

Hello Sir I just finished my vba project on Excel and I want to know is theire any way to put some code to control the project whit a gamepad thank you again

@YaNykyta 2 жыл бұрын

Nicely done Sumit. Thanks.

@muneer0313 3 жыл бұрын

Very well explained, Thank you

@williamarthur4801 Жыл бұрын

What a nice straightforward way.

@karimredouane2150 3 жыл бұрын

thanks sumit as always simple and helpful

@Heavieth 4 жыл бұрын

Thank you for this great class!

@trumpexcel 4 жыл бұрын

Glad you found it useful George!

@ArunKumar-zv1ge 3 жыл бұрын

Ur channel is good enough, no need of searching anywhere for the excel related information👍😎

@trumpexcel 3 жыл бұрын

Thanks for the kind words Arun... Glad you're finding the videos useful!

@ashwathrao7862 4 жыл бұрын

Good one and very well explained.. 👍🏻

@UDTEYKADAMFOUNDATION 3 жыл бұрын

Hello, Thanks for sharing valuable information, Could you please also share how to make multiple Search box for multiple filters.

@ahmedhussain8041 4 жыл бұрын

First time on your channel.. Quick question: can i filter date with same formula? Thank you!!!!

@telljansiddiqi 4 жыл бұрын

Amazing THANK YOU

@kraj1966 2 жыл бұрын

Thank you So much fro your tutorial on Listobjects. Sir, is it possible to protect the sheet when Listobjects VBA code is used. Kindly help. Thank you

@user-xm3tb4oq9e 4 жыл бұрын

Brilliant. Thank you

@tsl-sr.mgr.commercial716 4 жыл бұрын

Gazab...... Very nice & useful technique. You r great.

@ivanshaw2229 3 жыл бұрын

This is GREAT! Thank you!

@IncredibleOwais 3 жыл бұрын

Speechlees, you are just amazing

@hostelnimoj1799 Жыл бұрын

It is very helpful I also have questions If i want to use this singal search box for filtering multiple worksheets of my workbook at same time than how i can do it through VBA I tryed many times but no result found So can you prepare video on that so it is easy to understand

@lorrainego9298 3 жыл бұрын

Thank you so much for this!! :)

@walimuhammad3119 2 жыл бұрын

You are awesome Sumit. Thanks for the Tip

@caesar7821 Жыл бұрын

Excelent video, this is what i've looking for so long. it is a very useful tool. Thank you So much !!

@accnotech3863 4 жыл бұрын

I have been following your tricks from quite a long time, used this one too. my question is, i have placed this textbox in sheet1 and i want when i activate sheet 1 this textbox should be ready to use, for which i have used sheet activate event and activated textbox once this sheet is active, still not working. please suggest some thing for this... and btw i am using vba method of this trick

@beatriza.darroz8513 4 жыл бұрын

Would it be possible to have it search not in one specific column, but in more than one, like name and region, at the same time?

@nagendravishwamitra3652 Жыл бұрын

Hi sumit, thanks for such a wonderful video, This has bought my work load really down. Sir, have one query will this work when we do not want all the column names to be displayed in other words from the entire table i want only few columns to be displayed

@xkaliba1751 3 жыл бұрын

hi i have tried this and it works great I have a couple of question if you can help first how do you solve the issue when you forget to click the design mode again and the reference isn't valid error message keeps popping and I can not get rid of it without stopping the application using the task manager. Secondly, how do I add a new entry to the table when the search doesn't find the person in the list so that it automatically adds it to the table.

@mla6marc 6 ай бұрын

Thanks and this is so useful. Btw, could you share how to reset the filter after search

@dudybug 3 жыл бұрын

This man saved my ass at work today 🙏

@silverfunnel6819 4 жыл бұрын

Always useful stuff on your channel:-)

@CollegeFrolics 3 жыл бұрын

Really really helpful !! Thanks a lot !

@vishalathwani 4 жыл бұрын

Thank you for this amazing trick. A quick question, how should I approach the VB script in case I don't want to use table

@ankursharma6157 4 жыл бұрын

Hi Vishal, Hope, following helps: Workbooks("").Worksheets("

@karandabhi3228 3 жыл бұрын

What change is required in code to apply filter based on the value in any columns instead of only column 2?

@tanveerbatliwala Жыл бұрын

Great Stuff!! Very nicely explained

@wxmyjnsn 3 жыл бұрын

OK Thank you for this video. I have a second part to this I would like your help with. I want to add a button that will clear the textbox instead of me having to back space. For the life of me I cannot get this to work. Would you help me please?

@nadermounir8228 2 жыл бұрын

Amazing video 📹 thank u for ur hard work Mr. Trump Excel :)

@ericopoku6412 3 жыл бұрын

You are one of the best. Keep up the good work.

@GanatraNilesh 3 жыл бұрын

Is it possible to add multiple range fields to search in multiple columns instead of just one?

@pinkeishz9106 4 жыл бұрын

👍Good one, will try...

@matteofelici4525 2 жыл бұрын

Samit great video. I have used it in VBA and works fine, but I have a question: Since my table is almost 400 lines (and 25 columns right) and my search might end up with 20 lines, I need to move cursor up/down and left/right to find correct code and prices. (and I leave the cursor/active cell there) If then I make another search and the new records are before the previous ones where I left the cursor/active cell then it will not automatically display the search but I have to manually activate a cell on the headers and then move the active cell with arrow down to jump to the searched list. (so to make it appear, let's say) Is there any way to insert in the VBA steps of process an additional step like "GOTO" first top left cell of the table or "REFRESH & DISPLAY" the entire table before digiting or and additional text box button to "CLEAR FILTER" brigning back the table and the active cell on the top? Thank you if you have a suggestion for it.

@zaidhamidalnaimiy9758 4 жыл бұрын

thank you for video, but can you make it in mac cuz a lot of function no appears.

@rajeshveljishah1299 3 жыл бұрын

I would like to have vba code for blinking cell, single or range of cell, which can be used in the entier workbook, it should start blinking when a certain criteria or condition.

@shouviksarkar2560 4 жыл бұрын

Great job, excellent

@coolag74 4 жыл бұрын

Fantabulous. Hats off to boss..

@rakeshbond009 4 жыл бұрын

i have changed Field:=3 but filter not working. Possibly as field 3 is numeric. Any idea how to use filter for Numeric

@kossiviaglee2997 2 жыл бұрын

Very useful, thanks!

@VijayKumar-xr7sd 4 жыл бұрын

wow fab....Just working super bro...one issue the same doesnt work when the sheet is protected

@wikiwawa100 Жыл бұрын

Can you update this such that the range of what’s being filtered is dynamic (if I add to the table it still filters the pre-existing table plus the extra row that I’ve added)

@robertyeing9666 Жыл бұрын

Hi, I used your method of filtering with a dynamic drop down box (another video) and it works great, I substituted the drop down box for a text box and it works good too...... However, by default when the box is blank, all the list is displayed..... I would like to know how to not show any results until text is entered....... no text, no results..... hope you can advise me

@manzoorofficial23 2 жыл бұрын

This is superb. But can Filter function be wrapped to a sumifs? Problem: I am working on a data set having Dates, Area, Customer, product and Amount. I want to filterout top Five Customers by Date and the problem here is all the fields have multiple Rows and i want to filter the customer after summing the corresponding amounts between dates. Is there a way to do this. Thanks