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

The FILTER Function in Microsoft Excel is possibly MY FAVOURITE function in Excel. It's DEFINITELY my favorite Dynamic Array Function!

Available in Excel 365 and Excel 2021, this little function allows us to filter a dataset and place the results anywhere in the workbook. And, because it is a formula, we can combine it with other functions like SORT, SORTBY, COUNTIF etc. to create more complex formulas.

In this video, we are going to walk through the following:
- FILTER Function Basics
- FILTER with AND (*) Criteria
- FILTER with OR (+) Criteria
- FILTER with One or the Other but not Both (-) Criteria
- FILTER with Both or Neither (=) Criteria
- FILTER Non-Adjacent Columns
- Using FILTER to Compare Lists

********************************
Chapters
00:00 - 01:00 - Introduction
01:00 - 07:52 - FILTER Function Basics
07:52 - 10:05 - The FILTER Function with AND (*) Criteria
10:05 - 11:57 - The FILTER Function with OR (+) Criteria
11:57 - 14:33 - The FILTER Function with One or the Other but Not Both (-) Criteria
14:33 - 16:16 - The FILTER Function with Both or Neither (=) Criteria
16:16 - 17:01 - FILTER and SORT
17:01 - 21:34 - FILTER Non-Adjacent Columns
21:34 - 23:31 - Compare Lists with FILTER, COUNTIF and NOT
23:31 - 23:41 - Outro/Close

********************************
DOWNLOAD THE WORKBOOK

********************************
🔗LINKS to related videos

📈Create Pivot Table Report Filter Pages

🤔NEW Excel GROUPBY Function - How does it work?

💪🏻 Create a Connected Dot/Dumbbell Chart in Excel

************************************************

🤝Let's CONNECT on social:

[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!

[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.

[muhammadasad]

Very nice update for unique values in drop-down.

[nongartinunt]

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

[windsurf-journey]

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.

[Ezra-bw]

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

[MichaelBrown-lwkz]

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.

[manosvenianakis]

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..

[clivegamble]

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

[stevereed]

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

[annietaylor]

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

[grahamsopp]

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

[jackcurl]

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.

[AntoF]

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.

[weiwang]

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

[felge]

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.

[jayarajmarydasan]

Thank you thank you so much for this video

[deepakadroja]

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

[anphan]

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

[AntoF]

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!