The FILTER Function in Excel + Bonus Tips - COMPREHENSIVE TUTORIAL!

[@saumilparadkar8636]

Hello Deb, Thanks for the detailed video on the filter function and also the logical operators... I do struggle with them but this video has been extremely helpful to understand... Also is quite helpful in my office tasks... Keep up the great work 😊

[@cellmatestraining]

@@saumilparadkar8636 thank you!

[@liquidapathy82]

That's right, there are plenty of filter videos out there but definitely not a lot of good videos discussing all the ways to use filter. This was an amazing educational video. Thank you!

[@cellmatestraining]

@@liquidapathy82 thanks so much!

[@Luciano_mp]

I've never seen someone explore the filter function in depth! Your explanation is brilliant. I recognized your voice and then I saw your name, you teach on another channel! Thank you very much!

[@cellmatestraining]

@@Luciano_mp amazing! Thank you so much for the brilliant feedback! Yes that’s me on ‘the other’ channel :) thank you for watching - appreciate it!

[@captvo]

Thank you so much for the in-depth coverage of the FILTER function. You’ve got a new sub! The new feature of data validation automatically picking up the unique list of values is new to me and is indeed a very useful feature.

[@cellmatestraining]

You’re welcome and thank you for subscribing!

[@grahamsopp]

8:34 what you call curly brackets are just brackets or curved brackets () . Curly brackets are {}.

[@cellmatestraining]

Ah, you're right! Just a slip of the tongue, thanks for pointing it out though. Hope you enjoyed the video! 🙂

[@ExcelTL]

😂 I even zoomed in to make sure those were in fact NOT curly brackets.

[@grahamsopp]

@@cellmatestraining yes, always keen to learn more excel

[@nibzy6803]

I started with the chat GPT out of interest and ended up binge watching whatever came next. Thank you for the clear teaching method and delivery. Well-deserved subscription.

[@cellmatestraining]

Thank you so much!

[@stevereed5776]

I thought I knew quite a bit about the FILTER function but seems every day is a school day! Thank you for the video. New subscriber

[@cellmatestraining]

Awesome! Thank you and welcome!

[@MichaelBrown-lw9kz]

FANTASTIC. This is by far the best video I have watched on the "FILTER" function, and I have watched a number of them. Thank you so much, madam.

[@cellmatestraining]

@@MichaelBrown-lw9kz you’re welcome! And thank you so much for your kind comments :)

[@clivegamble379]

Brilliant, only found your channel today, fantastic content, comprehensive and so easy to follow.

[@cellmatestraining]

@@clivegamble379 amazing! Thank you and welcome! Appreciate the follow! :)

[@windsurf-journey3167]

Es hat sich für mich absolut rentiert ein weiteres Video zum Thema Filter-Funktion anzuschauen. Du erklärst sehr gut und strukturiert, alle möglichen Aspekte werden beschrieben. Vielen Dank und ich freue mich als neuer Abonnent auf Deine weiteren Beiträge.

[@cellmatestraining]

Thank you!

[@nongartinunt2014]

Thx you for your awesome work and simple but effective explanation. Keep following and recommending to other members

[@cellmatestraining]

Thank you so much! I really appreciate that 😊

[@annietaylor2898]

I had no idea of this filter with operations option! Excellent new tip & presentation!

[@cellmatestraining]

A lot of people don’t! 😊

[@silvanodemetrio407]

Great video. FILTER is a very useful function.

[@cellmatestraining]

Agreed!

[@muhammadasad729]

Very nice update for unique values in drop-down.

[@cellmatestraining]

I know right? No more messing around creating a unique list first!

[@westleyempeigne6541]

Great explanation Deb! Didn’t know we can use - and = operators

[@cellmatestraining]

Yes! Pretty cool, huh? :)

[@manosvenianakis9323]

Great video! Filter has replaced most of the other functions I use. Definitely my #1 function! For the part Non - Adjacent Columns, an other way is to use the CHOOSECOLUMN function or create 3 separate filters for each column..

[@cellmatestraining]

Thanks for the comment. Yes you could replace the first FILTER with CHOOSECOLS, if you have Excel 365. The function isn’t available in any other version :)

[@ExcelTL]

What versions include FILTER that DON'T include CHOOSECOLS? I didn't know there were any unless it's 🤮 Mac.

[@cellmatestraining]

@@ExcelTL Excel 2021.

[@cellmatestraining]

@@ExcelTL TIP: If you google the function and go to the MS site, it says the versions the function is available on at the top.

[@ExcelTL]

@@cellmatestraining Why would anybody use Excel 2021? I guess price, but not worth losing out on really useful features in 365.

[@iankr]

Brilliant! Many thanks. Very clearly explained. 🙂

[@cellmatestraining]

You're very welcome!

[@AdamLacey-d3n]

Such a good function!

[@cellmatestraining]

So good!!

[@ابوالايهمالصادق]

I love you, my beautiful teacher, how wonderful you are

[@Ezra-b1w]

Amazing explanation, thank you very much. Regarding selecting columns I would add the options to use the CHOOSE function or the CHOOSECOL function.

[@cellmatestraining]

@@Ezra-b1w yes I mentioned that in the video :)

[@MrCHATTERJEE01]

excellent tutorial 😊 🙏

[@JoseAntonioMorato]

Dear Deb, To filter non-adjacent columns it is easier to use the CHOOSECOLS function: =FILTER(CHOOSECOLS(Results,1,2,5),(Results[Exam]=H5)*(Results[Month]=H9),"No Records") 🤗

[@cellmatestraining]

@@JoseAntonioMorato hello, yes that’s mentioned in the video. You can use CHOOSECOLS if you have M365 only. It’s not available in any other version. FILTER is slightly more widely available.

[@Arulendran]

Brilliant, thanks.

[@cellmatestraining]

You’re welcome! :)

[@adnanozkara3576]

Crystal clear

[@cellmatestraining]

@@adnanozkara3576 great!!

[@worldofdata]

great video, thanks :)

[@cellmatestraining]

You’re welcome! Thank you for watching :)

[@jayarajmarydasan4242]

Thank you thank you so much for this video

[@cellmatestraining]

I'm so glad you got value from it!

[@daysihyland1804]

Nice

[@Luciano_mp]

Wow...Thak you!

[@cellmatestraining]

You’re welcome!

[@felge1977]

Hi! Great video! Although when I'm following it along, in the bit of the "One or another but never both", my results are different. I get people working in London with a profit greater than 40k in my filtered results. Same formula you used.

[@cellmatestraining]

@@felge1977 have you used the correct operator? Should be 40000 :)

[@theportervibes]

Thank you for your reply! Yes, you are right but how can I count specific rows in column D without adjusting its filter, while filters are applied in columns A, B, and C? Column D shows two different row counts unless I filter it manually. I know this isn't a private tutorial, but this would be useful for anyone analysing data. Also, I want to commend you on the excellent courses you’ve provided online. I try to watch them all when I can.

[@cellmatestraining]

@@theportervibes have you tried AGGREGATE with the ignore hidden rows option selected?

[@deepakadroja6065]

Quite useful for academic purposes. If I want a total of e.g.English how do I do ?

[@cellmatestraining]

@@deepakadroja6065 you would need the SUMIF or SUMIFS function.

[@deepakadroja6065]

@@cellmatestrainingThanks for quick response 🙏🪴🙏incorporate with Filter function?

[@jackcurl2005]

Selecting columns with a nested filter shows a nifty trick (the embedded array). However, you can only display the columns in the relative order of the original source data. Sometimes that's fine, but using CHOOSECOLS is much more versatile, allowing you to specify which columns to return and in what order. But then, that's not demonstrating the use of FILTER as a highly versatile/adaptable function.

[@cellmatestraining]

I agree! The vid is focused on FILTER but CHOOSECOLS is a better option if you have 365! Anything older and you don’t have the function!

[@vaidhyasiv]

Good explanation, I am benefitted by this tutorial. Thanks

[@cellmatestraining]

Glad to hear that

[@weiwang2000]

"choosecols" may be used to output non-adjacent columns in the filtered array.

[@cellmatestraining]

@@weiwang2000 hello, yes I mention that in the video. There’s an annotation that says you can use CHOOSECOLS. Need to remember that CHOOSECOLS is only available in Excel 365. FILTER is more widely available :)

[@sanjayTECH04]

Thanks

[@cellmatestraining]

You’re welcome!

[@patriotjam9709]

Love the video however adding the sort function to the filter function does not return "No Results" when no criteria is met, it returns #Value!. is there a trick to that?

[@anphan9275]

Hi, I have a question about creating batch folders in file explorer using excel. Are there any way to prevent the system from auto arrange base on alphabet (for example I have 4 names: d a c b, now I want to create batch folders in the exact same order d-a-c-b not a-b-c-d). Thanks

[@willemmulder7304]

In the part "Non-Adjacent filter, you used Filter(Filter. Why didn't you use Choosecol()? Is that not easier to understand (and to explain)?

[@bradosborne9299]

Mine does not auto the unique values, is that the version I am using from work?

[@theportervibes]

Dear Madam/All, I am working on a table and need to find the total number of rows in a column using a function, such as SUBTOTAL, SUMIF or SUMPRODUCT with the filter applied. However, the functions only work when the filter is not in use. When I apply filters to different columns, the results don’t update accordingly. How can I get the total number of rows with the filter applied? I would greatly appreciate any help or advice on how to get the total number of rows with the filter applied. Thank you!

[@cellmatestraining]

@@theportervibes hello, you need to use SUBTOTAL with COUNT or COUNTA. In a cell somewhere type =SUBTOTAL(3,A1:A10) replace my cell refs with a column in the filtered table.

[@ezelkarlkl1284]

Hi , example file is not same with video file. Could you update it again ?

[@cellmatestraining]

We have been changing the links to go through to my website. I’ve just tested it and it downloads the correct file for me. The link should be to cellmates website not Google drive. If you see Google drive, please refresh your browser.

[@jeniceprovost3322]

This is confusing - I don't understand/see the use of curly brackets?

[@cellmatestraining]

@@jeniceprovost3322 I said curly bracket instead of bracket. Follow what you see on screens

[@bernpatton8966]

I followed this but I get only 'Function is invalid'

[@cellmatestraining]

@@bernpatton8966 which version of Excel are you using? FILTER is only available on M365 and Excel 2021.

[@johank4361]

Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns i.e 20/11/2024 Diner to 24/11/24 Breakfast BF (Column) 4nos Lunch (Column) 3nos Diner (Column) 4nos If you give me a solution i will be very greatful.