Filter Function in Microsoft Excel 365

How to use the filter function in Microsoft Excel 365 by applying multiple criteria.

Watch the video below:

Filter Function in Microsoft Excel

We can use the FILTER function to filter a range of data based on our criteria. This filter function has been made available to Microsoft 365 users since July 2020.

We have already learnt how to use AutoFilter and the Advanced filter features in Excel. We have also learnt how to use both Auto and advanced filters in combination with VBA.

We will use the FILTER function on a set of data that has Region, Sales Rep, Product and Units Sold as headers. Let’s say we wish to know how many laptops were sold in different regions. We first select our data without the headers. Next we copy the same headers next to our data. Under the header Region we write the filter function as: = FILTER(A5:D20,C5:C20=G2,””)

When we start entering ‘FI’ we receive the option FILTER and we press the tab key. In brackets we write the data range(A5:D20) or we select the data range. This is our ‘array’ parameter. For the include parameter we select C5:C20 which represents the PRODUCT data and we assign it the value from Range G2 which represents ‘LAPTOP’. For the parameter ‘if_empty’ we write two quotes without a space. We complete our Filter function by closing the bracket and pressing the Enter key. Now we observe how our data has been filtered based on the criteria ‘Laptop’. We get the relevant sales of the product laptop in the West and North regions.

What if we wanted to filter or analyze our data based on multiple criteria like a specific product and a certain region? We would write the FILTER function like this:

=FILTER(A5:D20,(C5:C20=G2)*(A5:A20=J2),””)

Array=A5:D20

PRODUCT = G2 =Laptop

*=AND

J2=Region=West

So we use the * for AND. That is now filter the data based on the two criteria Laptop and West. Now our data has been analyzed for Laptop and West region and we observe a single row of data.

We can simplify the criteria entries by not selecting specific ranges but writing our criteria as follows:

=FILTER(A5:D20,(C5:C20=”Laptop”)*(A5:A20=”West”),””)

That can make our working quicker and easier.

FILTER function in Excel
FILTER function in Excel

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.