Google Sheets offers powerful tools to slice and dice your data. While many users rely on the standard filter buttons in the toolbar, there is a much more powerful way to organize your data: The FILTER Function.
Unlike standard filters, which simply hide rows, the FILTER function extracts data that meets your criteria and places it into a new area of your spreadsheet. Even better? It is dynamic. If your original data changes, your filtered list updates automatically.
This guide makes the Google Sheets FILTER function the perfect tool for creating interactive reports and dashboards.
In this comprehensive tutorial, we will cover:
- The exact syntax of the function.
- How to filter by single and multiple conditions (AND/OR).
- Advanced techniques like filtering by Date, Top 5 Values, and Odd/Even rows.
- How to sort your filtered results automatically.
Prefer watching over reading? Check out our video tutorial:
The Google Sheets FILTER Function Syntax
Before we dive into examples, let’s understand the anatomy of the formula.
Here is what each part means:
- range: This is the data you want to display. It can be a single column or an entire table (e.g.,
A2:C100). - condition1: This is the rule used to filter the data. It must be a column or row of the same size as your range that returns TRUE or FALSE.
- [condition2, …]: (Optional) You can add more conditions. If you do, the function will only return rows where ALL conditions are true.
Example 1: Basic Filter (Based on a Single Condition)
Let’s start with the basics. Suppose you have a dataset of sales records and you want to extract a list of all transactions that occurred in the state of Florida. I’m including a screenshot here, and I’ll show more for each step.
To do this, we select our entire data table as the data range, and set our condition to check the “State” column. Then, we set our filtering criteria (including cell references).
Formula Breakdown:
- A2:C11: This is the data we want to pull.
- B2:B11=”Florida”: This is the condition. Sheets looks at column B, line by line. If it sees “Florida”, it marks it as TRUE and keeps the row. If it sees “Iowa”, it marks it FALSE and skips it.
Making it Dynamic:
Hard-coding “Florida” into the formula is fine, but for a dashboard, you might want to reference a cell. If you type “Florida” into cell H1, you can use this formula:
=FILTER(A2:C11, B2:B11=H1)
Now, whenever you change the text in H1, your filtered list updates instantly. That’s a basic filter. It’s super common in Google Sheets dashboards. But there is so much more you can do with spreadsheet data. Let’s talk about more complex filters.
Example 2: Filter Based on Multiple Conditions (AND Logic)
Real-world data often requires more specific criteria. What if you need to find records where the State is Florida AND the Sales value is greater than $5,000?
The FILTER function handles “AND” logic simply by adding more arguments separated by commas.
How it Works:
By using a comma between conditions, you are telling Google Sheets: “Only show me rows where Condition 1 is TRUE AND Condition 2 is TRUE.”
- Condition 1: Is the state Florida?
- Condition 2: Are sales > 5000?
If a row is “Florida” but sales are only 4000, it will be excluded.
Example 3: Multiple Filter Conditions (OR Logic)
What if you want to filter for California OR Iowa?
The syntax changes slightly here. You cannot just use a comma (which means AND). Instead, we use the Plus (+) operator. In spreadsheet logic, the Plus sign acts as an “OR” operator.
The Logic Behind the Plus (+) Sign:
This works based on Boolean math. In Google Sheets, TRUE = 1 and FALSE = 0.
- If a row is California: Condition 1 is TRUE (1) + Condition 2 is FALSE (0). Total = 1.
- If a row is Iowa: Condition 1 is FALSE (0) + Condition 2 is TRUE (1). Total = 1.
- If a row is Florida: Both are FALSE (0). Total = 0.
The FILTER function returns any row where the result is greater than 0.
Example 4: Filter Top 3, 5, or 10 Records
A very common request is to see the “Top Performers.” You can do this by combining the FILTER function with the LARGE function.
Suppose you want to extract the rows with the Top 3 Sales figures.
Step-by-Step:
- The LARGE Function:
LARGE(C2:C11, 3)looks at the sales column and finds the 3rd largest number in the entire list. - The Condition:
C2:C11 >= ...tells the filter to keep any row where the sales number is equal to or greater than that 3rd largest number.
LARGE for SMALL in the formula!Example 5: Filter AND Sort the Data Simultaneously
By default, the FILTER function returns data in the exact order it appears in your original list. Often, this isn’t helpful. If you are pulling the Top 5 Sales, you probably want them ordered from Highest to Lowest.
We can wrap our FILTER formula inside a SORT formula.
Formula Breakdown:
- Inner Part: The FILTER function grabs the data (as seen in Example 4).
- Outer Part: The SORT function takes that filtered data.
- Sort Column: The
3indicates we want to sort by the 3rd column (Sales). - Sort Order: The
FALSEindicates we want Descending order (Big to Small).
Example 6: Filter Odd or Even Rows
Sometimes you might import data from a website or PDF that is formatted badly, with junk data in every other row. You can clean this up using FILTER combined with the MOD and ROW functions.
To Keep Even Rows Only:
To Keep Odd Rows Only:
The Math Explanation:
The MOD function divides the row number by 2 and looks at the remainder.
- If the remainder is 0, the number is Even.
- If the remainder is 1, the number is Odd.
This allows you to systematically strip out every 2nd, 3rd, or 4th row depending on your needs.
Example 7: How to Filter by Date (Bonus Section)
Filtering by date is one of the most common tasks in Google Sheets, but it often causes errors because Sheets can confuse dates with text strings.
The Wrong Way: =FILTER(A2:C, A2:A > "01/01/2023") (Do not do this!)
The Right Way: Use the DATE function.
This formula accurately extracts all records occurring after January 1st, 2023. By using the DATE(yyyy, mm, dd) function, you ensure Google Sheets understands exactly what point in time you are referring to.
Troubleshooting: Common Errors and Fixes
The FILTER function uses “Dynamic Arrays,” which means the results spill over into adjacent cells. This can lead to specific errors.
| Error Code | What It Means | How to Fix It |
|---|---|---|
| #REF! | “Array result was not expanded” The filter wants to display data, but there is existing text in one of the cells blocking it. |
Clear the cells below and to the right of your formula. The filter needs empty space to “spill” into. |
| #N/A | “No matches found” None of your data met the criteria you set. |
Wrap your formula in the IFNA function to show a custom message:=IFNA(FILTER(...), "No Data Found") |
| #VALUE! | “Mismatched range sizes” You selected A2:A100 for the range but B2:B50 for the condition. |
Ensure your condition range has the exact same number of rows as your source range. |
Frequently Asked Questions
Can I Filter Data from Another Sheet?
Absolutely. Just click into the other tab when selecting your range. Your formula will look like this: =FILTER(Sheet2!A2:C, Sheet2!B2:B="Florida").
Can I Edit the Data inside the Filtered Results?
No. The results are dynamic and linked to the source. If you try to type over a cell in the filtered list, you will break the formula and get a #REF! error. To edit data, you must edit the source dataset.
How is FILTER different from QUERY?
FILTER is best for simple, quick extractions. QUERY is a more advanced function (using SQL-like language) that can filter, sort, sum, and group data all in one step. For 90% of users, FILTER is easier to learn and use.
Other Google Sheets tutorials you may like: