Switching Vertical and Horizontal Data with Excel 2007's TRANSPOSE Function

Excel 2007's TRANSPOSE function enables you to change the orientation of a cell range (or array). You can use this function to transpose a vertical cell range where the data runs down the rows of adjacent columns to one where the data runs across the columns of adjacent rows and vice versa. To successfully use the TRANSPOSE function, not only must you select a range that has an opposite number of columns and rows, but you must also enter it as an array formula.

For example, if you’re using the TRANSPOSE function to transpose a 2 x 5 cell range (that is, a range that takes up two adjacent rows and five adjacent columns), you must select a blank 5 x 2 cell range (that is, a range that takes five adjacent rows and two adjacent columns) in the worksheet before you use the Insert Function button to insert the TRANSPOSE function in the first cell. Then, after selecting the 2 x 5 cell range that contains the data that you want to transpose in the Array text box of the Function Arguments dialog box, you need to press Ctrl+Shift+Enter to close this dialog box and enter the TRANSPOSE function into the entire selected cell range as an array formula (enclosed in curly braces).

Suppose that you want to transpose the data entered into the cell range A10:C11 (a 2 x 3 array) to the blank cell range E10:F12 (a 3 x 2 array) of the worksheet. When you press Ctrl+Shift+Enter to complete the array formula, after selecting the cell range A10:A11 as the array argument, Excel puts the following array formula in every cell of the range:

{=TRANSPOSE(A10:C11)}

If all you want to do is transpose row and column headings or a simple table of data, you don’t have to go through the rigmarole of creating an array formula using the TRANSPOSE function. Simply copy the range of cells to be transposed with the Copy button on the Home tab of the Ribbon. Position the cell cursor in the first empty cell where the transposed range is to be pasted before you click the Transpose option on the Paste button’s drop-down menu.

dummies

Source:http://www.dummies.com/how-to/content/switching-vertical-and-horizontal-data-with-excel-.html