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

Other functions
Returns a reference as text to a single cell in a worksheet
Returns the number of areas in a reference
Chooses a value from a list of values
Returns the column number of a reference
Returns the number of columns in a reference
Returns the formula at the given reference as text
Returns data stored in a PivotTable report
Looks in the top row of an array and returns the value of the indicated cell
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
Uses an index to choose a value from a reference or array
Returns a reference indicated by a text value
Looks up values in a vector or array
Looks up values in a reference or array
Returns a reference offset from a given reference
Returns the row number of a reference
Returns the number of rows in a reference
Retrieves real-time data from a program that supports COM automation
Sorts the contents of a range or array
Sorts the contents of a range or array based on the values in a corresponding range or array
Returns the transpose of an array
Returns a list of unique values in a list or range
Looks in the first column of an array and moves across the row to return the value of a cell
Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match
Returns the relative position of an item in an array or range of cells