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.
Keep Reading Similar Functions:
MATCH
The MATCH function finds row or column of a given value containing in a data set, cell B3 in a list contains ‘Feb’ =MATCH(B3,B1:B10,0) returns 2 (3rd row).
HYPERLINK
The HYPERLINK function creates a shortcut or clickable link of a given location with a “friendly name”. User Click that initiates a call for action.
FORMULATEXT
The FORMULATEXT function displays the syntax of a reference cell as a string, cell B5 contains =5+2 return 7, = FORMULATEXT(B5) returns a text value “=5+2”.
