The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records.
Syntax:= FILTER (array, include, [if_empty])
The FILTER function syntax has the following arguments:
- array – is the range or array you want to be included in the result. This can be the entire data set or part of the original data set.
- include – is the test you are performing on each record in the data set. This is the engine that moves the FILTER function forward. Here is where you define the criteria for included versus excluded records.
The logic will resemble the test portion of an IF function; we select a range of cells and compare each item in the range to a defined value (ex: B8:B40=E6, where column B is the data and cell E6 is what the data is being compared against.) Any record that results in a TRUE statement will be included in the results. Any record that results in a FALSE statement will be excluded from the results.
- [if_empty] – defines what is to be displayed if no records match the include test (ex: “No Data”). Although this argument is optional, if it is not defined, and no data is returned from the include test, a #CALC! error will be displayed.
Example: Let’s look at some Excel FILTER function examples and explore how to use the FILTER function as a worksheet function in Microsoft Excel:
We will start with a simple text filter and filter the data below to only see the employees from New York:
The following formula returns all columns of data if “New York” is found in column C. Notice the height of the Array and Include range of equal.
Syntax: =FILTER(A2:F17;C2:C17=“New York”)
The results are positioned under the data set here for better visuals for the article. The filter function can return the results to a different sheet or workbook, no problem.
The formula was entered into cell A20 but spilled into the range A20:F23.
This spill effect is a unique feature of the new dynamic array formulas. The formula only resides (and be edited) in cell A20, but its results spill to the array of cells outside.
If there was content in the area it needed to use, a SPILL error would be shown.
- Filter can work with both vertical and horizontal arrays.
- The include argument must have a dimension compatible with the array argument, otherwise filter will return #VALUE!
- If the include array includes any errors, FILTER will return an error.
- If FILTER is used between workbooks, both workbooks must be open, otherwise FILTER will return #REF!.