How to use Excel Filter Function

Quick view of Excel Filter Function

Explanation: We have filtered Sales man name, Sales Amount, Department (entire data) based on the condition that department is equal to textiles. You can see the results form Cell F8 to H10.

Excel Filter Function

The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records.

Purpose (When to use Filter Function)

Filters range with given criteria or condition. This is dynamic compared to regular filter option under Data tab in excel.

Return value (Result)

Array of filtered values. We can select the required columns to shown after filter.

Syntax (Rules to use Filter Function)

=FILTER (array, include, [if_empty])

Total we have 3 arguments in Filter function, [If_empty] has square brackets around it so it is an optional argument.

Arguments (Parts of Syntax)

array – Range or array to filter.
include – Boolean array, supplied as criteria.
if_empty – [optional] Value to return when no results are returned.

Examples of Filter Functions

Example1

Explanation Example1

We have only filtered Sales man name and Sales Amount based on the criteria that department is equal to Textiles.

Example2

Explanation Example2

We have only filtered only Sales Amount based on the criteria that department is equal to Textiles and then top of that we have included sum function so that we can get total sales of Textiles.

Example3

Explanation Example3

We have only filtered only Sales Amount based on the criteria that department is equal to Textiles and then top of that we have included count function so that we can get total count of Textiles.

Example4

Explanation Example4

We have only filtered entire data based on the criteria that department is equal to Textiles and here we have added another condition by adding * symbol and the second condition is sales amount is greater than 35000.

Example5

Explanation Example5

We have only filtered entire data based on the condition that 3 rows should contain some content if there is blank it will get skipped in the resulted table.