When array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on. TRANSPOSE can be used with both ranges and arrays. Transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range.
Examples
When given a vertical array, TRANSPOSE returns a horizontal array: To transpose the vertical range A1:A5 into a horizontal array: To transpose the horizontal range A1:E1 to a vertical array: In the example shown above, the formulas in I5 and F12 are: Note: TRANSPOSE does not carry over formatting. In the example shown, the target ranges have been formatted in a separate step.
TRANSPOSE with other functions
TRANSPOSE can be used to “catch” and transpose the output from another function. The formula below changes the result from XLOOKUP from a horizontal configuration to a vertical configuration: Read more: XLOOKUP wildcard example.
Excel 365
In Excel 365, which supports dynamic array formulas, no special syntax is required, TRANSPOSE simply works and results spill into destination cells automatically. However, in other versions of of Excel, TRANSPOSE must be entered as a multi-cell array formula with control + shift + enter:
Paste special
The TRANSPOSE function makes sense when you need a dynamic solution that will continue to update when source data changes. However if you only need a one-time conversion, you can use Paste Special with the Transpose option. This video covers the basics of Paste Special.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
