How to Use FILTER Function in Excel (9 Easy Examples)

Overview of Filter function

Excel 365 provides us a powerful function for automatically filtering our datasets, named the FILTER function. It makes our task easier by using this function in Excel formulas. This article will share the complete idea of how the FILTER function works in Excel independently and then with other Excel functions.

Download the Practice WorkBook

FILTER Function in Excel (Quick View)

Overview of Filter function

Excel FILTER Function: Syntax & Arguments

Filter function argument and syntax

Summary

Filter a range or array.

Syntax

=FILTER (array, include, [if_empty])

Arguments

Argument Required or Optional Value
array  Required An array, an array formula, or a reference to a range of cells for which we require the number of rows.
include Required This works like a Boolean array; it carries the condition or criteria for filtering.
[if_empty] Optional Pass the value to return when no results are returned.

Note: 

  • The FILTER function “filters” a range of data based on supplied criteria.
  • The include argument can take one or more logical tests.
  • The FILTER function returns a dynamic result. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically.

How to Use the FILTER Function in Excel (9 Examples)

Example 1: Filter with Multiple Criteria (AND logic)

Let’s have a student information dataset with their ID, Name, Department, Semester, CGPA. Now we will find out the data where Department = CSE and CGPA >= 3.77. According to AND logic both conditions must be true to get a student’s information.

Filter with multiple criteria using AND logic

Step 1: Enter the formula in cell H9 and press Enter

=FILTER(B4:F14, (D4:D14=I4) * (F4:F14>=I5), "no results")

Formula Explanation

  • Here B4:F14 is the table range.
  • (D4:D14=I4) * (F4:F14>=I5) this is the condition where D4:D14=I4 this part finding the rows where Department = CSE and F4:F14>=I5 this is finding the rows where CGPA is greater than or equal to 3.77.
  • “No results” is the message which will be printed when there will be not matched data.

Enter formula using Filter function

Example 2: Filter with Multiple Criteria (OR logic)

Now let’s see how we can apply or condition the above dataset. For OR condition if any of the given conditions get true then it will return true. Now the conditions will be the same, but the logic will be OR.

Step 1: Enter the formula in cell G9 and press Enter

=FILTER(A4:E14, (C4:C14=H4) + (E4:E14>=H5), "no results")

Formula Explanation

  • This formula is the same as the previous one, but instead of an asterisk (*), we use the plus (+) for OR condition.

Filter with Multiple Criteria (OR logic)

Example 3: Filter Based on Multiple AND & OR Criteria

Now here we will use both AND and OR logic as criteria in the FILTER function. Now our concern is to find out the student data where the department should be CSE or EEE and the CGPA must be greater or equal to 3.77.

Filter Based on Multiple AND & OR Criteria

Step 1: Enter the formula in cell G10 and press Enter

=FILTER(A4:E14, (E4:E14>=H6)*((C4:C14=H5)+(C4:C14=H4)), "No results")

Formula Explanation

  • ((C4:C14=H5)+(C4:C14=H4)) this part contains the OR condition.
  • (E4:E14>=H6)*((C4:C14=H5)+(C4:C14=H4)) this full combined part is acting like AND condition.

Enter formula using Filter function

Example 4: How to Filter Duplicates in Excel using FILTER Function

Now we will see how we can detect duplicate values in Excel. For this again we will use the same dataset above but there will be duplicate values on that dataset.

How to Filter Duplicates in Excel using FILTER Function

Step 1: Enter the formula in cell G5 and press Enter

=FILTER(A4:E19, COUNTIFS(A4:A19, A4:A19, B4:B19, B4:B19, C4:C19, C4:C19,D4:D19,D4:D19,E4:E19,E4:E19)>1, "No results")

Formula Explanation

  • COUNTIFS function is used to count the rows with conditions. We are comparing each column with another and checking if it appears more than one time or not.
  • Like A4:A19, A4:A19 for the first column, B4:B19, B4:B19 for the second column, etc.
  • FILTER function is used to extract the filtered values.
  • If you want to know more about COUNTIFS functions you can check this Link

Enter formula using FILTER and COUNIFS

Example 5: How to Filter out Blanks in Excel

Let’s consider the previous dataset with some blank space in some rows. Now our task is to filter the data where there will be no rows with bank cells.

How to Filter out Blanks in Excel

Step 1: Enter the formula in cell G5 and press Enter

=FILTER(A4:E14,(A4:A14<>"")*(B4:B14<>"")*(C4:C14<>"")*(D4:D14<>"")*(E4:E14<>""),"No results")

Formula Explanation

  • Here using OR logic we are checking it any column has a blank value or not.
  • For each column, the condition is like (A4:A14<>””), (B4:B14<>””) etc.
  • Star (*) sign is used for OR logic.

Enter formula using Filter function

Example 6: Filter Cells Containing Specific Text

FILTER function can be searching for any specific text. We can search for any names from the student dataset. Let’s see how we can do that.

Filter Cells Containing Specific Text

Step 1: Enter the formula in cell

=FILTER(A4:E14, ISNUMBER(SEARCH(I3, B4:B14)), "No results")

Formula Explanation

  • SEARCH(I3, B4:B14) will return the cells which will be matched with the input value.
  • ISNUMBER(SEARCH(I3, B4:B14)) this will return true if the search value is a number other than false.
  • Finally, the FILTER function extracts the matched rows and shows them.

Enter formula using Filter ISNUMBER and search function

Example 7: Calculate Summation, Maximum, Minimum, Average Using Filter Function

This step will find out the total CGPA, maximum CGPA, minimum CGPA, and the average CGPA for any specific department from the student dataset using the FILTER function.

Calculate Summation, Maximum, Minimum, Average Using Filter Function

Step 1: Enter the below formulas in cells H7, H8, H9, H10

For summation

=SUM(FILTER(E4:E14, C4:C14=I3, 0))

For average

=AVERAGE(FILTER(E4:E14, C4:C14=I3, 0))

For minimum

=MIN(FILTER(E4:E14, C4:C14=I3, 0))

For maximum

=MAX(FILTER(E4:E14, C4:C14=I3, 0))

Enter formula using SUM and filter function

Example 8: How to FILTER Data and Return Only Specific Columns

Up to now, we were returning the whole column for the matched data. Now in this section, we will return only two specific columns for the matched data after filtering.

how to filter data and return only specific columns

Step 1: Enter the formula in cell G7 and press Enter

=FILTER(FILTER(A4:E14, C4:C14=J3), {1,1,0,0,0})

Or

=FILTER(FILTER(A4:E14, C4:C14=J3), {TRUE,TRUE,FALSE,FALSE,FALSE})

Formula Explanation

  • FILTER(A4:E14, C4:C14=J3) this part will return the matched rows from the given dataset with all the columns.
  • FILTER(FILTER(A4:E14, C4:C14=J3), {1,1,0,0,0}) this outer FILTER function will select only the first two columns of the selected data. We can either use 0 ,1 or TRUE, FLASE.

 Enter formula using two FILTER function

Example 9: How to Limit the Number of Rows Returned by the FILTER Function

Now using the FILTER function, we will see how to set a limit for the returning values. Here our task is to find out the first two student information where the department will be CSE.

How to Limit the Number of Rows Returned by the FILTER Function

Step 1: Enter the formula in cell G6 and press Enter

=IFERROR(INDEX(FILTER(A4:E14, C4:C14=J3), {1;2}, {1,2,3,4,5}), "No result")

Formula Explanation

  • FILTER(A4:E14, C4:C14=J3) this will return the filtered data by matching it with the input value.
  • INDEX(FILTER(A4:E14, C4:C14=J3), {1;2}, {1,2,3,4,5}) this will return the first two rows of the matched data. {1;2} this is for the first two rows. And {1,2,3,4,5} this is for selecting the five columns.
  • Lastly, the IFERROR function is used to avoid the error if there is a problem with other function return values.

Formula using FILTER INDEX and IFERROR function

Things to Remember

Common Errors When they show
#VALUE It will appear when the array and include argument have incompatible dimensions.
#CALC! It will appear if the optional if_empty argument is omitted and no results meeting the criteria are found.
#NAME It will appear when trying to use FILTER in an older version of Excel.
#SPILL This error will happen if one or more cells in the spill range are not entirely blank.
#REF! This error will happen if a FILTER formula is used between different workbooks and closed the source workbook.
#N/A or  #VALUE  This type of error may occur if some value in the include argument is an error or cannot be transformed to a Boolean value (0,1 or TRUE, FALSE).

Conclusion

This is all about the FILTER function and its different applications. Overall, in terms of working with time, we need this function for various purposes. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of utilizing this function, then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo