## Introduction to the FILTER Function in Excel

**Function Objective:**

Filter some particular cells or values according to our requirements.

**Syntax:**

`=FILTER (array, include, [if_empty])`

**Arguments Explanation:**

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

**Return Parameter:**

The 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: 10 Ideal Examples

To demonstrate the examples, we consider a dataset of **10** students of an institution. Their ID, name, department, enrolled semester, and the amount of CGPA are in the range of cells **B5:F14**.

** Note:**

All the methods this article were done using Microsoft Office 365. However, the FILTER function is also available on Excel 2021.

### Method 1 – Performing AND Operation with FILTER Function for Multiple Criteria

In the first example, our desired conditions are in the range of cells **C5:C6**.

**Steps:**

- Select cell
**B10**. - Copy the following formula into the cell:

`=FILTER(Dataset!B5:F14,(Dataset!D5:D14=C5)*(Dataset!F5:F14>=C6),"no results")`

- Press
**Enter**. - You will get the filtered result in the range of cells
**B10:F11**.

### Method 2 – Application of OR Operation with FILTER Function for Multiple Criteria

Consider the conditions in the range of cells **C5:C6**.

**Steps:**

- Select cell
**B10**. - Input the following formula in the cell.

`=FILTER(Dataset!B5:F14,(Dataset!D5:D14=OR!C5)+(Dataset!F5:F14>=OR!C6),"no results")`

- Press
**Enter**.

### Method 3 – Combination of AND and OR Logic with FILTER Function

The conditions are in the range of cells **C5:C7 **where the first two are using OR.

**Steps:**

- Select cell
**B11**. - Enter the following formula in the cell:

`=FILTER(Dataset!B5:F14,(Dataset!F5:F14>=Combine!C7)*((Dataset!D5:D14=Combine!C5)+(Dataset!D5:D14=Combine!C6)),"No results")`

- Press
**Enter**.

### Method 4 – Filtering Duplicates Using FILTER Function

Our dataset contains **2** duplicate entities.

**Steps:**

- Select cell
**H5**. - Copy the following formula in the cell:

`=FILTER(B5:F16,COUNTIFS(B5:B16,B5:B16,C5:C16,C5:C16,D5:D16,D5:D16,E5:E16,E5:E16,F5:F16,F5:F16)>1,"No result")`

- Press
**Enter**.

** Explanation of the Formula**

**COUNTIFS(B5:B16,B5:B16,C5:C16,C5:C16,D5:D16,D5:D16,E5:E16,E5:E16,F5:F16,F5:F16)**: The **COUNTIFS** function checks the presence of the duplicate values.

**FILTER(B5:F16,COUNTIFS(B5:B16,B5:B16,C5:C16,C5:C16,D5:D16,D5:D16,E5:E16,E5:E16,F5:F16, F5:F16)>1,”No result”)**: Finally, the **FILTER** function filter the duplicate values and listed them separately.

### Method 5 – Find Out Blank Cells by FILTER Function

We have a dataset with some blank cells. Let’s filter out the rows that don’t contain any blanks.

**Steps:**

- Select cell
**H5**. - Insert the following formula in the cell:

`=FILTER(B5:F14,(B5:B14<>"")*(C5:C14<>"")*(D5:D14<>"")*(E5:E14<>"")*(F5:F14<>""),"No results")`

- Press
**Enter**.

### Method 6 – Filter Cells That Contain Specific Text

Let’s search for the desired text **‘Ellie’** in cell **J4**.

**Steps:**

- Select cell
**H7**. - Put the following formula in the cell:

`=FILTER(B5:F14,ISNUMBER(SEARCH(J4,C5:C14)),"No results")`

- Press the
**Enter**key.

** Explanation of the Formula**

` `

**SEARCH(J4,C5:C14)**: The **SEARCH** function will return the cells which will be matched with the input value.

` `

**ISNUMBER(SEARCH(J4,C5:C14))**: The **ISNUMBER** function will return true if the search value is a number other than false.

` `

**FILTER(B5:F14,ISNUMBER(SEARCH(J4,C5:C14)),”No results”)**: Finally, **the FILTER function** extracts the matched rows and shows them.

### Method 7 – Calculation of Summation, Maximum, Minimum, and Average

Now, we are going to perform some mathematical calculations. The data for which we will filter will be in cell **J5**. Here, we are going to determine all the values for the **CSE** department.

**Steps:**

- Select cell
**J7**. - Copy the following formula in the cell for the summation:

`=SUM(FILTER(F5:F14,D5:D14=J5,0))`

** Explanation of the Formula**

` `

**FILTER(F5:F14,D5:D14=J5,0)**: The **FILTER** function filters the **CGPA** value of our desired department.

` `

**SUM(FILTER(F5:F14,D5:D14=J5,0))**: Finally, the **SUM** function adds all of them.

- Press
**Enter**.

- Select cell
**J8**and input the following formula for the**average**value:

`=AVERAGE(FILTER(F5:F14,D5:D14=J5,0))`

** Explanation of the Formula**

` `

**FILTER(F5:F14,D5:D14=J5,0)**: The **FILTER** function filters the CGPA value of our desired department.

` `

**AVERAGE(FILTER(F5:F14,D5:D14=J5,0))**: The **AVERAGE** function will calculate the **average** value of those values.

- Press
**Enter**.

- Select cell
**J9**, then insert the following formula inside the cell for getting the**minimum**value:

`=MIN(FILTER(F5:F14,D5:D14=J5,0))`

** Explanation of the Formula**

` `

**FILTER(F5:F14,D5:D14=J5,0)**: The **FILTER** function filters the CGPA value of our desired department.

` `

**MIN(FILTER(F5:F14,D5:D14=J5,0))**: The **MIN** function will figure out the **minimum** value among the **4** values.

- Press
**Enter**.

- Select cell
**J10**and copy the following formula inside the cell for the**maximum**value:

`=MAX(FILTER(F5:F14,D5:D14=J5,0))`

** Explanation of the Formula**

` `

**FILTER(F5:F14,D5:D14=J5,0)**: The **FILTER** function filters the CGPA value of our desired department.

` `

**MAX(FILTER(F5:F14,D5:D14=J5,0))**: The **MAX** function will find out the **maximum** value among the **4** CGPA values.

- Press
**Enter.**

### Method 8 – Filter Data and Return Only Particular Columns

Our desired entity is in cell **J5**. We will only show the **ID** and the **Name** columns.

**Steps:**

- Select cell
**H8**. - Insert the following formula in the cell:

`=FILTER(FILTER(B5:F14,D5:D14=J5),{1,1,0,0,0})`

- Press
**Enter**.

** Explanation of the Formula**

` `

**FILTER(B5:F14,D5:D14=J5)**: The **FILTER** function will return the matched rows from the given dataset with all the columns.

` `

**FILTER(FILTER(B5:F14,D5:D14=J5),{1,1,0,0,0})**: The outer **FILTER** function will select only the first **two** columns of the selected data. We can either use **0**,**1** or **TRUE**, **FALSE**.

### Method 9 – Apply Limitation on Returned Number of Rows

Our desired department is in cell **J5 **but we only want the first two rows. To apply the limitation, we have to use the **IFERROR** and **INDEX** functions.

**Steps:**

- Select cell
**H8**. - Input the following formula in the cell.

`=IFERROR(INDEX(FILTER(B5:F14,D5:D14=J5),{1;2},{1,2,3,4,5}),"No result")`

- Press
**Enter**.

** Explanation of the Formula**

` `

**FILTER(B5:F14,D5:D14=J5)**: The **FILTER** function will return the filtered data by matching it with the input value.

` `

**INDEX(FILTER(B5:F14,D5:D14=J5),{1;2},{1,2,3,4,5})**: This formula 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.

` `

**IFERROR(INDEX(FILTER(B5:F14,D5:D14=J5),{1;2},{1,2,3,4,5}),”No result”)**: Lastly, the **IFERROR** function is used to avoid the error if there is a problem with other function return values.

### Method 10 – Use of Wildcard with FILTER Function

Let’s search for departments that contain C in the abbreviation.

**Steps:**

- Select cell
**H8.** - Put the following formula in the cell:

`=FILTER($B$5:$F$14,ISNUMBER(SEARCH(J5,D5:D14)),"No Results!")`

- Press
**Enter**.

** Explanation of the Formula**

` `

**SEARCH(J5,D5:D14)**: The **SEARCH** function will search the data by matching it with the input value.

` `

**ISNUMBER(SEARCH(J5,D5:D14))**: This formula will check which result of the **SEARCH** function is true,

` `

**FILTER($B$5:$F$14,ISNUMBER(SEARCH(J5,D5:D14)),”No Results!”)**: Lastly, the **FILTER** function will show them in our desired cell.

## What Are the Alternatives to the Excel FILTER Function?

There is no specific alternative to this function. However, the combination of some general Excel functions may return us the results of **the FILTER function**. Among them, the **IFERROR**, **INDEX**, **AGGREGATE**, **ROW**, **ISNA**, and **MATCH** functions are mentionable. But, combining the functions will make the formula more complex to understand.

## What Are the Possible Reasons If the FILTER Function Does Not Work in Excel?

Sometimes, **the FILTER function** of Excel doesn’t work properly, usually due to **#SPILL!**, **#CALC!**, or **#VALUE! **errors. To eliminate this error, you typically need to go back to your original dataset and fix it.

The frequently seen errors in Excel are explained below briefly:

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 closes the source workbook. |

#N/A or #VALUE | This type of error may occur if some value in the included argument is an error or cannot be transformed to a Boolean value (0,1 or TRUE, FALSE). |

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

**<< Go Back to Excel Functions | Learn Excel**