The Excel Transpose function is used to switch data displayed in a row into a column and vice versa. It is the formula equivalent of the Transpose option found under Paste Special. The Paste Special option is useful if you want to perform a one time paste of the data.
The Transpose function is written as below:
{ =TRANSPOSE(array) }
The array argument is the range of cells that you need to convert from rows to columns, or columns to rows.
The curly braces indicate that it is an Excel array function. The curly braces are not entered manually, but rather appear by entering the function by pressing Ctrl, Shift and Enter. Pressing the Enter like when entering regular Excel functions will result in an error message. The Ctrl, Shift and Enter buttons must be used when entering and editing the formula.
Using the Transpose Function in Excel
Let’s have a look at using the Transpose function in Excel to convert the data shown below.

1.   Select the cells on the spreadsheet where you want to transposed data to be displayed. In this example I will select the cells F1:G4
A tip at this stage is to select the current range of cells and look in the Name box whilst you select them. The name box will display the number of rows and columns being selected. This can then be reversed to know how many rows and columns you need to select. This is very helpful when transposing large ranges.
The example below shows 2R x 4C in the Name box. This stands for 2 rows and 4 columns and means the transposed range will be 4 rows and 2 columns.
2.       Click the Formulas tab on the Ribbon
3.       Click the Lookup & Reference button and select Transpose
4.       Select the cells A1:D2 on the sheet
5.       Press Ctrl, Shift and Enter to run the formula
The data has been converted into columns. Select any one of the cells in the range F1:G4 to see the Transpose function written as an array. A useful thing about array functions is that they keep the formula consistent in all the cells.

No comments:

Post a Comment