Normally, we filter our data using the Sort and Filter tool where we have to click each filter arrow in each column to organize our data.  While working in Google Sheets, we can use a unique function called the FILTER function.  It has the ability to filter values in multiple columns using only one formula.

Figure 1.  Final result:  How to filter in Google Sheets

How to filter in Google Sheets

In order to filter data using the FILTER function in Google Sheets, let us follow the step-by-step procedure below.  

Example 1

Suppose we have below sales report and we want to filter the values with sales greater than 1000.  Here are the steps:

  • Enter this formula in E4: =FILTER($B4:$C,C4:C>1000)
  • Press Enter

Figure 2.  Sample data

The formula will return all values that satisfy the condition C4:C > 1000.  The FILTER function in Google Sheets is very powerful.  By entering only one formula in one cell, it will return the rows of data that meet the criteria.  

Figure 3.  Output: Filter with one condition

Example 2

We want to obtain the values whose sales is greater than the average.  In cell H4, we enter this formula:

=FILTER($B4:$C,C4:C>AVERAGE(C4:C))

Figure 4.  Output: FILTER formula with AVERAGE function

Filter Multiple Criteria

Can FILTER function handle multiple criteria?  Yes, it definitely can.

Example 3

In the next example, we want to obtain the values for sales between 500 and 1000.  We simply enter this formula in E4:

=FILTER($B4:$C,C4:C>500,C4:C<1000)

Figure 5.  Output: Filter Multiple Criteria

This is an example of how to use the AND logic in FILTER function.  Each criterion is added into the formula, separated by a comma. As a result, only the rows of data with sales greater than 500 and less than 1000 are returned in columns E and F.  

Filter Multiple Columns

FILTER function can also be applied to multiple columns.  

Example 4

We want to filter the dates in 2018 with sales greater than 1000.  In cell H4, enter the formula:

=FILTER($B4:$C,YEAR(B4:B)=2018,C4:C>1000)

Figure 6.  Output: Filter Multiple Columns

Again, we apply the AND logic, wrap the first condition with the YEAR function and add the second condition into our FILTER formula.  

The FILTER function has so much potential.  It can even be used to filter values that contain text and can handle data with several columns.