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.

Example 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)

Example 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



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language