What Is FILTER Function Google Sheets?
FILTER function in Google sheets, as the name suggests, filters the data and gives us the result in the spreadsheet. Using this function, we can find the value using multiple conditions and even in other Google sheets.
For example, consider the below example showing region, sales and units in columns A, B, and C, respectively.
Now, using FILTER function, let us find the value showing ‘Available’.
To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C5. Next, select the cell where we want to find the result. In this example, it is cell A7. Next, insert the FILTER function formula in cell A7.
The formula is = FILTER(A2:C5,C2:C5=”Available”)
Press Enter key. We will be able to see the result as shown in the below image.
In this article, let us learn how to use FILTER function in Google sheets with detailed examples.
Table of Contents
Key Takeaways
- Filter function in Google sheets is used to filter the data based on a criteria.
- The syntax of FILTER function is =FILTER(range,condition1,[condition2,…]) where
- range is the cell range where we want to filter the value.
- condition1 is the column or row with the criteria
- Both the arguments are mandatory.
- Using the default FILTER function, we can filter from another sheet or a different workbook.
- Remember, to filter value from a different workbook, we need to use the IMPORTRANGE function in Google sheets.
Syntax
The formula or syntax of FILTER function is
=FILTER(range,condition1,[condition2,…])
where
- range shows the data range where we want to find the value.
- condition1 is the column or row where we have to include the criteria which we want to filter from the range
- condition2 shows the additional criterias which we want to include in the formula to filter other columns.
Range and condition1 are the mandatory arguments.
How To Use FILTER Function In Google Sheets?
The steps to find the value using FILTER function in Google sheets are:
- Step 1: To begin with, insert the cell range in the spreadsheet. Next, select the cell where we want to find the result.
- Step 2: Next, insert the FILTER function in Google sheets formula in the active cell.
- Step 3: Press Enter key.
We will be able to see the result, immediately.
Examples
Let us learn how to use FILTER function with detailed examples.
Example #1 – Filter With Multiple Conditions
For example, consider the below example showing region, sales and units in columns A, B, and C, respectively.
Now, using FILTER function, let us find the value showing ‘Sold.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C7. Next, select the cell where we want to find the result. In this example, it is cell A9.
Step 2: Next, insert the FILTER function formula in cell A9.
Step 3: Press Enter key.
We will be able to see the result as shown in the below image.
Likewise, we can use FILTER function.
Example #2 – Filter With OR Function
Now, for the same example, let us filter out a value using FILTER with OR function.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C9. Next, select the cell where we want to find the result. In this example, it is cell A9.
Step 2: Next, insert the FILTER function formula in cell A9.
The formula is =FILTER(A2:E7,(C2:C7=”Sold”)+(C2:C7=”Available”))
Step 3: Press Enter key.
We will be able to see the result as shown in the below image.
Likewise, we can use FILTER with OR functions in Google sheets.
Example #3 – Filter With AND Function
Now, for the same example, let us filter out a value using FILTER with AND function.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C9. Next, select the cell where we want to find the result. In this example, it is cell A9.
Step 2: Next, insert the FILTER function in Google sheets formula in cell A9.
The formula is =FILTER(A2:E7,(B2:B7>=100)+(B2:B7<=250))
Step 3: Press Enter key.
We will be able to see the result as shown in the below image.
Likewise, we can use FILTER with AND functions.
Example #4 – Filter From Another Google Sheet
Till now, we learned how to filter value using multiple conditions, AND & OR functions with FILTER functions. Now, let us learn how to filter value from another Google sheet.
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C9 in the sheet named DATA. Next, select the cell where we want to find the result. In this example, it is cell A1 in a new sheet, named Example 4.
Step 2: Next, insert the FILTER function formula in cell A1 in sheet, Example 4.
The formula is =FILTER(DATA!A2:C7,DATA!C2:C7=DATA!C7)
Step 3: Press Enter key.
We will be able to see the result as shown in the below image.
Likewise, we can use FILTER function from another sheet.
Example #5 – Filter From Another Google File: Importrange
Now, let us learn how to filter value from another Google file using IMPORTRANGE formula.
The syntax of IMPORTRANGE formula is =IMPORTRANGE(spreadsheet_url,range_string)
The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C7 in a new Google sheet file, named IMPORTRANGE.
Step 2: Next, insert the cell range A1:C7 in Example 5 sheet. Now, insert the FILTER function in Google sheets with IMPORTRANGE function.
Step 3: Press Enter key.
We will be able to see the #REF! error.
Step 4: Press Allow access and we will be able to see the result in Google sheets immediately.
Likewise, we can filter from another sheet using FILTER and IMPORTRANGE function.
Important Things To Note
- Google sheets filter function is used to filter data from the spreadsheet.
- We can filter a value with multiple conditions, OR function, AND function.
- Similarly, we can also filter a value using another Google sheet or another Google sheets workbook.
Frequently Asked Questions
For example, consider the below example showing region, sales and status in columns A, B, and C, respectively.
Now, using FILTER function in Google sheets, let us find the value showing ‘Good. The steps are:
Step 1: To begin with, insert the cell range in the spreadsheet. In this example, data is inserted in the cell range A1:C5. Next, select the cell where we want to find the result. In this example, it is cell A7.
Step 2: Next, insert the FILTER function in Google sheets formula in cell A7.steps are:
The formula is =FILTER(A2:C5,C2:C5=”Good”)
Step 3: Press Enter key.
We will be able to see the result as shown in the below image.
Likewise, we can use FILTER function.
The IMPORTRANGE function is used to import a Google spreadsheet file. Using this function, we can filter the data from another Google sheets workbook.
The syntax or formula of IMPORTRANGE function is =IMPORTRANGE(spreadsheet_url,range_string)
where, both the arguments are mandatory.
• spreadsheet_url denotes the link of the spreadsheet where we want to filter the value
• range_string is the cell range showing the criteria.
Download Template
This article must help understand Filter Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is FILTER Function Google Sheets. Here we discuss How to use Filter Function in Google Sheets with its examples. You can learn more from the following articles. –
Leave a Reply