You can use the following basic syntax to filter data from another sheet in Excel:
=FILTER(Sheet1!A2:C11, Sheet1!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 Excel
Suppose we have the following sheet named All_Teams in Excel that contains 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(Sheet1!A2:C11, Sheet1!B2:B11="Western")
We can type this formula into cell A1 of the Specific_Teams sheet:
Notice that this FILTER function returns all rows from the All_Teams sheet where the conference column is equal to “Western.”
How to Avoid Common Errors with FILTER Function
When using the FILTER function, there are two common errors you may encounter:
Error #1: Using Ranges of Different Sizes
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 an error.
To fix this error, simply make sure the two ranges contain the same number of rows.
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 would receive an error.
To fix this error, simply use double quotes.
Note: You can find the complete documentation for the FILTER function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Filter One Column Based on Another Column
Excel: How to Filter by Number of Characters
Excel: How to Count Filtered Cells with Text
