What TRANSPOSE does
TRANSPOSE turns a vertical list into a horizontal one, or swaps the two axes of a whole grid. A source with two rows and three columns becomes a result with three rows and two columns. The formula reads the source cells again when Excel recalculates, so changes to those cells appear in the result.
Practical examples
Turn a color list into a row
Enter Red in A2, Green in A3, and Blue in A4. In Excel with dynamic arrays, leave C2:E2 empty and enter this in C2:
=TRANSPOSE(A2:A4)FormulaThe expected result is one row across C2:E2:
Red Green BlueExampleThe first source cell becomes the first result cell; the other colors follow in the same order.
Turn a two-row grid into three rows
On a separate sheet, enter these values in A2:C3:
| Row | A | B | C |
|---|---|---|---|
| 2 | 1 | 2 | 3 |
| 3 | 4 | 5 | 6 |
Leave E2:F4 empty and enter this in E2:
=TRANSPOSE(A2:C3)FormulaThe expected result in E2:F4 is:
1 4
2 5
3 6ExampleSource column A becomes the first output row, source column B the second, and source column C the third.
Common mistakes and notes
Reverse the dimensions when reserving space
Count the source rows and columns before choosing the destination. A two-by-three source needs a three-by-two destination. In dynamic-array Excel, enter the formula only in the top-left result cell and leave the rest of the output area clear.
Older Excel may require array entry
In versions without dynamic arrays, select the entire destination of the correct size before entering TRANSPOSE and confirm it as a legacy array formula. Microsoft documents Ctrl+Shift+Enter for the Windows method. Don't type the surrounding braces yourself.
A formula result remains linked to its source
Keep the source cells if you want the transposed result to continue working. For a one-time copy, use the paste method in the transpose tutorial. TRANSPOSE changes the layout of the returned values; it doesn't copy the source formatting.
Flattening a grid is a different operation
TRANSPOSE preserves two dimensions. Use TOCOL for one long column or TOROW for one long row.