TRANSPOSE function

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 sheet.

Syntax

TRANSPOSE(array)

Argument Description
array An array or range of cells on a sheet 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.

ShowExample 1: Transpose a vertical range of cells

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

 Note   The formula in the example must be entered as an array formula. First, type the formula into cell A4 and then press RETURN . The single result is 1. Next, select the range A4:A6, press CONTROL + U , and then press COMMAND + RETURN . The array results, from top to bottom, are 2 and 3.

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)  

ShowExample 2: Transpose LINEST results

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.

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

 Note   The formula in the example must be entered as an array formula. First, type the formula into cell A7 and then press RETURN . The single result is 2. Next, select the range A7:A8, press CONTROL + U , and then press COMMAND + RETURN . The array result is 1.

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)

See also

MDETERM function

MINVERSE function

MMULT function

List of all functions (by category)

 
 
Applies to:
Excel for Mac 2011