Quotes, captions, names, wishes & practical how-tos

Google Sheets & Drive

Google Sheets FILTER Function: Practical Examples

Person typing on a laptop, using the Google Sheets FILTER function
Photo: Alejandro Escamilla alejandroescamilla, CC0, via Wikimedia Commons

The Google Sheets FILTER function returns only the rows or columns of a range that meet the conditions you set, and it updates automatically when the data changes. It replaces manual filtering, copy and paste with a single formula. The examples below start simple and build up to multiple conditions, sorting and error handling.

Key takeaways

  • Syntax: FILTER(range, condition1, [condition2, ...]).
  • Separate conditions work as AND; adding conditions with + works as OR.
  • The result spills into neighboring cells, so they must be empty.
  • Wrap the formula in IFERROR when no rows might match, and in SORT to order the output.

Google Sheets FILTER Function Syntax

FILTER takes the range you want to return, followed by one or more conditions. Each condition must be a column or row of the same size as the range you are filtering.

=FILTER(A2:D100, C2:C100 > 50)

This returns every row of A2:D100 where the value in column C is greater than 50. The condition range (C2:C100) has exactly as many rows as the data range, which is the main rule to remember. The official FILTER help page lists the arguments in full.

Example data used below

Assume a sales sheet with these columns, starting in row 2 under a header row.

Column Contents
A Order date
B Region
C Salesperson
D Amount

Filter by One Condition

To list only orders from the West region, compare the region column with text:

=FILTER(A2:D100, B2:B100 = "West")

Put this formula in a single empty cell, ideally on a separate sheet or to the right of your data. The matching rows appear below and to the right of that cell automatically.

Combine Conditions with AND and OR

List each extra condition as its own argument to require all of them:

=FILTER(A2:D100, B2:B100 = "West", D2:D100 >= 500)

For OR logic, add the conditions together. A row passes if either part is true:

=FILTER(A2:D100, (B2:B100 = "West") + (B2:B100 = "East"))

You can also multiply conditions with * to express AND inside one argument, which is handy when mixing it with OR.

Laptop and notebook on a wooden table for planning a Google Sheets filter function formula
Photo: By StartupStockPhotos from Pixabay, CC0, via Wikimedia Commons (source)

Partial Text Matches and Dates

For a “contains” search, combine ISNUMBER with SEARCH, which is not case sensitive:

=FILTER(A2:D100, ISNUMBER(SEARCH("smith", C2:C100)))

For dates, compare the date column against a date built with the DATE function so the formula does not depend on regional text formats:

=FILTER(A2:D100, A2:A100 >= DATE(2026, 1, 1))

Sort and Clean the Result

FILTER returns rows in their original order. Wrap it in SORT to change that. This sorts the filtered rows by the fourth column, largest first:

=SORT(FILTER(A2:D100, B2:B100 = "West"), 4, FALSE)

If you only need certain columns, filter a narrower range, such as C2:D100, or combine FILTER with the QUERY function for column selection. For turning a filtered list into totals by group, our pivot table guide is the natural next step.

Handle Errors and No Matches

When no row meets the conditions, FILTER returns an #N/A error. Supply a friendly fallback with IFERROR:

=IFERROR(FILTER(A2:D100, B2:B100 = "North"), "No matching rows")

Other typical problems:

  • Mismatched sizes. If the condition range has a different number of rows from the data range, Sheets reports an error. Check that both start and end on the same rows.
  • Output blocked. If a non-empty cell sits in the spill area, the formula shows a #REF! error until you clear it.
  • Text that looks like numbers. Compare like with like, as explained in our VLOOKUP troubleshooting guide.
Calculator and payroll paperwork, the kind of data a Google Sheets filter function can sort out
Photo: Arun8388, CC0, via Wikimedia Commons (source)

Practical Uses for Filter Formulas

Once the basics click, the same pattern covers many everyday jobs.

  • Personal task lists. Keep one master task sheet and filter by owner or status on a second tab.
  • Gradebooks and rosters. Show only the students whose score falls below a pass mark.
  • Expense reviews. List all amounts above a threshold for a given month so approvals are quick.
  • Dropdown sources. Feed a data validation list from a filtered range so choices depend on an earlier selection.

Use bounded ranges such as A2:D100 while you learn. Whole-column references like A2:D work too and pick up new rows automatically, but they can slow very large files, so test on your own data.

FILTER vs the Filter Tool and Filter Views

The toolbar filter hides rows in the sheet itself, which affects everyone viewing it. Filter views save personal filtering without changing what collaborators see. The FILTER formula is different: it leaves the source untouched and writes the results somewhere else, which suits reports and dashboards that must stay live.

Frequently Asked Questions

Does FILTER work across sheets?

Yes. Reference another tab such as Sales!A2:D100 for both the range and the conditions. Pulling from another file needs IMPORTRANGE first.

Can FILTER return columns instead of rows?

It can. Give it a horizontal range and a condition that is a single row of the same width.

Why is my FILTER result an error?

The most common reasons are no matching rows, ranges of different sizes or a blocked output area. Fix them in that order.

Is FILTER case sensitive?

Comparisons with = ignore case. Use EXACT inside the condition if you need case-sensitive matching.

Can I filter on more than one column at once?

Yes. Add one condition argument per column, or combine tests with + and *.

Wrap-Up

Once you are comfortable with the pattern of a data range followed by same-sized conditions, the Google Sheets FILTER function becomes the fastest way to build live, self-updating lists. Start with one condition and add more as needed.

Written byEditorial Team

Part of the MarkdownToGoogleDocs editorial team, sharing copy-ready words and practical Markdown and Google Docs how-tos.