FILTER

Dynamic array function that returns all rows matching one or more criteria. Results spill automatically into adjacent cells. Replaces manual filtering and copy-paste workflows.

Syntax

excel-mastery

=FILTER(array, include, [if_empty])

Example

excel-mastery

=FILTER(SalesTable, SalesTable[Region]="West", "No data")

=FILTER(SalesTable, (SalesTable[Region]="West")*(SalesTable[Quarter]="Q4"))

=SORT(UNIQUE(FILTER(Names, Department="Engineering")))