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