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