Create Dynamic Excel Filter - Extract data as you type

Рет қаралды 625,204

Күн бұрын

In this excel tutorial video, learn how to create a DYNAMIC EXCEL FILTER - which extracts data as you type.
Download file here: trumpexcel.com/dynamic-excel-...
This dynamic Excel filter allows you to type the filter text in a text box and instantly extracts the records as you type in the search bar.
While Excel already has a filter and advanced filter functionalities, these are not dynamic. This means that you will have to specify the criteria and filter, and then change the criteria and filter again.
This dynamic Excel filter, on the other hand, filters the data as soon as you change the text in the search box.
This Dynamic Excel filter can be created in 3 steps:
-- Getting a unique list of items (countries in this case). This would be used in creating the drop-down list.
-- Creating the search box. Here I have used a Combo Box (ActiveX Control).
-- Setting the Data. Here I would use three helper columns with formulas to extract the matching data.
00:00 Intro
00:10 Demo of the Dynamic Filter Box in Excel
01:12 Creating a Unique List of Items
02:20 Creating the Named Range
03:17 Creating the Filter Combo Box
05:00 Adding the Helper Columns
08:47 Extracting the Data By Typing in the Filter Combo Box
You can use this dynamic Excel filter when you have a huge dataset and you want to quickly filter it by entering the text. Since this is dynamic, you can remove the existing filter by simply deleting the text in the text box and type something else to filter based on it.
While this advanced dynamic Excel filter works great with a small dataset, you may find that it tends to get a bit slow when you have large data sets. This is because it uses formulas and helper columns which can lead to slow processing time and data analysis.
Step-by-step written tutorial and download file:
trumpexcel.com/dynamic-excel-...
You May Also Like the following Tutorials:
-- Creating a drop-down list in Excel: • Creating an Excel Drop...
-- Advanced Filter in Excel: • Advanced Filter in Exc...
Also, I have made all of my Excel courses available for FREE. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
#Excel #ExcelTips #ExcelTutorial

Пікірлер: 388

@zakeerrg3010 Жыл бұрын

I love the way you explain every detail, with definitive pronunciation and clarity. Keep up your good work.

@juanmanuelprat5490 8 жыл бұрын

This was a life saver for me. Very clear and quick. Straight to the point. I used a helper pivot table and an slicer to pick the "unique value" from the list, so it was exactly what I was looking for. Thanks a lot!

@AlexaGTV 3 жыл бұрын

I am creating a new database/housing system for my position and have been SEARCHING for something that would let me filter easier with numerical data and text data. This is the best solution I've found and you made it so easy to follow and do. Thank you so much

@June25th87 4 жыл бұрын

I've been watching many video about this trick, but your formulas is the best and most applicable. Thank you!

@leshecclaassens3740 4 жыл бұрын

Great tutorial. Based on this tutorial I manage to get my own version up and running. Also learnt a lot in the process; much appreciated.

@anirudhjayant9557 7 жыл бұрын

Thanks a lot, man! You have saved my developer's time. I highly recommend this to anyone who is looking for any search functionality in excel.

@bellostephen6791 5 жыл бұрын

You rock, man! You're the best! Been searching for how to do this for several hours...You made it simple!!!

@andrewo6521 4 жыл бұрын

Thank you - I work on an IT desk and we use this to associate keywords to specific departments. Works brilliantly! It can be tricky when if the user (me) makes a tiny error after inital set up but it's worth the time taken to set up

@trumpexcel 4 жыл бұрын

Glad it helped :)

@TheOndricek 8 жыл бұрын

This is terrific! And I saw in one of the comments below that you can do this for two columns at the same time - amazing! Thank you for sharing!

@TheFamousPatrick 8 жыл бұрын

Thank you, Thank you, THANK YOU!! I looked for this very process for over an hour and all I could get was VBA solutions, and I don't have that skill yet. I need to add this functionality to my spreadsheet and could not figure out how to do it. Your instruction was concise and complete! Again, thanks! BTW, the link to the worksheet didn't work, but I used a copy of my data and worked along with the video and it was just fine.

@coachz7970 9 жыл бұрын

Great and easy way of explaining the topic, thank you very much.

@IonbeamGreg 6 жыл бұрын

Very interisting usage of different formulas - Dynamic w/o VBA ! Great !

@ankitamaini 9 жыл бұрын

Helped me a lot! Great explanation, so clear and everything is detailed.

@gm2023MJ 2 жыл бұрын

Very informative and useful. Your step by step instructions is a great help to a layperson. Wonderful tips to help in retrieving information. Thank You Sumit.

@TheKt75 4 жыл бұрын

Pressure cooker- 3 whistles. Lunch is ready. Great video.

@epicham08 5 жыл бұрын

Thank you so so so much. I created my own dynamic search filter with your help alone. Keep it up.

@kmcdermott69 4 жыл бұрын

You just made my life much easier - thank you!

@AliRazaAgha 5 жыл бұрын

Very very well done bro. I've been looking for such kind of information for days. It's going to help me a lot in my analytical tools.

@smartshan4u 4 жыл бұрын

Thank you very much for sharing this video

@BenLinfordUK 4 жыл бұрын

This is a very clear and concise tutorial video. Thanks!

@dudukusudhakar2903 8 жыл бұрын

sir, your way of presentation is excellent and simply undestanble.

@educacionportable 5 жыл бұрын

Excellent tutorial Thank you so much God bless you. Best regards from Nicaragua.

@NjaanAdima 4 жыл бұрын

Finally, I came to the right place. Thank you from the bottom of my heart!

@abhi2011674 4 жыл бұрын

This is bit complicated method. there is a much easier way to do the similar formulation in the data.

@pandharinathjoshi6565 3 жыл бұрын

Great video sir

@DVSgolddust 5 жыл бұрын

Thank you For your help. This made my Stockhandling database very complete on an easy way.

@whitedove6878 8 ай бұрын

Dear Mr. Sumit great day to you. I have been your student and admirrer since years. How nicely you teach slowly and step by step giving all details. Your are marvelous.

@trumpexcel 3 жыл бұрын

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Here are some other Excel tips videos you may find useful: ✅ Filter data as you type (using FILTER Function or VBA): kzfaq.info/get/bejne/rqiCnqanm7i7k3k.html ✅ Advanced Filter in Excel: kzfaq.info/get/bejne/kLt2eNSbqbKxYn0.html Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course

@andrewstevenson5449 4 жыл бұрын

Excellent clear summary of how to solve a tricky issue.

@furaosentu 2 жыл бұрын

5:24 OCD trigger. Apart from that, great method and great video.

@jimkuhn7076 8 жыл бұрын

Thank you very much for this video; it was well done, and gave me more than one tip.

@ritchicaca 8 жыл бұрын

Trump Excel, you are a genius! This is great! Good job! Very helpful!!!

@littlelionmanOO 2 жыл бұрын

After watching a few other videos, this one is the best ! thank you!

@jeanielee7980 6 жыл бұрын

thank you! this was very easy to follow and helpful!

@sglander2 5 жыл бұрын

This is awesome! I used it to make an incredible daily job cost report. Thank you!

@chuzi_shopr Жыл бұрын

Thank you for a very clear instruction on how to do each step. :)

@arpitagarwal3006 4 жыл бұрын

Thanks a lot... I've been in great trouble for last few months... U brought me come out from it...

@hamidlatache2053 6 жыл бұрын

Perfect I was looking for this trick for so long , Thank you

@mrrabbeatsa 5 жыл бұрын

Thanks Man I've been looking for something like this for weeks.

@sunilvetri2716 2 жыл бұрын

I'm The luckiest person I think about it past 3 days finally I found it and u perfectly nailed it.

@dishagupta1122 7 жыл бұрын

Its awesome! just what I have been looking for since days now. Many thanks Champ!!

@amansoni8899 5 жыл бұрын

Great explanation bro, You're the best! I'm searching for how to do this from several hours...but you made it simple. God Bless You.

@trumpexcel 5 жыл бұрын

Glad you found the video useful Aman :)

@janined.56 4 жыл бұрын

Hallejulia, Thank you very much! for me, it*s been several days of searching and trying

@SebAnt 7 жыл бұрын

Excellent tutorial - Very clearly explained!

@abinayas6151 5 жыл бұрын

Awesome 👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽👏🏽👍🏽... I was searching for this.. for a long time.. thank you so much 👍🏽

@venkyrsboddupalli3380 7 жыл бұрын

An awesome trick explained in simplest way. thanks bro.

@islambek2712 5 жыл бұрын

This is a great and very informative explanation. Thanks a lot.

@boehringervietnam2676 4 жыл бұрын

Such a great demo and clear explanation.

@merveilmeok2416 6 жыл бұрын

This is one of the most magical appplications of Excel (non VBA.)

@ambadas.kulkarni2021 5 жыл бұрын

Thank you very much for sharing ... Very nice teaching ..

@shenporscha5439 7 жыл бұрын

Thank you for the video , that was very simple and useful video that I have seen ever

@paulord603 6 ай бұрын

This was brilliant. Thank you for sharing.

@nikunjpatel4142 4 жыл бұрын

Awesome Trick instead of making so much combination of formulas

@AJJmusician 7 жыл бұрын

BRILLIANT!!!! Thanks a million for this!

@rizwanaslam5131 8 жыл бұрын

WONDER FUL EFFORT BY TRUMP EXCEL.KEEP IT UP NEED SOME MORE DEMOS

@ananadkumar2474 6 жыл бұрын

Awesome trick. I have always used your trick in my workbook.

@dritannaum4360 9 жыл бұрын

Supper explanation, and very helpful file

@Kadi14dz 5 жыл бұрын

Thanks ...as always Indians are number one

@Bartraam 4 жыл бұрын

Great video. Thank you so much for this. Just wondering how would I go about if I wanted to filter both by Country and Product Name ? Or I want to filter using all 3 columns ?

@asifkhurshid9208 3 жыл бұрын

Excellent demo...Thanks and kind regard.

@bassamthebian9461 2 жыл бұрын

Thank you, just what I was looking for, it helped me great.

@mobr. 5 жыл бұрын

Perfect as usual, thank you.

@aliobtel5617 7 жыл бұрын

Thanks for the efforts made to make this lovely and helpful video available. Any idea how to apply the combobox and all its activeX controls on excel Mac?

@yerrichardson9349 8 жыл бұрын

Thank you for the video. What if I want to extract any data that I type in the combo box, what would the formula be? Thanks.

@garrensoutar2649 4 жыл бұрын

Thank you for sharing, this is fantastic.

@indianprofiles 5 жыл бұрын

Garda uda diye ho bhai...Gazab 👌👌👍👍

@chbelairful 3 жыл бұрын

Thank you, exactly what I was looking for

@srvithal38 5 жыл бұрын

Thank you..Thank you ..Thank you veryyyyyy much brother. You have explained crisp and clear. Very useful knowledge sharing. It helped me very much

@jaymungur8616 5 жыл бұрын

Hello bro...am doing a similar task..but cannot do it...please can u help..I will send u the worksheet...plz

@ritcherteofilo 5 жыл бұрын

This solved my problem. Thank you sir!

@next-hh1et 5 жыл бұрын

Great Man! Thanks, very understandable explanation!

@TheEngineerbasel 4 жыл бұрын

Thanks, It was very very vey very helpful

@ashokanselliah9571 4 жыл бұрын

Excellent video presentation. Hugely helpful Sir, Many many thanks for this..

@santoshtodkar4463 3 жыл бұрын

On point & top of that your English accent can be understandable easily it's lime cheery on the cake that's made to like share & offcourse subscribe your channel fall in love with excel bro thank you so much god bless you..

@claudiac333 7 жыл бұрын

Thanks for the tip you saved me a lot of work

@purnimasand9443 7 жыл бұрын

thanks Sumit..ur videos are really helpful..

@aealex5589 4 жыл бұрын

all the videos are very useful thank you so much

@mustafesalaat6401 5 жыл бұрын

Very help full video thank you :)

@jameshaviland3550 8 жыл бұрын

Wonderful video, thanks for posting!

@panagiotisfessas3709 7 жыл бұрын

Thank you very much! That's very useful! However, it seems that the moment I use my arrow keys to navigate among possible choices, excel freezes (excel has stopped working..). btw when I use the mouse to select, everything works fine. I have already added a piece of code that I found on the internet, which is supposed to navigate the list according to the arrow keystrokes, though I am still getting the same error (excel has stopped working..) Any ideas? Thanks in advance!

@mardangga 6 жыл бұрын

Thanks for your video. It's help me solve my problem. You're a lifesaver :)

@shamanpj Жыл бұрын

Excellent and very helpful video. Thanks :) An additional question: IF you have a really big data range, is there a way it can automatically scroll to all matching results, or at least tell you the number of instances / matches found?

@seoklee8 2 жыл бұрын

Very clear explanation

@chandanlimma3443 5 жыл бұрын

Awesome sir. Nice explanation. But... I am having problem with combobox middle center alignment while enrage the combobox size. Please suggest. Many thanks.

@leoceles1987 8 жыл бұрын

Hello! Great Videos! I have a question about the search results, how can I link the search result to its corresponding sheet?

@usmanamin6838 6 жыл бұрын

Realy Thanks its very helpful. What if we have to add more criteria by adding one more drop down list? how it will work

@M0RpH3dSaTaN 10 ай бұрын

Wonderfully Done, Cheers mate!

@honminglim4301 6 жыл бұрын

I would like to extract 7 column of data when I type in the combo box. Is the formula possible to make it ? Thank you

@Avibad4u 5 жыл бұрын

Nice video. Lots of things to learn

@khanabdussabur8604 5 жыл бұрын

excellent tutorial.

@gregsti 8 жыл бұрын

I have loved it !!! thank you so much !

@merveilmeok2416 7 жыл бұрын

This helps a ton! Blessings.

@rimazsplash1 6 жыл бұрын

Hi sir...Its working very fine and how handle large data like 10K rows with 15 columns? its getting stuck in data base

@jjelpesocondor6591 5 жыл бұрын

hey Trump Excel.., your video helped me a lot...! so thank you very very much...!!👍 👍 👍

@prakashsrinivasan7840 Жыл бұрын

Terrific video Sir Thanks for sharing 👍😀

@mirrrvelll5164 3 жыл бұрын

Amazing video, amazingly simple actually. Was looking smth similar, and usually is with VBA..or dynamic arrays (but when you dont have O365 then..)

@betovil4 8 жыл бұрын

Im having troubles replicating it on Excel for Mac do you any tutorial for mac?

@kudykhovillareal1500 4 жыл бұрын

Thank you sir you make my job Compatible!!!!!!!!

@vikasreddy2049 4 жыл бұрын

Bro,thank u for educating me.Keep doing🤗

@rajaeeengp 5 жыл бұрын

Hi I need a the same kind of function with additional drop down filters ... for example in this table if i select India, i should also have a drop down list box to select sales rep and so on ( when the columns are large)