How to Use the FILTER Function in Excel

The FILTER function in both Microsoft Excel and Google Sheets enables users to filter a range of data based on specified criteria. This function proves invaluable for extracting particular information from a dataset, bypassing the need for manual sorting. Below, we will detail the operation of the FILTER function, providing syntax and practical examples in both applications.

Basic Syntax

The basic syntax for the FILTER function is quite similar in both Microsoft Excel and Google Sheets. Here is the formula structure:

=FILTER(range, include, [if_empty])

• range: This specifies the cells you wish to filter.
• include: This defines the condition that each row must satisfy to be included in the filtered result.
• if_empty: An optional argument that provides a value to return when no results meet the criteria. If omitted, an empty string is returned by default.

Using FILTER in Excel

In Excel, the FILTER function is available exclusively to Microsoft 365 subscribers using the most recent version of Excel. To apply the FILTER function in Excel, follow these steps:

1. Select the cell where you wish the filtered results to be displayed.
2. Type the FILTER formula with the designated range and conditions. For instance:

=FILTER(A2:B10, B2:B10="Apple")

This formula filters the range A2:B10 based on column B, including only rows where “Apple” appears in column B.

Using FILTER in Google Sheets

Google Sheets users can readily access the FILTER function without any special prerequisites. To use it, proceed as follows:

1. Select the cell where the filtered results should appear.
2. Enter the FILTER formula with the required parameters. For example:

=FILTER(A2:B10, B2:B10="Banana", "No results found")

This example, similarly to the one in Excel, filters the range A2:B10 by the condition in column B, displaying “No results found” if no matches are made.

Practical Applications

The FILTER function is highly versatile and can be employed for diverse scenarios. Some common applications include:

• Filtering data based on specific conditions, such as text, numbers, or dates.
• Isolating records that satisfy defined criteria for deeper analysis.
• Generating dynamic reports that automatically refresh when the underlying criteria change.

By becoming proficient with the FILTER function in Excel and Google Sheets, users can effectively handle, analyze, and derive significant insights from their data with ease.

More information: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759