TRANSPOSE

Rational

Switch reference data from rows to columns or vice versa

Description

The Excel TRANSPOSE function “switch data direction” the range of data from vertical format (row) to horizontal format (column), or vice versa.
Example: If a data spread in eight rows and two columns (B4:Cll), the data will reverse to eight columns and two rows (E4:L5), refer to the figure above.

Syntax

=TRANSPOSE (array)

Parameters

array – Array or range of cells to transpose

Return Value

Transposed data.

Key Notes

The TRANSPOSE function returns the same value but in different data distribution format by rotating vertical data to horizontal data, or vice versa. Reference data presentation of rows and columns reverse.

To transpose a set of data, you must select an area that covers the same number of cells in the reverse format of data distribution and enters the formula in the first cell.
For Example: If the area of data (array) is eight rows and two columns, select an area that is eight columns and two rows.

Excel can transpose easily by using Paste Special dialog box (Home> Find & Select> Go To Special or Alt+E+S) then choose transpose. However, this switch data without referring to the original data. So, no change in original data will reflect in transposed data automatically in this case.

The TRANSPOSE function must be entered as an array formula into the same number of cells by pressing Ctrl+Shift+Enter.
The formatting of transpose data may not be same as the original data.