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.

**Table of Contents**hide

## Download the Practice WorkBook

**FILTER Function in Excel (Quick View)**

**Excel FILTER Function: Syntax & Arguments**

**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.

**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.

**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.

**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.

**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.

**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.

**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

**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.

**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.

**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.

**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.

**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.

**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))`

**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.

**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.

**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.

**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.

**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

- How to Use SORT Function in Excel (4 Examples)
- How to use XLOOKUP function in Excel (7 Examples)
- How to use MATCH function in Excel (3 Examples)
- How to Use HLOOKUP Function in Excel (8 Suitable Approaches)
- How to Use VLOOKUP Function in Excel (2 Examples)
- VLOOKUP and HLOOKUP combined Excel formula (with example)
- Using Excel to Lookup Partial Text Match [2 Easy Ways]