Excel FILTER Function – 20 Practical Examples

These examples assume a dataset in columns A:C (Name, Department, Salary) unless otherwise specified.

Excel Filter


1. Filter by Department

Formula:

=FILTER(A2:C20,B2:B20="HR")

Explanation: Returns only HR employees.

2. Salary Greater Than

Formula:

=FILTER(A2:C20,C2:C20>50000)

Explanation: Returns salaries above 50,000.

3. Dynamic Cell Reference

Formula:

=FILTER(A2:C20,B2:B20=F2)

Explanation: Uses the value in cell F2.

4. AND Condition

Formula:

=FILTER(A2:C20,(B2:B20="HR")*(C2:C20>50000))

Explanation: Returns HR employees with salary > 50,000.

5. OR Condition

Formula:

=FILTER(A2:C20,(B2:B20="HR")+(B2:B20="IT"))

Explanation: Returns HR or IT employees.

6. No Match Message

Formula:

=FILTER(A2:C20,B2:B20="Legal","No Records Found")

Explanation: Displays a custom message if no match exists.

7. Blank Cells

Formula:

=FILTER(A2:C20,B2:B20="")

Explanation: Returns rows with blank departments.

8. Non-Blank Cells

Formula:

=FILTER(A2:C20,B2:B20<>"")

Explanation: Returns rows with non-blank departments.

9. Between Values

Formula:

=FILTER(A2:C20,(C2:C20>=40000)*(C2:C20<=60000))

Explanation: Returns salaries between 40,000 and 60,000.

10. Less Than

Formula:

=FILTER(A2:C20,C2:C20<40000)

Explanation: Returns salaries below 40,000.

11. Starts With

Formula:

=FILTER(A2:C20,LEFT(A2:A20,1)="A")

Explanation: Names starting with A.

12. Ends With

Formula:

=FILTER(A2:C20,RIGHT(A2:A20,1)="n")

Explanation: Names ending with n.

13. Contains Text

Formula:

=FILTER(A2:C20,ISNUMBER(SEARCH("Manager",D2:D20)))

Explanation: Rows containing 'Manager'.

14. Today's Date

Formula:

=FILTER(A2:D20,D2:D20=TODAY())

Explanation: Records for today.

15. Current Month

Formula:

=FILTER(A2:D20,MONTH(D2:D20)=MONTH(TODAY()))

Explanation: Records for the current month.

16. Current Year

Formula:

=FILTER(A2:D20,YEAR(D2:D20)=YEAR(TODAY()))

Explanation: Records for the current year.

17. FILTER + SORT

Formula:

=SORT(FILTER(A2:C20,B2:B20="HR"),3,-1)

Explanation: Sort filtered results by salary descending.

18. FILTER + UNIQUE

Formula:

=UNIQUE(FILTER(B2:B20,C2:C20>50000))

Explanation: Unique departments after filtering.

19. MATCH Criteria List

Formula:

=FILTER(A2:C20,ISNUMBER(MATCH(B2:B20,F2:F5,0)))

Explanation: Filters using a list of departments.

20. High Scores

Formula:

=FILTER(A2:D20,D2:D20>=90)

Explanation: Returns scores of 90 or above.