Switch reference data from rows to columns or vice versa in Excel
Returns the transpose of an array


Switch reference data from rows to columns or vice versa


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.


=TRANSPOSE (array)


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:

To display formula as text in Excel


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”.

Read More »
Create a shortcut of a given path in Excel


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.

Read More »
Returns the relative cell position of value


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).

Read More »