TRANSPOSE function. Create a cell range with the rows and columns of the specified cell range swapped.(Microsoft Excel)
Create a cell range with the rows and columns of the specified cell range swapped.
How it works
=TRANSPOSE(array)
| Name | Omission | Explanation |
|---|---|---|
| array | Required argument. | Specify a range of cells to swap rows and columns. |
Demonstrate
Excel in a version compatible with Spill
In Excel 2019 or later and Office365, it is used in Spill.
To do so, first specify the cell range of the table in the TRANSPOSE function argument.
Once the formula is finalized, it is automatically expanded by the necessary amount.
Versions of Excel that do not support Spill
If Spill is not supported, an Array formula is required.
To do so, first specify the cell range of the table as the argument to the TRANSPOSE function.
Cell copy the TRANSPOSE function.
All will result in a #VALUE! error, but at this point it is correct as is.
Select all TRANSPOSE function cells for the first time starting with the top left-most cell and put the formula bar in input mode.
In this state, hold down [Ctrl]+[Shift] and press [Enter].
The result will be an array formula surrounded by {} and the result of the matrix swap will be displayed, If it is outside the cell range, a #N/A error will result.
Comparison with similar functions
Paste by Transpose
Select a range of cells, then copy and select "Paste Special".
Then check the "Transpose" checkbox", "OK" button to paste with the matrices swapped.
In the case of the TRUNSPOSE function, it is the same as pasting a link (cell reference).
If you update the original table, the function will also change its display, However, this method does not reflect the update of the original table because it is a value pasting.
OFFSET, ROW, and COLUMN Functions
A combination of the OFFSET, ROW, and COLUMN functions can also be used to achieve this.
=OFFSET(Start cell of the referenced table [absolute cell reference] , COLUMN() - Number of columns in the start cell of the OFFSET function, ROW() - Number of rows in the start cell of the OFFSET function)
The formulas are more complicated, but the method is easier to use because it does not use Array formula.
However, in versions where Spill is available, it is better.
---