TRANSPOSE function

This article describes the formula syntax and usage of the TRANSPOSE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Find a link to more information about transposing data in the See Also section.

Description

The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as the source range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.

Syntax

TRANSPOSE(array)

The TRANSPOSE function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • array    Required. An array or range of cells on a worksheet 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 worksheet.

ShowHow do I copy an example?

  • Select the example in this article.

 Important   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  • Press CTRL+C.
  • In Excel, create a blank workbook or worksheet.
  • In the worksheet, select cell A1, and press CTRL+V.

 Important   For the example to work properly, you must paste it into cell A1 of the worksheet.

  • To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1
2
3
4
5
6
A B C
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

 Important   The formula shown in the example must be entered as an array formula for the TRANSPOSE function to work as expected. After copying the example to a blank worksheet, 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. The LINEST function 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 by using the LINEST function.

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  • Select the example in this article.

 Important   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  • Press CTRL+C.
  • In Excel, create a blank workbook or worksheet.
  • In the worksheet, select cell A1, and press CTRL+V.

 Important   For the example to work properly, you must paste it into cell A1 of the worksheet.

  • To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1
2
3
4
5
6
7
8
A B C
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

 Important   The formula shown in the example must be entered as an array formula for the TRANSPOSE function to work as expected. After copying the example to a blank worksheet, 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.

 
 
Applies to:
Excel 2007