Excel Pro Trick: Filter Data Dynamically with Excel FILTER Function

Excel Pro Trick: Filter Data Dynamically with Excel FILTER Function - How to Tutorial

Рет қаралды 2,036,689

Жыл бұрын

Excel allows us to filter data with a dedicated tool. We can even access it with an Excel shortcut. However, if we have to use the filtered data for further processing, it is not of much help.
This is where Excel FILTER Function helps us in writing Excel formulas that can get us filtered lists or filtered data. Excel filter function makes the whole process extremely easy as this function is an array function and data will simply spill over to cells to accommodate all the matching results.
In this tutorial, we are learning how to use Excel FILTER Function with an example to get dynamic filtered lists in Excel.
Lets Excel!
Access or Download Practice workbook for this Excel tutorial: 1drv.ms/x/s!Apno7889bCyLj1EYv...
For more Excel tips, tricks and tutorials don't forget to subscribe to @Learn Excel to excel YT channel.
Learn #Excel your way:
===================
Website: learnexceltoexcel.com/
--------------------------------------
Pick your favourite social page:
Fb: bit.ly/3yM3dvd
Tiktok: bit.ly/3wFllpf
Tw: bit.ly/3wGCRYU
Pin: bit.ly/3NsjKIU
Yt: bit.ly/3wxKL8a
Insta: bit.ly/3wtT9oW
Music by: / ikson
#removeduplicatesexcel #exceltutorial #exceltips

Пікірлер: 336

@LearnExceltoexcel Жыл бұрын

Access or Download Practice workbook for this Excel tutorial: 1drv.ms/x/s!Apno7889bCyLj1EYvIsJh92wI36F?e=7j096d

@prernasingh5055 Жыл бұрын

what if f4 does not lock the range??

@luisorozco4593 4 ай бұрын

@@prernasingh5055do it manually

@tr911tr Жыл бұрын

Ctrl+shift+down arrow while selecting range is a useful shortcut :)

@LearnExceltoexcel Жыл бұрын

Yes!!!! Hi5!!!! And to get back to first cell of the range hit CTRL+BACKSPACE 🤯

@tr911tr Жыл бұрын

@@LearnExceltoexcel thanks! New learning!!

@dudegaming7302 2 ай бұрын

Ctrl+a is best for whole selection.

@nuclear9977 Ай бұрын

@@LearnExceltoexcelhey you are teaching all my hidden tricks 😢

@anhhoangminh9474 27 күн бұрын

Thanks you.

@evanlane1690 Жыл бұрын

Best part of this tip is the lock range shortcut! I've been looking for it for years, but didn't know the right name for it. Thanks!

@LearnExceltoexcel Жыл бұрын

Wow :) Glad you unlocked something special today. It is indeed one of the tiniest bit in Excel that is of great use. Happy Excelling!

@accounting.finance Жыл бұрын

Can put u file

@rohitganjoo4634 Жыл бұрын

F4 is the short cut

@bobbarker142 Жыл бұрын

For years? Jeez man

@efisgpr Жыл бұрын

😂 me too... always put the $'s in manually

@akhieleshsrirangam Жыл бұрын

This channel is a life saver, cause learing from shorts is much easier than long videos!!!

@eurika297 Жыл бұрын

I'm Such a huge a fan of this channel. No voiceover, just straight demo. Love it ❤ Whenever I scroll KZfaq shorts, I come across this channel then learn something new 😊

@Bhavik_Khatri 3 күн бұрын

Nice little trick

@user-fe3ok8ms8w Жыл бұрын

it just poped up on my tiktok vids.. this is my very first time subscribing on tiktok videos...worth it..thanks..

@LearnExceltoexcel Жыл бұрын

Bucket load of encouragement just when it's needed :) hi5!!!

@mr.chandanmanna2478 Жыл бұрын

Thanks Sir/Ma'am Like it Very Much. Your Simplicity of Explanation🙋‍♂️

@LearnExceltoexcel Жыл бұрын

Glad it helped.

@at8537 Жыл бұрын

Finally, a nice somewhat cross pivot text function . Thanks for sharing

@LearnExceltoexcel Жыл бұрын

Yes! I still believe this function should have been given more 'powers' but still what we have is no small thing!

@samiulbariavick9520 11 ай бұрын

Advanced Filter is also useful!

@JesusReigns7 Жыл бұрын

Nice!

@123456Bukhari Жыл бұрын

Why don’t you use pivot/unpivot columns in power query?

@kodetrek4247 3 күн бұрын

Excellent job!! Btw, did you make this video from phone or pc screen? Thanks

@ExcelLagi Жыл бұрын

Wow, superb trick!

@agirmas Жыл бұрын

Thank you a lot. I was using IFS command for 400 row list so I had to add each specific row from the list into script.

@LearnExceltoexcel Жыл бұрын

😳😳😳😳😳😳😳😳 What monstrosity were you upto 🥺

@excelmixture 4 ай бұрын

Great job 👏

@ilove80100 5 ай бұрын

Brilliant ❤

@mamamo8518 Жыл бұрын

Best solution for that is pivot table and also it helps you to enhance the file size of your file

@LearnExceltoexcel Жыл бұрын

And what if you want to use the data in formula??? Will pivot table help? Pivot table has its place where we need to summarize the data. If we need to process a filtered data, FILTER function is one of the best thing happened to Excel. Sending another tutorial with FILTER function in few minutes your way!

@juwelmollah8463 Жыл бұрын

এরক ভিডিও ভালো লাগে ভাই, সহজে শিখতে পারি

@princebabu3265 Жыл бұрын

Very useful. Thank you 🙏

@LearnExceltoexcel Жыл бұрын

Glad it helped

@mdmarufahammed1695 Жыл бұрын

❤This is great Sir ❤❤

@tommyharris5817 Жыл бұрын

Just brilliant

@LearnExceltoexcel Жыл бұрын

🥳🥳🥳🥳

@mathemagiciancreator7921 Жыл бұрын

love your skills ...

@LearnExceltoexcel Жыл бұрын

🎉🎉

@JITENDRAKUMAR-bm7ss Жыл бұрын

Your video is awesome and easy steps

@LearnExceltoexcel Жыл бұрын

Glad you liked it

@tommyharris5817 11 ай бұрын

Nice one

@petlover8195 Жыл бұрын

Best 👌

@Laibacreative299 7 ай бұрын

Amazing

@ishanchandna4203 Жыл бұрын

Filter function works only in MS Excel 365

@makeshkathayath8008 4 ай бұрын

It also works on Google sheets

@SandeepSingh-wo9go 2 ай бұрын

Yes I tried it in old version and got error.

@wanggang2396 11 сағат бұрын

I really like your video and I would like to know how the two animations that are incorrect and correct are made in the video ✅❌

@bijaytanty8434 7 ай бұрын

Thanks

@arslanplayzz Жыл бұрын

Keep it up

@LearnExceltoexcel Жыл бұрын

Thank you and stay blessed

@jayeshkumar5406 Жыл бұрын

Super 🎉

@rbfreitas 5 ай бұрын

Great video

@LearnExceltoexcel 5 ай бұрын

Glad you enjoyed it

@agussusanto7028 5 ай бұрын

Thx

@mpscqa7387 Жыл бұрын

awesome

@TamilTop5 7 ай бұрын

Nice

@kirantp3281 2 ай бұрын

Great song 🎉

@iordachecristian5930 2 ай бұрын

Genial🎉

@gandhingira4071 Жыл бұрын

Thank you

@LearnExceltoexcel Жыл бұрын

Pleasure! 😊

@dheerajpundir3065 4 ай бұрын

Wow...

@Farsath999 6 ай бұрын

Superb

@LearnExceltoexcel 6 ай бұрын

Thanks 🤗

@Aarushhhhhh 4 ай бұрын

🙏 thanks

@reyt.1967 26 күн бұрын

THANKS

@LearnExceltoexcel 26 күн бұрын

🎉

@samirtahboub2273 Жыл бұрын

Super

@lovellyboy7950 Жыл бұрын

Proof that is practice

@ExcelBaba 7 ай бұрын

nice

@bidhanrai3729 Жыл бұрын

❤️ you brother

@LearnExceltoexcel Жыл бұрын

🤩🤩🤩

@dougmphilly 5 ай бұрын

slick

@user-uq3ip8hc3y 3 ай бұрын

Use captions for better understanding ❤

@wrkarnage2864 Жыл бұрын

One not having version of Excel in which we don't have this filter function...then we can make use of INDEX AND AGGREGATE FUNCTIONS or INDEX WITH HELPER COLUMNS

@LearnExceltoexcel Жыл бұрын

I will suggest to use Power Query. Much more flexible and powerful. Much easier to use as well.

@softwaredynamicsolutions Жыл бұрын

Best video 👍

@LearnExceltoexcel Жыл бұрын

Thanks

@softwaredynamicsolutions Жыл бұрын

@@LearnExceltoexcel welcome

@ahamedirsath8924 11 ай бұрын

I am thinking about the filter formula...now I got it...

@juwelmollah8463 Жыл бұрын

সুন্দর

@hussain9598 Жыл бұрын

Nyce

@nagarajunagarajmnr Жыл бұрын

Super dear 💕

@LearnExceltoexcel Жыл бұрын

♥️♥️♥️

@dilsaifi2563 5 ай бұрын

🔥🔥🔥🔥🔥

@tufangamer7910 Жыл бұрын

Song name batao bhai

@motivation_m00d 7 ай бұрын

B true 😊

@ravinshetty57 9 ай бұрын

Wow

@SAJSANAL Жыл бұрын

How to seperate letters and digits one colum to another

@GOD-EX 2 ай бұрын

i think you can just use filter tool like drop down in Department

@pyybanez2926 8 ай бұрын

So which is better? =filter or =vlookup function?

@sza2269 Жыл бұрын

Kanye being in HR literally gave a spike in anxiety. 😅

@LearnExceltoexcel Жыл бұрын

😆😆😆

@LearnExceltoexcel Жыл бұрын

Arnold doing IT is no small thing either 😂

@goodboy6724 Жыл бұрын

👏👏👌👌👍👍💯

@aarifhassan901 3 ай бұрын

Where i can download this practice sheet ?

@prasenjitbanik1872 5 ай бұрын

U shld hv placed Number on top of each person before shuffling, may be after that u can find out

@mahendrasugga1010 9 ай бұрын

I have data in a single column that, some cells having 1 mobile number & another some cells having 2 or 3 mobile numbers in the single cell, What to do if I need a single mobile number in every single cell sequencely. (The cell which has multiple mobile numbers should split & every mobile number should come separately in below cells)

@mortal7862 Жыл бұрын

And add any function in Excel then harmful security show this msg. how to add any function?

@subbareddybommu1113 Жыл бұрын

👌👌👌

@two-four-five_trioxin124 Жыл бұрын

Why not just sort the table by detartment, then alphabetically by team. Then copy and paste the lists of names over?

@taimoor0315 4 ай бұрын

I know, bro. If one can realize the uses of sorting, it will make his life and work easier

@danielsimbikitobo1948 Жыл бұрын

Magnifique

@LearnExceltoexcel Жыл бұрын

🤩🤩🤩

@mortal7862 Жыл бұрын

How to add filter function in Excel

@deepadeepa6622 Жыл бұрын

Which song is this please give a link .

@LearnExceltoexcel Жыл бұрын

Ikson remix named runaway

@titonecarter2071 2 ай бұрын

What's the title of the song?

@PremKumar-rf3mo Жыл бұрын

Wowow

@nagappabiradar7464 Жыл бұрын

We Can Use a Filter Like just Do Ctrl+shift+L in Dept Column, & Copy the IT files & so on........ Easy naa

@informationsatellite5155 6 ай бұрын

Wts that background music? Explain it or leave with a simple ringtone

@rohandas8940 Жыл бұрын

Please try to provide attachment of the problem it would be easier to practice

@LearnExceltoexcel Жыл бұрын

Providing fir the latest ones. But will try to pull this one as well and make it available

@sagarbairwa3177 Ай бұрын

Sir mujhe basic aur advance Excel sikhana hai per day 1 hour study Karun to kitne manth lagega

@AnitaM90 Жыл бұрын

I legit try to create filters... but more with dynamic tables. This one seems legit fun to learn. Do you have a longer version for multiple columns attached to 1 category?

@LearnExceltoexcel Жыл бұрын

You want to fetch values from multiple columns or get multiple columns against filter applied to one column? 🤔

@ryzagonzalez9013 Жыл бұрын

@@LearnExceltoexcel 0

@denisuryono9287 Жыл бұрын

Apakah ini berhasil di WPS ?

@crizchelle12 Жыл бұрын

Wondering if you can do that in reverse? Meaning from several dept column into name and dept column only.?

@LearnExceltoexcel Жыл бұрын

Means you want to get the department name for each employee?

@crizchelle12 Жыл бұрын

@@LearnExceltoexcel ya, just like the columns F and G on the right.

@pallavijoshi2193 11 ай бұрын

This filter option is not shown on my pc when I try to do it

@JonRusson Жыл бұрын

What about when I add data to F & G? Does it update dynamically?

@LearnExceltoexcel Жыл бұрын

Having the source data range in the form of a table will make the solution completely dynamic and it will update it entries or changes are made.

@alejandrosalcedo6148 2 ай бұрын

Is this possible to do this if the information is in one cell? Like if Benedict Customer Care was in one cell could I create a filter to split the info into two columns? Lol I have a few hundred names I have to manually separate and I am not Microsoft proficient in any way😅

@LearnExceltoexcel 2 ай бұрын

Are they separated by comma or space?

@praving9046 Жыл бұрын

In which version of Excel we can use filter function, as i tried it in my system it suggested filterxml only

@LearnExceltoexcel Жыл бұрын

As per support documents. 2019 onwards version support FILTER function. But Excel for web and Android/iOS apps have this formula. And you can use these facilities for free.

@ennamarine5464 Жыл бұрын

Same here

@rauscaca5376 Жыл бұрын

How can I save this video? Haha. Does it only run for exact array? or maybe (example : there is contain "Finance") we can get all kind of finance?

@LearnExceltoexcel Жыл бұрын

There should be a way to download shorts in KZfaq app. We can have multiple columns output. Tomorrow's video will be about it :)

@LearnExceltoexcel Жыл бұрын

Or if you have a different idea then let me know and will try to incorporate it in my example

@KocakLu14 11 ай бұрын

Filter Function Exist which Version ?? I use excel 2016 and its dont appear fillter function

@90hijacked Жыл бұрын

Not a vlookup? interesting! Thanks

@LearnExceltoexcel Жыл бұрын

FILTER is basically a lookup function. So you can say that it is a special kind of VLOOKUP

@duncanotieno3968 Жыл бұрын

What about Index and MATCH function

@LearnExceltoexcel Жыл бұрын

Can't spill multiple matching values. Way too complex to get the job done. FILTER function is a lot easier to use and less prone to breaking. If one dont want to use filter function. Power query is the second best option

@zohrain9083 Жыл бұрын

Whenever I press f4 some other setting open cell does not lock I am laptop user please tell what I have to do for locking the cell instantly

@LearnExceltoexcel Жыл бұрын

Your laptop probably requires pressing "Fn" key as well for function keys to work.

@ammukutty809 6 ай бұрын

How to join this job

@ofuresusan6808 Жыл бұрын

Hello, after I've successfully fill individuals in their departments, I can't get it to flash fill other individuals into their perspective departments. Please help

@LearnExceltoexcel Жыл бұрын

Flash fill is quite limited in recognizing the data patterns. Better use FILTER function or lookup functions to get this done.

@Mallikarjun024 Жыл бұрын

How can I make ascending or descending order using filter function

@LearnExceltoexcel Жыл бұрын

You need to wrap this whole formula under SORT function. That way the results will be sorted in desired order. Hope this helps.

@pv8929 Жыл бұрын

Xlookup?

@LearnExceltoexcel Жыл бұрын

Yes can be done with any lookup function including XLOOKUP. Even FILTER function is a lookup function. But FILTER function is the simplest of them all to get this job done. Just two augments. And closest thing to Excel Filters.

@moneydata4134 Жыл бұрын

Hi i want to filter random multiple values from from filter atleast 100 how could I please help

@LearnExceltoexcel Жыл бұрын

For such cases power query is better suited.

@brendamg7298 11 ай бұрын

❤❤

@Kapildev_N._Pol Жыл бұрын

you must provided a practice excel sheet the same page you show in the video, so that we can practice at that time it self, so from next time give a download link of that excel page, if we don't practice at the same time you show we will eventually forget it

@LearnExceltoexcel Жыл бұрын

Done! Practice file for this tutorial is now available. You can access the practice workbook from the description or in the pinned comment. This is from the older videos. Have already started providing practice files for recent tutorials. Thank you for stopping by and Happy Excelling!