How to Use FILTER Function in Google Sheets

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.