Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles filter google sheets Filter in Google Sheets – Excelchat

Filter in Google Sheets – Excelchat

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.  

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc