The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records. The purpose is to filter a range with given criteria. The return value is an array of filtered values.

1 Syntax

= FILTER (array, include, [if_empty])

The breakdown of this is:

• Array indicates the range or array to filter.
• Include indicates one or more logical tests (criteria).
• If_empty is an optional argument that specifies which value to return when no results are returned.

The results from FILTER are dynamic. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically.

2 Example

Suppose you want to know the data of region East

= FILTER(A4:H11;B4:B11=$L$1;”geen data”)

Since the value in L1 is “EAST”, the FILTER function extracts data from array where the Group column contains “EAST”. All matching records are returned to the worksheet starting from cell J4, where the formula exists.

Values can be hardcoded as well.

3 Remarks

• Filter function is possible by date
• More than 1 criteria are possible
• Filter can work with both vertical and horizontal arrays
• The include argument must have dimensions compatible with the array argument, otherwise FILTER will return #VALUE!
• If the include array includes any errors, FILTER will return an error.