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. Type the FILTER function in an empty cell.
- 2. Select the range of cells you want to filter, followed by a comma.
- 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.
- 4. Press ‘Enter’ to see the result.
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. In an empty cell, type the FILTER function and select the range.
- 2. Add the condition - column G must equal “Agent3” - and close the parentheses.
- 3. Press ‘Enter’ to see the result.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREHow 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. Type the FILTER function and select the range of cells you want to filter.
- 2. Add the condition - the values in column F must be equal to or greater than $1000 - and close the parenthesis.
- 3. Press ‘Enter’ to see the result.
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. In an empty cell, type the FILTER function and select the range.
- 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.
- 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.
- 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.
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 MOREHow 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. Type the FILTER function and select the range.
- 2. Add the first condition: values in column G must be equal to “Agent1”.
- 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.
- 4. Close the parenthesis and press ‘Enter’ to see the results.
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. Click on one of the headers in your table and go to Data > Create a filter.
- 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.
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.