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.