Explanation

Note: FILTER is a newdynamic arrayfunction inExcel 365.

In other versions of Excel, there arealternatives, but they are more complex.

There are ten columns of data in the range C4:L6.

FILTER and TRANSPOSE horizontal data

TheFILTER functioncan be used to extract data arranged vertically (in rows) or horizontally (in columns).

FILTER will return the matching data in the same orientation.

No special setup is required.

FILTER and TRANSPOSE horizontal data

Semicolons (;) would indicate rows.

FILTER returns this data in the original horizontal structure.

Because FILTER is adynamic array function, the resultsspillinto the range C9:H11.

FILTER and TRANSPOSE horizontal data

Filter on age

The same basic formula can be used to filter the data in different ways.

The output from FILTER is dynamic.

If source data or criteria change, FILTER will return a new set of results.

FILTER and TRANSPOSE horizontal data

FILTER function basic example

FILTER and TRANSPOSE horizontal data

FILTER columns by age < 22

Excel formula: Basic filter example

Excel formula: Filter this or that

Excel formula: Filter text contains

Excel formula: Filter exclude blank values

Excel FILTER function

Excel TRANSPOSE function

Article image