FILTER function in Google Sheets

When you are working with large datasets in Google Sheets, scrolling endlessly or applying manual filters again and again can be both time-consuming and frustrating.

Imagine you have a list of thousands of employees, sales transactions, or expense records — and you just want to see the ones that match specific criteria, like “Sales above ₹50,000” or “Employees from the IT department”. Doing this manually every time not only slows you down but also increases the chances of mistakes.

That’s where the FILTER function comes in. It allows you to create a dynamic, automated filter that instantly pulls out only the rows that meet your conditions. Even better, the results update automatically whenever your source data changes — meaning you don’t need to apply filters again and again.

---

Syntax of FILTER Function in Google Sheets

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

• range → The data you want to filter.
• condition1, condition2… → One or more conditions (must be the same size as the range).

---

Example 1: Basic Filter

You have employee data in A1:C10 (Name, Department, Salary). To get all employees from Sales department:

=FILTER(A2:C10, B2:B10="Sales")

👉 This will return only the rows where column B = “Sales”.

---

Example 2: Filter with Multiple Conditions (AND)

Employees in Sales department with salary above ₹50,000:

=FILTER(A2:C10, B2:B10="Sales", C2:C10>50000)

Read More: IMPORTRANGE in Google Sheets

---

Example 3: Filter with OR Condition

Employees in HR or Finance department:

=FILTER(A2:C10, (B2:B10="HR") + (B2:B10="Finance"))

👉 The + sign works like OR.

---

Example 4: Filter Blank or Non-Blank Cells

Show rows where salary is not blank:

=FILTER(A2:C10, C2:C10<>"")

---

Example 5: Top Performers (Filter with MAX)

Get employees with the highest salary:

=FILTER(A2:C10, C2:C10=MAX(C2:C10))

---

Example 6: Filter with Text Match (LIKE)

Get names starting with “Ra”:

=FILTER(A2:C10, LEFT(A2:A10,2)="Ra")

Read More: QUERY Function in Google Sheets

---

Example 7: Filter Data from Another Sheet

If your data is in another sheet named DataSheet:

=FILTER(DataSheet!A2:C100, DataSheet!B2:B100="IT")

---

Example 8: Filter Unique + Sorted Data

Get unique departments in ascending order:

=SORT(UNIQUE(FILTER(B2:B100, B2:B100<>"")))

---

Limitations of FILTER Function

• Returns #N/A error if no rows match → Fix using IFERROR.
• Cannot filter non-contiguous ranges.
• Every condition must match the size of the range.

👉 Example with IFERROR:

=IFERROR(FILTER(A2:C10, B2:B10="Marketing"), "No records found")

Read More: ARRAYFORMULA in Google Sheets

---

MIS Use Cases

1. Attendance Report – Get only “Absent” employees for a date.
2. Finance Report – Show invoices pending beyond 30 days.
3. Project Monitoring – List tasks marked “In Progress”.
4. HR Report – Filter employees whose probation is ending this month.
5. Sales Analysis – Show top 10 customers by purchase value.
6. Procurement – List vendors with overdue deliveries.

Limitations of FILTER Function

• Returns #N/A error if no data matches.
• Needs conditions equal in size to range.
• Cannot combine non-contiguous ranges.
• Sometimes slow with very large data.

👉 Use with IFERROR to handle empty results:

=IFERROR(FILTER(A2:C10, B2:B10="Marketing"), "No records found")

---

Troubleshooting FILTER

• FILTER returns #N/A → No match found, wrap in IFERROR().
• FILTER returns #REF! → Range and condition size mismatch.
• Not updating → Check if your range includes all rows.
• Slow performance → Reduce range from A:A to A2:A5000.

---

Pro Tips

1. Combine FILTER with ARRAYFORMULA for auto-updating reports.
2. Use REGEXMATCH for advanced text-based filtering.
3. Use SORT(FILTER()) to always get results in proper order.
4. Use IMPORTRANGE + FILTER to consolidate reports from multiple files.
5. Use FILTER + UNIQUE to remove duplicates dynamically.

---

FILTER vs QUERY vs Excel

Feature	FILTER	QUERY	Excel Filter
Ease of Use	Very Easy	Medium (SQL-like)	Manual
Live Update	✅ Yes	✅ Yes	❌ No (manual refresh)
Works Across Sheets	✅ Yes	✅ Yes	❌ No

👉 So, if you want something simple and fast, use FILTER. For advanced reporting, move to QUERY.

Conclusion

The FILTER function in Google Sheets is a game-changer for MIS reporting and day-to-day office use. It saves time, avoids manual effort, and gives you live, dynamic reports.

If you are shifting from Excel, this will be one of the most powerful functions you’ll learn. Start simple, and slowly combine it with QUERY, IMPORTRANGE, and ARRAYFORMULA for advanced reporting.

FAQs – Filter function in Google Sheets

What’s Next?

In the next post, we’ll learn about the UNIQUE Function in Google Sheets