Excel - FILTER Function



FILTER Function

The Excel filter function permits you to filter the source data dynamically and extracts only those values depending upon the constraints the user specifies. It is part of Microsoft Excel's Dynamic Array functions category, which consistently handles arrays without pressing the keys Ctrl+Shift+Enter. Filter function was introduced in EXCEL 365.

It is helpful in the Data cleaning, Transformation, and Extraction methods. For example if you wish to retrieve the names of only those mountain climbers who have covered 2000 meters tracking.

Compatibility

This Dynamic Array function is compatible with the following versions of Microsoft Excel −

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
  • Excel 2024
  • Excel 2024 for Mac
  • Excel 2021
  • Excel 2021 for Mac
  • Excel for iPad
  • Excel for iPhone
  • Excel for Android tablets
  • Excel for Android phones

Syntax

The syntax of the FILTER function is as follows −

=FILTER(array,include,[if_empty])

Arguments

You can use the following arguments with the FILTER function −

Argument Description Required / Optional
array It specifies the array or selected range of cells. Required
include It denotes the condition applied to the specific range of cells. Required if_empty A text string is displayed if the cell range does not match the criteria. Optional

Points to Remember

  • Once you enter the Filter function, ensure the worksheet contains enough empty cells to fill the dynamic array values; otherwise, a #SPILL error may occur.
  • If the selected cell range does not satisfy the criteria and the third argument is empty. In this case, the FILTER function will retrieve the #CACL! Error.
  • If the second argument contains the invalid condition, the Filter function will retrieve the #VALUE! error.

Examples of FILTER Function

Practice the following examples to learn the use of the FILTER function in Excel.

Example 1

The FILTER function in Excel is used to extracting specific data from a range based on criteria.

Solution

Step 1 − First, consider the sample dataset comprised of various columns named Job_rl, Monthly Salary, and Department.

FILTER Function

Step 2 − Since you only wish to Job_rl, Monthly Salary, and Department, the dynamic array will be the cell values for only these columns that contain the cell range B2:D12. Write the formula =FILTER(B2:D12,C2:C12>L2) in the J5 cell and press the Enter tab.

FILTER Function 1

Therefore, the Filter function will filter the dataset and display records after satisfying the condition that the monthly salary is greater than 45,000.

FILTER Function 2

Note − You enter the Filter function in the J6 cell, and it automatically populates the multiple values. In the earlier array formula, pressing the Enter key displays the Error.

Example 2: Wildcard Condition using Filter Function

The use wildcard characters in the FILTER function to match partial text criteria.

Solution

Lets say you want to display only those job roles whose role ends with the three-letter eer.

First, enter the formula =FILTER(B2:H12,RIGHT(B2:B12,LEN(L2))=L2,"Not exist") in the J5 cell and hit the Enter button. In this Filter formula, the first argument specifies the cell range B2:H12, and the second argument denotes the RIGHT function implied in the cell range B2:B12 and determines the length of the L2 cell reference.

FILTER Function 3

Therefore, the Filter function retrieves only those records whose job role ends with eer.

FILTER Function 4

Download Practice Sheet

You can download and use the sample data sheet to practice the FILTER function.

Advertisements