January 28, 2021

# Filter Function in Microsoft Excel 365

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

Watch the video below:

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.