Mastering the FILTER Function in Google Sheets

Ant
5 days ago
5 min read

Introduction

The FILTER function in Google Sheets is one of the most powerful and versatile tools for dynamically working with data. Whether you're creating dashboards, filtering lists by criteria, or building custom reports, FILTER enables you to display only the data that meets specific conditions, without needing to sort or manually copy anything.

In this tutorial, we'll walk through how the FILTER function works, followed by a series of real-world examples to help you get the most out of it.

What is the FILTER Function?

The FILTER function returns only the rows or columns of a range that meet the conditions you specify.

Syntax:

=FILTER(range, condition1, [condition2, ...])

range: The array or range to return filtered data from.
condition1, condition2, etc.: Logical tests that must be TRUE for rows to be returned.

Each condition must return the same number of rows (or columns) as the range you're filtering, so that Google Sheets knows which parts of the data to keep.

For example, if you're filtering 100 rows of data, your condition must also produce 100 TRUE or FALSE values; one for each row.

Worked Examples

Example 1: Filter Tasks by Status

Imagine you have a task list in columns A to D, with the task status in column D. To show only the tasks marked as "To Do" we can use this formula:

=FILTER(A2:D, D2:D = "To Do")

Example 1: Filter Tasks by Status in Google Sheets — Example 1: Filter Tasks by Status

This formula returns all columns for rows where column D equals "To Do".

First, we specify the range =FILTER(A2:D... (in this case, the whole table). Next, we specify the condition, and in this case, there is only one condition =FILTER(..., D2:D = "To Do") - We're saying that column D should match the text "To Do" and if it does, the FILTER will return only those rows. The result is four rows that match this condition, so only they are returned.

This condition can be adjusted to suit any situation. For example, this has been altered now to return column C where the name is equal to “Helen”.

Example 2: Filter Data Between Two Dates

Suppose column C contains dates, and you want to display only the data for July 2025, we can use this formula:

=FILTER(A2:E, C2:C >= DATE(2025,7,1), C2:C <= DATE(2025,7,31))

Example 2: Filter Data Between Two Dates in Google Sheets — Example 2: Filter Data Between Two Dates

This is useful for filtering data within a specific Date period where the dates should be entered in this format: DATE(year, month, day).

You’ll notice that there are two conditions here. We’re checking dates that are greater than or equal (>=) to July 1st, 2025 AND where the dates are less than or equal (<=) to July 31st, 2025. You can have as many conditions as you need; you’re not limited to two conditions. A comma separates each condition in the condition part of the function.

A comma separates each condition. Example of a FILTER Function in Google Sheets — A comma separates each condition.

Example 3: Filter Using a Cell Value

Let’s say cell F2 contains a status like "Completed". You can make the filter dynamic, as shown below. Instead of hard-coding a status or name, it can be dynamic, using the contents of cell F2.

=FILTER(A2:D, D2:D = F2)

Example 3: Filter Using a Cell Value in Google Sheets — Example 3: Filter Using a Cell Value

This approach is excellent when paired with dropdown menus or user input. You can simply change the dropdown in cell F2 to another status, and the table will update automatically.

Below, the Status has been altered to ‘In Progress’, which now returns all rows that meet that condition.

Example 4: Filter with Multiple Conditions

If you want to filter for a specific person who has tasks in the ‘Backlog’ status, you could use this:

=FILTER(A2:D, (C2:C = "Sebastian") * (D2:D = "Backlog"))

Example 4: Filter with Multiple Conditions in Google Sheets — Example 4: Filter with Multiple Conditions

Here, both conditions must be TRUE for a row to be returned. This formula returns all rows from the range A2:D where:

Column C equals "Sebastian" AND
Column D equals "Backlog"

Again, these do not have to be hard-coded values, the name and status could be dropdown menus to make this flexible and dynamic.

Let’s dive in to see exactly how it works in this example.

C2:C = "Sebastian"

This checks every row in column C to see if it contains the name "Sebastian".

It returns an array of TRUE and FALSE values, one for each row.

D2:D = "Backlog"

Similarly, this checks each row in column D for the status "Backlog" and returns another array of TRUE and FALSE.

Multiplying the conditions: (C2:C = "Sebastian") * (D2:D = "Backlog")

In Google Sheets, multiplying two boolean arrays treats TRUE as 1 and FALSE as 0.

TRUE * TRUE = 1 (keeps the row)
TRUE * FALSE = 0 (excludes the row)
FALSE * FALSE = 0 (excludes the row)

So only rows where both conditions are TRUE are kept.

FILTER(A2:D, ...)

Finally, the FILTER function returns all columns from A2:D for the rows where the result of the multiplication is 1 (i.e. TRUE).

Example 5: Handle No Match Error Gracefully

To avoid a #N/A error when no data matches your filter, you can incorporate an IFERROR function wrapped around the FILTER function.

=IFERROR(FILTER(A2:D, D2:D = "Archived"), "No archived items found")

Example 5: Handle No Match Error Gracefully in Google Sheets — Example 5: Handle No Match Error Gracefully

This makes your sheet cleaner and more user-friendly.

FILTER vs QUERY: When to Use Which

While FILTER is intuitive and great for dynamic filtering, QUERY is better suited when you need to:

Group data
Use aggregation (e.g. SUM, COUNT)
Write SQL-style queries

However, FILTER shines in simplicity and readability, especially when paired with other functions like SORT, UNIQUE, and ARRAYFORMULA.

Best Practices for Using FILTER

Use named ranges to improve readability.
Avoid mixed data types in the columns being filtered.
Avoid using merged cells within the FILTER range, as they can cause errors.
Combine with SORT, UNIQUE, or ARRAYFORMULA to build advanced dynamic tables.

Conclusion

The FILTER function is a foundational skill for any Google Sheets user who wants to make their spreadsheets smarter and more dynamic. Whether you’re creating task trackers, reports, or dashboards, knowing how to filter data with precision can save hours of manual work.

Looking for examples to try out? Make a copy of the template used in this post, or check out our ready-made Google Sheets templates for task tracking, project management and Gantt charts, etc.