You can use the following basic syntax to filter data from another sheet in Google Sheets:
=FILTER(All_Teams!A2:C11, All_Teams!B2:B11="Western")
This particular function will return all rows in the range A2:C11 from the sheet titled All_Teams where the values in the range B2:B11 are equal to “Western.”
The following example shows how to use this syntax in practice.
Example: Filter from Another Sheet in Google Sheets
Suppose we have the following sheet named All_Teams in Google Sheets that contains some data about various basketball teams:
Now suppose we’d like to switch to the sheet titled Specific_Teams and only display the teams from All_Teams that are in the Western conference.
We can use the following syntax to do so:
=FILTER(All_Teams!A2:C11, All_Teams!B2:B11="Western")
Here’s how to use this formula in practice:
Notice that this FILTER function returns all rows from the All_Teams sheet where the conference column is equal to “Western.”
Potential Error #1: Mismatched Range Sizes
When using the FILTER function, you may receive an error if you use two ranges that are not of equal size.
For example, suppose we use the following FILTER function:
=FILTER(All_Teams!A1:C11, All_Teams!B2:B11="Western")
The first range A1:C11 does not contain the same number of rows as the second range B2:B11, so we’ll receive how an error:
To fix this error, simply make sure the two ranges contain the same number of rows.
Potential Error #2: Using Single Quotes
When using the FILTER function, you may receive an error if you use single quotes instead of double quotes.
For example, suppose we use the following FILTER function:
=FILTER(All_Teams!A1:C11, All_Teams!B2:B11='Western')
Since we wrapped the word Western in single quotes instead of double quotes, we receive how an error:
To fix this error, simply use double quotes.
Note: You can find the complete documentation for the FILTER function here.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Filter with Multiple Conditions
Google Sheets: How to Filter by Date Range
Google Sheets: How to Filter for Cells that Contain Text