FILTER

Also known as · FILTER function

A dynamic-array function (Excel 365 and 2021) that returns the matching rows as a live table that spills into the cells below and to the right. Multiply conditions for AND and add them for OR.

When to use

A formula such as =FILTER(A2:J201,(E2:E201="Sales")*(H2:H201>10)) replaces the Criteria Range approach for most tasks. Leave room for the Spill Range or you get #SPILL!, and do not include the header row in the first argument.

See Lec 01.

Appears in

No references yet.