Working with large sets of data can be overwhelming, but Google Sheets offers tools that make it much easier. One of the most useful tools is the FILTER function. Instead of scrolling through rows or adjusting built-in filter buttons, this function instantly shows only the information you need based on the conditions you set. It creates a clean, dynamic view of your data without changing your original table.
What the FILTER Function Does
At its core, the FILTER function checks your data range and returns only the rows that match your conditions. It doesn’t delete or hide anything—your original data stays untouched. Instead, it simply displays a new, filtered version wherever you place the formula.
Here’s the basic structure:
=FILTER(range, condition1, [condition2, ...])
- range: The data you want to filter
- condition1, condition2, …: Rules that decide what stays and what gets removed
Each condition must line up with the rows or columns you’re filtering. You can add as many conditions as you want, but they must all follow the same direction (all row-based or all column-based).
Filtering With Simple Conditions
1. Filter by a Single Condition
If you want to see only rows that contain a certain text, number, or date, you can use one condition.
Example: Show only rows where column A says “Vegetables”:
=FILTER(A2:D10, A2:A10="Vegetables")
This instantly pulls out all the matching rows.
2. Filter With Two Conditions (AND condition)
If you want rows that meet more than one requirement, simply add more conditions.
Example: Show only “Vegetables” and calories greater than 20:
=FILTER(A2:D10, A2:A10="Vegetables", C2:C10>20)
All conditions must be true.
3. Filter With Multiple Conditions
You can continue adding conditions to narrow your results further.
Example: Vegetables, calories > 20, and fat < 0.3:
=FILTER(A2:D10, A2:A10="Vegetables", C2:C10>20, D2:D10<0.3)
4. Filter Using OR Logic
If you want results that meet any one of several conditions, you can add them using a + (plus sign).
Example: Vegetables OR items with fat under 0.3:
=FILTER(A2:D10, (A2:A10="Vegetables") + (D2:D10<0.3))
When any condition is true, the row is included.
Filtering Numbers, Dates & Text
1. Filter Largest Values
To find the highest values (like top 3 results):
=FILTER(A2:D10, C2:C10 >= LARGE(C2:C10, 3))
This pulls all rows with values in the top three.
2. Sort Filtered Results
If you want your filtered output sorted at the same time:
=SORT(FILTER(A2:D10, C2:C10>=LARGE(C2:C10,3)), 3, FALSE)
Here, column 3 is sorted in descending order.
3. Filter by Exact Text Match
To get rows where a column matches a specific word:
=FILTER(A2:E15, E2:E15="Late")
4. Filter by Text That Contains Certain Letters
For partial text matches, use SEARCH or FIND.
Example: Find rows where column A contains “CA” starting from the 8th character:
=FILTER(A2:E15, SEARCH("CA", A2:A15, 8))
5. Filter by Date
To filter by an exact date:
=FILTER(A2:E15, C2:C15 = DATE(2020, 1, 9))
Find all dates in January:
=FILTER(A2:E15, MONTH(C2:C15)=1)
Filter rows after a specific date:
=FILTER(A2:E15, C2:C15 >= DATE(2020,1,1))
6. Filter by Time
Example: Times after 2 PM:
=FILTER(A2:B10, A2:A10 > TIME(14,0,0))
Filtering With Multiple Columns
You can combine conditions across several columns.
Example:
- Amount between 200 and 400
- Arriving in January
- Status = “on the way”
=FILTER(A2:E15, B2:B15>=200, B2:B15<=400, MONTH(C2:C15)=1, E2:E15="on the way")
Filtering Across Sheets or Files
1. Filter From Another Sheet
Just include the sheet name:
=FILTER(Orders!A2:E15, Orders!C2:C15="Late")
If the sheet name has spaces:
=FILTER('May Orders'!A2:E15, 'May Orders'!C2:C15="Late")
2. Filter From Another Google Sheets File
Use IMPORTRANGE.
=FILTER(IMPORTRANGE("FILE_ID","Orders!A2:E15"), IMPORTRANGE("FILE_ID","Orders!E2:E15")="Late")
After running it, click Allow Access when prompted.
Combining FILTER With Other Functions
1. FILTER + SORT
Sort filtered data:
=SORT(FILTER(B3:E17, B3:B17="John Doe"), 4, FALSE)
2. FILTER + UNIQUE
Remove duplicate names:
=UNIQUE(FILTER(B3:B17, B3:B17<>""))
3. FILTER + SUM
Sum values for rows matching a condition:
=SUM(FILTER(E3:E17, B3:B17="John Doe"))
4. FILTER + LARGE
Largest value for certain customer:
=LARGE(FILTER(E3:E17, B3:B17="John Doe"), 1)
Top 3 values:
=FILTER(B3:E17, E3:E17 >= LARGE(E3:E17, 3))
Common FILTER Issues (And Easy Fixes)
1. “No matches found” (#N/A)
Happens when no row meets your conditions.
Fix with a friendly message:
=IFERROR(FILTER(A2:E15, E2:E15="Arrived"), "No matches found")
2. Mismatched Range Sizes
All ranges in your conditions must have the same height/width as the main range.
3. #REF! Error
This appears when filtered results try to fill cells that already contain data.
Clear the output area.
4. Can’t Edit Filtered Output
FILTER results are dynamic and uneditable.
If you need to edit them:
- Copy → Paste as values
- Or change your FILTER conditions
Practical Tips
- Keep your data clean (no merged cells, consistent formatting).
- Use cell references to avoid hardcoding conditions.
- Leave enough empty space where the results will appear.
- Try combining FILTER with other formulas to build deeper insights.
Conclusion
The FILTER function is one of the most flexible tools in Google Sheets. Once you understand the basics—range, conditions, and how to combine them—you can build powerful custom filters for any kind of data. From simple text checks to complex multi-column rules, FILTER helps you extract the exact information you need without touching the original sheet.
Other Articles
DTM Meaning in Text: What It Stands For and How to Use It
