Summary

The Excel FILTER function is used to extract matching values from a range or array based on one or more specified conditions. It dynamically returns a subset of the data that meets the given criteria. FILTER is particularly useful for isolating specific data within a dataset without altering the original data, making it an invaluable tool for analysis and reporting.

Syntax

				
					=FILTER(array, include, [if_empty])

array: The range or array to filter
include: The Boolean array, supplied as criteria, which determines which elements to include in the output
[if_empty]: [Optional] The value to return when no results are returned from the filter

Return value

An array of values that meet the specified criteria.

How to use

Use FILTER by specifying the data range or array you want to filter and providing a logical test (or tests) in the include argument. These tests return TRUE or FALSE to determine which data to include in the output. If no data meets the criteria, the function can return an optional value, like a custom message or an empty string.

Examples

Simple FILTER

Filtering Data Based on Numeric Criteria: To extract values greater than a certain threshold:

				
					=FILTER(A2:A10, A2:A10 > 100)

This formula filters the range A2:A10, returning only those values greater than 100.

FILTER for Specific Group

Filtering Data Based on a Textual Match: To filter data from a specific group:

				
					=FILTER(B2:D20, D2:D20 = "Blue Group", "No results")

This formula filters the range B2:D20 based on the condition in column D, returning records associated with the “Blue Group.” If no match is found, it returns “No results.”

FILTER with Multiple Criteria

Applying Multiple Conditions to Filter Data: To filter data meeting multiple conditions:

				
					=FILTER(B2:D10, (B2:B10 > 50) * (C2:C10 = "Approved"), "No matching data")

This filters the range B2:D10 for values in column B greater than 50 and with a status of “Approved” in column C. If no data matches, it returns “No matching data”.

FILTER with Date Criteria

Filtering Data Based on Date Conditions: To extract records from a specific month:

				
					=FILTER(A2:B10, MONTH(B2:B10) = 7, "No data for July")

This filters records in A2:B10 where the date in column B falls in July.

FILTER for Text Containment

Extracting Data Containing Specific Text: To filter data containing a certain substring:

				
					=FILTER(A2:B20, ISNUMBER(SEARCH("Tech", B2:B20)), "No Tech data")

This formula searches for the text “Tech” in column B and filters the range A2:B20 accordingly.

Additional Notes

The FILTER function is dynamic; changes in source data or criteria automatically update the results.
It’s compatible with both vertical and horizontal arrays.
If the criteria array dimensions are not compatible with the source array, FILTER returns a #VALUE! error.
FILTER does not inherently support wildcards, but can be combined with functions like SEARCH for more complex text matching.