# How to Use FILTER Function in Google Sheets

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

1. 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.
2. 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.
3. The `range` that you want to filter can be a single column or multiple columns.
4. The ranges that are used in the `condition1`, `condition2` … variables must be single columns.
5. 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.
6. 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.

1. 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:

1. 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.

1. 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.

1. Always separate the variables inside the function with commas ‘,‘.

1. 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).

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

1. 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).

1. 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.

1. 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. 🙂

### Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Read More

## How to Filter Integers in Google Sheets

This guide will explain how to filter integers in Google Sheets. Filtering in Google Sheets is a powerful…
Read More

## How to Count Cells with Text in Google Sheets

Knowing how to count cells with text in Google Sheets is useful if you want to return the…
Read More

## How to Use DAVERAGE Formula in Google Sheets

The DAVERAGE formula in Google Sheets is useful to get the average of values that meet specific criteria.…
Read More

## How to Use the SUBSTITUTE Function in Google Sheets

The SUBSTITUTE Function in Google Sheets is useful for situations where you need to replace existing text with…
Read More

## How to Use IFS Function in Google Sheets

The IFS function in Google Sheets is useful if you want to evaluate a set of expressions and…
Read More

## How to Wrap Text in Google Sheets

In this article, we’ll learn how to wrap text in Google Sheets. We’ll see the ‘Wrap’ option within…