Quick, clean, and to the point

Filter and transpose horizontal to vertical

Excel formula: Filter and transpose horizontal to vertical
Generic formula 

To filter data arranged horizontally and display the result in a vertical format, you can use the FILTER function together with TRANSPOSE.  In the example shown, the formula in B10 is:


where data (C4:L6) and group (C5:L5) are named ranges.


The goal is to filter the horizontal data in the range C4:L6 to extract members of the group "fox" and display results with data transposed to a vertical format. For convenience and readability, we have two named ranges to work with: data (C4:L6) and group (C5:L5).

The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation.  The formula in B5 is:


Working from the inside out, the include argument for FILTER is a logical expression:

group="fox" // test for "fox"

When the logical expression is evaluated, it returns an array of 10 TRUE and FALSE values:


Note: the commas (,) in this array indicate columns. Semicolons (;) would indicate rows.

The array contains one value per record in the data, and each TRUE corresponds to a column where the group is "fox". This array is returned directly to FILTER as the include argument, where it does the actual filtering:


Only data in columns that correspond to TRUE make it through the filter, so the result is data for the six people in the "fox" group. FILTER returns this data in the original horizontal structure. Because we want to display results from FILTER in a vertical format, the TRANSPOSE function is wrapped around the FILTER function:


The TRANSPOSE function transposes the data and returns a vertical array as a final result in cell B10. Because FILTER is a dynamic array function, the results spill into the range B10:D15. If data in data (C4:L6) changes, the result from FILTER is automatically updated.

Dynamic Array Formulas are available in Office 365 only.
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.