The TRANSPOSE function converts the rows of the selected range into columns and vice versa.
This function is used to change the orientation of data without losing references.
The function is entered as an array formula using the Ctrl+Shift+Enter keyboard shortcut.
Syntax
TRANSPOSE(array)
Argument |
Description |
Permitted values |
|---|---|---|
array |
Range of cells or array of values to be transposed |
Range of cells or a named range |
Examples of use
Transposing a list of months
Initial data in A1:A12: "January", "February", ..., "December"
Select row B3:B15 and enter the formula:
=TRANSPOSE(A1:A12)
Apply it as an array formula (Press Ctrl+Shift+Enter).
Result: all months will be arranged horizontally in a single row in cells B3:B15.
Converting a table to build a chart
Source data: table with 9 rows × 5 columns.
Select the range of 5 rows × 9 columns, enter the formula:
=TRANSPOSE(B2:F10)
Apply it as an array formula.
Result: a table with 5 rows × 9 columns.
Notes
–The formula must be entered as an array formula: select the range for the results, enter the formula, and press Ctrl+Shift+Enter.
–The size of the target range must exactly match the transposed array.
–When the source data changes, the result is automatically updated.