|
|
TRANSPOSE
Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula in a range that has the same number of rows and columns, respectively, as an array has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a spreadsheet.
Syntax
TRANSPOSE(array)
Array is an array or range of cells on a spreadsheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.
Example 1
The example may be easier to understand if you copy it to a blank spreadsheet.
How?
- Create a blank spreadsheet.
- Select the example in the Help topic.
Selecting an example from Help
- Press CTRL+C.
- In the spreadsheet, select cell A1, and press CTRL+V.
- To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.
| Data | Data | Data |
|---|---|---|
| 1 | 2 | 3 |
| Formula | Description (Result) | |
| =TRANSPOSE($A$2:$C$2) | Value from first column (1) | |
| Value from second column (2) | ||
| Value from third column (3) |
Note The formula in the example must be entered as an array formula. After copying the example to a blank spreadsheet, select the range A4:A6 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 1.
Example 2
Some functions, such as LINEST, return horizontal arrays. LINEST returns a horizontal array of the slope and Y-intercept for a line. The following formula returns a vertical array of the slope and Y-intercept from LINEST.
The example may be easier to understand if you copy it to a blank spreadsheet.
- Create a blank spreadsheet.
- Select the example in the Help topic.
Selecting an example from Help
- Press CTRL+C.
- In the spreadsheet, select cell A1, and press CTRL+V.
- To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.
| Known y | Known x |
|---|---|
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| Formula | Description (Result) |
| =TRANSPOSE(LINEST(A2:A5,B2:B5,,FALSE)) | Slope (2) |
| Y-intercept (1) |
Note The formula in the example must be entered as an array formula. After copying the example to a blank spreadsheet, select the range A7:A8 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.