hmm, try this from the ever-helpful (!) help function..
TRANSPOSE
Show All
Hide All
Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE 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 an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
Syntax
TRANSPOSE(array)
Array is 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.
How?
Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1
2
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)
Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A5:A7 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 worksheet.
