Don’t forget to share this post

Google Sheets offers various options for filtering data, including the FILTER function. One of the benefits of using this function is that it returns a filtered version of the range of cells you select without affecting the original range. This also makes it useful for dashboards, as the filtering updates automatically when the sheet refreshes. The FILTER function can handle multiple conditions or criteria applied to one or multiple columns. Additionally, you can use it together with other functions to specify the conditions.

In this guide, you will learn about the FILTER function in Google Sheets and how to use it with different types of conditions. You will learn how to filter using other functions and comparison operators as conditions; for example, ISEVEN, ISBETWEEN, and DATE. You will also learn how to filter by text values, numerical values, and dates. Finally, you will review other filtering methods in Google Sheets.

Google Sheets FILTER Function

The FILTER function in Google Sheets returns a filtered version of the original array, which only includes the rows and columns that meet the relevant conditions.

The syntax for the FILTER function in Google Sheets is as follows:

=FILTER(range, condition1, condition2*,...conditionN*)
  • range: the range of data you want to filter.
  • condition1: a column or row and the condition it must meet to pass the filter.
  • condition2: additional conditions are optional and can apply to the same row or column as condition1 or to a different one. However, all conditions must apply to either rows or columns, but not both.

How to Use the FILTER Function in Google Sheets?

To use the FILTER function in Google Sheets, select the range you want to filter. After the comma, type in the column or row you want or the condition it must meet. For example, using =FILTER(A1:H1001,iseven(A1:A1001)) will only return rows with an even ID.

  1. 1. Type the FILTER function in an empty cell.
Google Sheets FILTER Function How To Examples Add FILTER Function
Google Sheets FILTER Function: How To & Examples - Add FILTER Function
  1. 2. Select the range of cells you want to filter, followed by a comma.
Google Sheets FILTER Function How To Examples Select Range
Google Sheets FILTER Function: How To & Examples - Select Range
  1. 3. Add the condition you want to use to filter. In this case, I want only the even IDs in the first column, so I’ll use the ISEVEN function on that column. Remember to close both sets of parentheses.
Google Sheets FILTER Function How To Examples Add Condition
Google Sheets FILTER Function: How To & Examples - Add Condition
  1. 4. Press ‘Enter’ to see the result.
Google Sheets FILTER Function How To Examples Filtered by Condition
Google Sheets FILTER Function: How To & Examples - Filtered by Condition

Google Sheets FILTER Function Examples

Now that you know how to filter using another function for the condition, it’s time to learn how to filter by text, by numerical value, and by date. After that, you will learn how to filter using multiple conditions on one or more columns.

How to FILTER by Text in Google Sheets?

Imagine you want to filter the table used in the previous section to only include transactions involving “Agent3” (Column G). Follow the steps below to filter by a text value.

  1. 1. In an empty cell, type the FILTER function and select the range.
Google Sheets FILTER Function How To Examples FILTER Function Range
Google Sheets FILTER Function: How To & Examples - FILTER Function & Range
  1. 2. Add the condition - column G must equal “Agent3” - and close the parentheses.
Google Sheets FILTER Function How To Examples Add Condition 2
Google Sheets FILTER Function: How To & Examples - Add Condition
  1. 3. Press ‘Enter’ to see the result.
Google Sheets FILTER Function How To Examples Filtered by Text
Google Sheets FILTER Function: How To & Examples - Filtered by Text
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

How to FILTER by Numerical Value in Google Sheets?

In this case, I want to filter the data in the table to transactions involving amounts larger than or equal to $1000.

  1. 1. Type the FILTER function and select the range of cells you want to filter.
Google Sheets FILTER Function How To Examples FILTER Function Range 2
Google Sheets FILTER Function: How To & Examples - FILTER Function & Range
  1. 2. Add the condition - the values in column F must be equal to or greater than $1000 - and close the parenthesis.
Google Sheets FILTER Function How To Examples Add Condition 3
Google Sheets FILTER Function: How To & Examples - Add Condition
  1. 3. Press ‘Enter’ to see the result.
Google Sheets FILTER Function How To Examples Filtered by Numerical Value
Google Sheets FILTER Function: How To & Examples - Filtered by Numerical Value

How to FILTER by Date in Google Sheets?

For this example, I want to filter by date to get only those transactions that took place in June of 2022.

  1. 1. In an empty cell, type the FILTER function and select the range.
Google Sheets FILTER Function How To Examples FILTER Function Range 3
Google Sheets FILTER Function: How To & Examples - FILTER Function & Range
  1. 2. Add the condition - column H must be between June 1st and June 30th of 2022 - and close the parenthesis. Type the ISBETWEEN function and use the date column as the first parameter.
Google Sheets FILTER Function How To Examples Add ISBETWEEN Function
Google Sheets FILTER Function: How To & Examples - Add ISBETWEEN Function
  1. 3. For the second argument, use the DATE function to provide the start date: 2022, 6, 1. For the third argument, do the same with the end date: 2022, 6, 30.
Google Sheets FILTER Function How To Examples Add DATE Functions
Google Sheets FILTER Function: How To & Examples - Add DATE Functions
  1. 4. Skip the third and fourth parameters to ensure the filter includes the start and end dates. Close the parentheses and press ‘Enter’ to see the results.
Google Sheets FILTER Function How To Examples Filtered by Date
Google Sheets FILTER Function: How To & Examples - Filtered by Date
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

How to FILTER Using Multiple Conditions in Google Sheets?

You can use the FILTER function with multiple conditions, and these conditions can be applied to one or multiple columns. In the example below, I will filter using two conditions, each applied to a different column.

  1. 1. Type the FILTER function and select the range.
Google Sheets FILTER Function How To Examples FILTER Function Range 4
Google Sheets FILTER Function: How To & Examples - FILTER Function & Range
  1. 2. Add the first condition: values in column G must be equal to “Agent1”.
Google Sheets FILTER Function How To Examples First Condition
Google Sheets FILTER Function: How To & Examples - First Condition
  1. 3. Add the second condition: values in column H must be on or after March 15, 2022. Remember to use the DATE function to provide the date.
Google Sheets FILTER Function How To Examples Second Condition
Google Sheets FILTER Function: How To & Examples - Second Condition
  1. 4. Close the parenthesis and press ‘Enter’ to see the results.
Google Sheets FILTER Function How To Examples Filtered by Text and Date
Google Sheets FILTER Function: How To & Examples - Filtered by Text and Date

Other Ways to Filter in Google Sheets

One of the benefits of using the FILTER function is that it returns the filtered array without affecting the original data. You can also do this by using the Google Sheets Filter View. Since you don’t change the original data, you won’t affect other users. However, sometimes you want to change the original data, and this is also easy to do.

  1. 1. Click on one of the headers in your table and go to Data > Create a filter.
Google Sheets FILTER Function How To Examples Data Create a Filter
Google Sheets FILTER Function: How To & Examples - Data > Create a Filter
  1. 2. Click on any of the filter icons in the column headers. You can filter by color, by condition, by value, by typing into the search box, or by selecting and deselecting values from the list.
Google Sheets FILTER Function How To Examples Data Choose Filter
Google Sheets FILTER Function: How To & Examples - Data > Choose Filter

Conclusion

You can use the FILTER function to filter your data by conditions as simple or as complicated as you want. You can use multiple conditions and apply them to the same column or different ones, and you can combine FILTER with other functions and operators to create complex conditions. You know the function syntax and requirements, as well as how to use it with many different types of conditions: using another function to test the values, matching text values, testing numerical values and dates using comparison operators and functions, and using multiple conditions applied to different columns. Finally, you have reviewed other methods for filtering data in Google Sheets.

Check out these guides to learn more about filtering and sorting data in Google Sheets.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 13 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets