How to Use FILTER Function in Excel?

Excel 365 provides us with 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. If you are also curious about it, download our practice workbook and follow us.

Introduction to the FILTER Function in Excel

Function Objective:

Filter some particular cells or values according to our requirements.

Syntax:

Arguments Explanation:

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.

1. Performing AND Operation with FILTER Function for Multiple Criteria

In the first example, we will perform the AND operation by the FILTER function. Our desired conditions are in the range of cells C5:C6.

The steps to complete this example are given below:

📌 Steps:

• First of all, select cell B10.
• Now, write down the following formula in the cell.

• Then, press Enter.

• You will get the filtered result in the range of cells B10:F11.

Thus, we can say that we are able to apply the FILTER function for the AND operation.

2. Application of OR Operation with FILTER Function for Multiple Criteria

In the second example, we are going to use the FILTER function for the OR operation. Here, we mentioned the conditions in the range of cells C5:C6.

The steps to finish this example are given as follows:

📌 Steps:

• First, select cell B10.
• After that, write down the following formula in the cell.

• Press Enter.

• You will figure out the filtered result in the desired cells.

Hence, we are able to use the FILTER function perfectly for the OR operation.

3. Combination of AND and OR Logic with FILTER Function

Now, we will use the FILTER function for a combined AND and OR operation. The conditions are in the range of cells C5:C7.

The steps to accomplish this example are given below:

📌 Steps:

• At first, select cell B11.
• Afterward, write down the following formula in the cell.

• Press the Enter.

• You will notice the filtered result will be available in the cells.

Therefore, our formula works effectively and we are able to perform the AND and OR operations simultaneously by the FILTER function.

4. Filtering Duplicates Using FILTER Function

In this example, we are going to filter out the duplicate entities from our dataset. Our dataset contains 2 duplicate entities.

The steps of this example are given below:

📌 Steps:

• In the beginning, select cell H5.
• Next, write down the following formula in the cell.

• Thus, press the Enter.

• You will see that all the duplicate values are listed separately.

At last, we can say that our formula works precisely and we are able to figure out the duplicates by the FILTER function in Excel.

5. Find Out Blank Cells by FILTER Function

We have a dataset with some blank cells. Now, we are going to filter out the cells that don’t contain any blank function with the help of the FILTER function.

The procedure to filter out the complete rows is given below::

📌 Steps:

• Firstly, select cell H5.
• Next, write down the following formula in the cell.

• After that, press Enter.

• You will get those entities that don’t have any blank cells.

So, we can say that our formula works fruitfully and we are able to get the value with no blank cells by the Excel FILTER function.

6. Filter Cells That Contain Specific Text

Using the FILTER function, we can easily search for any particular value and filter out the corresponding entities from our original dataset. Besides the FILTER function, the ISNUMBER and SEARCH functions also help us to complete the formula. Our desired text ‘Ellie’ is displayed in cell J4.

The approach of filtering out the data for a specific text is described below::

📌 Steps:

• At the start, select cell H7.
• Then, write down the following formula in the cell.

• Next, press the Enter key.

• You will get the result with that particular text.

Thus, we are able to apply the formula successfully and get the value for our specific text value.

7. Calculation of Summation, Maximum, Minimum, and Average

Now, we are going to perform some mathematical calculations with the help of the FILTER function. 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.

Besides the FILTER function, the SUM, AVERAGE, MIN, and MAX functions will be used for completing the evaluation process. The estimated value will be in the range of cells J7:J10. The calculation procedure is explained below step-by-step:

📌 Steps:

• First of all, select cell J7.
• Now, write down the following formula in the cell for the summation.

• Press Enter.

• After that, select cell J8, and write down the following formula for the average value.

• Again, press Enter.

• Then, select cell J9, and write down the following formula inside the cell for getting the minimum value.

• Similarly, press the Enter.

• Finally, select cell J10, and write down the following formula inside the cell for the maximum value.

• Press Enter for the last time.

• You will notice all the values for the CSE department will be available.

Hence, we can say that all of our formulas work perfectly, and we are able to get all the desired values by the Excel FILTER function.

8. Filter Data and Return Only Particular Columns

Here, we are going to use the FILTER function twice in a nested condition to get the particular columns based on our desired value. Our desired entity is in cell J5. We will only show the ID and the Name column.

The steps of this process are given below:

📌 Steps:

• First, select cell H8.
• Then, write down the following formula in the cell.

• After that, press Enter.

• You will get only the ID and Name column of our desired department.

Therefore, we can say that our formula works properly, and we are able to get some specific columns by the Excel FILTER function.

9. Apply Limitation on Returned Number of Rows

In this case, we will add some limitations to the FILTER function for getting the limited number of rows. Our desired department is in cell J5. To apply the limitation, we have to use the IFERROR and INDEX functions also.

The steps of this method are described as follows:

📌 Steps:

• First, select cell H8.
• Next, write down the following formula in the cell.

• Then, press Enter.

• You will get the result.

So, we can say that we are able to successfully apply the Excel FILTER, INDEX, and IFERROR functions successfully.

10. Use of Wildcard with FILTER Function

In the last example, we are going to apply the filter wildcard for filtering the data. We will apply the formula with the help of ISNUMBER, SEARCH, and FILTER functions. Our desired value is in cell J5.

The process is explained below step-by-step:

📌 Steps:

• Firstly, select cell H8, and write down the following formula in the cell.

• Now, press Enter.

• You will get all the results with the cell value C.

Finally, we can say that our formula works precisely, and we are able to create a wildcard with the Excel FILTER function.

What Are the Alternatives to the Excel FILTER Function?

From our previous application, you may notice that the Excel FILTER function is a handy function for getting our desired values within a short period of time. 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, we recommend that if you have the FILTER function, go for it. The combination of those functions will make the formula more complex to understand others. Besides that, it may slow down your Excel application.

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

Sometimes, the FILTER function of Excel doesn’t work properly. Most of the time, it occurs due to the presence of error. Mainly, the #SPILL!, #CALC!, #VALUE! errors usually don’t allow the FILTER function to work, and return the desired data. To eliminate this error, go back to your original dataset and fix it, and you will find that the FILTER function will work smoothly.

The frequently seen errors in Excel are explained below briefly:

Download Practice Workbook

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

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to apply the FILTER function in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

<< Go Back to Excel Functions | Learn Excel