Excel TRANSPOSE Function
The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.
- array - The array or range of cells to transpose.
The TRANSPOSE function converts a vertical range of cells to a horizontal range of cells, or a horizontal range of cells to a vertical range of cells. In other words, TRANSPOSE "flips" the orientation of a given range or array:
- When given a vertical range, TRANSPOSE converts it to a horizontal range
- When given a horizontal range, TRANSPOSE converts it to a vertical range
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.
In the example shown, the formulas in I5 and F12 are:
The transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range.
Note: TRANSPOSE does not carry over formatting. In the example, the target ranges have been formatted in a separate step.
Dynamic array vs Traditional array
The formulas above were entered in Excel 365, which supports dynamic array formulas, so 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:
- First select the target range, which should have the same number of rows as the source range has columns, and the same number of columns as the source range has rows.
- Enter the TRANSPOSE function, and select the source range as the array argument.
- Confirm the formula as an array formula with control + shift + enter.
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.