TRANSPOSE can be used with bothrangesandarrays.

Transposed ranges are dynamic.

If data in the source range changes, TRANSPOSE will immediately update data in the target range.

Excel formula: Dynamic two-way count

In the example shown, the target ranges have been formatted in a separate step.

TRANSPOSE with other functions

TRANSPOSE can “catch” and transpose the output from another function.

Excel formula: Filter horizontal data

Excel formula: Dynamic two-way sum

Excel formula: Multiple matches into separate rows

Excel formula: INDEX and MATCH exact match

Excel formula: Weighted average

Excel formula: Sort and extract unique values

Excel formula: List sheet names with formula

Excel formula: XLOOKUP wildcard contains substring

Excel formula: Flip table rows to columns

Excel formula: Filter and transpose horizontal to vertical

Excel formula: Dynamic two-way average

Excel formula: Count columns that contain specific values

Excel formula: Count cells that do not contain many strings

Excel formula: Count rows that contain specific values

Article image