The FILTER function in Google Sheets is **useful to filter and return the rows or columns in a range that meet the criteria we specify**.

##### Table of Contents

Filtering is one of the most powerful and most important features there is in Google Sheets.

The `FILTER`

function is used to filter the rows of a given range by the conditions of our choosing. We can filter based on one or more conditions.

Let’s take an example.

Say we have a list of food products. 🍅🧀

We would like to retrieve all products below a certain price as well as values that are in a specific category like vegetables, dairy products, etc.

**So how can we do that? **

It’s simple. The `FILTER`

function only needs the range where we are filtering and the conditions that we need.

As a result, we get a new data set which only shows the rows/columns from the original data set that meets the conditions set in the formula.

Now before we dive into some real examples, let’s understand how the FILTER function in Google Sheets works.

## The Anatomy of the FILTER Function

The syntax of a function specifies how we should use the function. For the `FILTER`

function, the syntax looks like this:

=FILTER(range, condition1, [condition2, ...])

Let’s break this function down and understand what each of the terms mean:

`=`

the equal sign is how we start every function in Google Sheets.`FILTER`

is our function. We will have to add the following variables into it for it to work.`range`

is the data to be sorted.`condition1`

is a column or row containing**TRUE**or**FALSE**values corresponding to a selected column or row of the range. It can be an array formula evaluating to TRUE or FALSE too. At least one condition is required to use in the function.`condition2`

,`...`

are additional conditions where their use is**optional**. You may set more than one conditions that should be checked.

### ⚠️ A Few Notes to Make Your FILTER Function Work Perfectly

- As a condition, you can define any formula, that returns a
**TRUE**or**FALSE**result. You may use simple operators (**=, <, >**), but even complicated expressions with several functions in it. - You are not allowed to use both row and column conditions in the same formula. To filter both rows and columns, you can use the return value of one
`FILTER`

function as the range in another. - The
`range`

that you want to filter can be a single column or multiple columns. - The ranges that are used in the
`condition1`

,`condition2`

… variables must be single columns. - Each condition should be of the same size as the range. In other words, the source range and the range for the condition must contain the same number of rows/columns.
- You have to make sure that there is enough empty area for your filter results. Before writing the function, select a clear area in your sheets where you can put your results.

Having now gained some brief insight into FILTER functions work, let’s jump right into some examples where we can write our own FILTER functions.

## A Real Example of Using FILTER Function

Have a look at the examples below to understand how to use the FILTER function in Google Sheets.

### FILTER Function with One Condition

In the first example, we defined one condition to filter by.

We have the list of food products, and we wanted to filter those that are in the “**Dairy products**” category.

The following formula will do the job:

=FILTER(A2:C11,B2:B11="Dairy products")

As a result, we get a new table with the filtered products that meet our criteria.

Here is what this example does:

- We selected a cell where we wanted to put the first result of our filtered new data set. Here we wrote the formula in cell
**E2**. - We wrote a
`FILTER`

function with two variables. The first variable is the`range`

, that is all the data set we have in the range**A2:C11**. - Then, we added the only criteria, called
`condition1`

. As we are filtering for categories, the range of the condition is**B2:B11**where the categories are. - To filter only the dairy products, we used an equality check as our
**TRUE**or**FALSE**condition. We added**=”Dairy products”**to our condition to check whether the cells in the range**B2:B11**match this text. - We got our filtered products that match the condition. As you can see, the two products (
**Cheese**and**Milk**) are the only dairy products in the original list.

Super easy, right?

You can make a copy of the spreadsheet using the link I have attached below and have a go at it yourself:

### FILTER Function with Multiple Conditions

You can understand from the syntax of the `FILTER`

function that it is possible to filter by multiple (two or more) criteria.

Let’s use the same food products and say we want to retrieve the products which are **fruits** and their **price is below 5**.

The `FILTER`

function with more conditions is as follows:

=FILTER(A2:C11,C2:C11<5,B2:B11="Fruits")

It works exactly the same way as the first example.

The difference is that we added two conditions as variables:

**C2:C11 < 5**means that we filtered the products by their price, and only kept those that have a price lower than 5.**B2:B11=”Fruits”**means that we checked for the Category column and filtered the products that have “**Fruits**” in it.

The filtered results meet both of the criteria we set, so their prices are below 5, and they are in the “**Fruits**” category.

We can add even more conditions. The only important thing is that these conditions should be **TRUE** or **FALSE** expressions, and the size of the ranges should match the size of the original range.

## How to Use FILTER Function in Google Sheets

Let’s begin writing your own `FILTER`

function step-by-step.

- Before starting it, you need to decide where you would like to put your filtered data. For this guide, we will make sure that the area
**E2:F11**is empty before starting to work with the`FILTER`

function:

- Now start your
`FILTER`

function in the first cell of your emptied range. Click into the cell to make it the active cell and start typing`=FILTER(`

. We will write our formula in the cell**E2**.

- After the opening bracket, you have to add the first argument. The range will be the whole unfiltered data set, so in our example, the range of
**A2:C11**. You can type it or more easily, highlight the range with your mouse.

- Always separate the variables inside the function with commas ‘
**,**‘.

- Then, add the first condition you want to define. You should select the range of the condition and then add the criteria. For this guide, I will be selecting the range of prices (
**C2:C11**).

- After that, add the condition itself. We will be adding the condition less than five, so
**<5**.

- Now you may add more conditions if you want to. Just follow the same way as before. In our example, we added a second condition to filter the products that are fruits. First, we selected the range of the condition (
**B2:B11**).

- Then add the condition that you want to filter by. We added the condition
**=”Fruits”**. Make sure to use double quotation marks if you use string values in the conditions.

- Finally, after you have added all the conditions, close the brackets ‘
**)**‘ and then hit**Enter**. The result is the filtered new data set with only the rows that meet your conditions.

That’s it, well done! You can now use the `FILTER`

function together with the **various other Google Sheets formulas** to create even more useful formulas. 🙂