Excel TRANSPOSE function

Summary

The TRANSPOSE function in Excel is used to switch or “transpose” the rows and columns of a range or array. This means it converts vertical ranges to horizontal ranges and vice versa. TRANSPOSE is particularly useful in reorganizing data, especially when dealing with data sets that are more conveniently analyzed or viewed in a different orientation.
Syntax
				
					=TRANSPOSE(array)
				
			
  • array: The range of cells or array to be transposed.
Return value
Returns a transposed range or array.

How to use

To use the TRANSPOSE function, simply input the range or array you want to transpose. When entering the formula, it’s important to select the range where you want the transposed data to appear. After writing the formula, instead of pressing Enter, you need to press Ctrl+Shift+Enter (in Excel versions before Excel 365) to enter it as an array formula. In Excel 365 and later, it’s entered as a dynamic array formula automatically.

Examples

Simple TRANSPOSE
Transposing a Single Row to a Column: To transpose data from a single row into a column:
				
					=TRANSPOSE(A1:E1)
				
			
This formula transposes the data horizontally from row 1, columns A to E, into a vertical range. If entered in Excel 365, the result will automatically spill into the adjacent cells downward.
TRANSPOSE for Data Reorganization
Switching Rows and Columns in a Data Table: Reorganizing a Data Table Layout:
				
					=TRANSPOSE(A1:D5)
				
			
This formula transposes a 4-row by 5-column data table (A1:D5) into a 5-row by 4-column layout. It’s particularly useful for reorienting data to fit different analysis or presentation requirements.
TRANSPOSE with Dynamic Arrays
Dynamic Array Transposition in Excel 365: Utilizing Dynamic Arrays for Easy Data Manipulation:
				
					=TRANSPOSE(A1:B4)
				
			
In Excel 365, this formula transposes a 2-column, 4-row range (A1:B4) into a 4-column, 2-row range. The transposed data automatically fills into the adjacent cells.

Additional Notes

  • In Excel versions prior to Excel 365, TRANSPOSE must be entered as an array formula using Ctrl+Shift+Enter.
  • The transposed array cannot overlap the original array. Ensure you have enough space in the destination area for the transposed data.
  • TRANSPOSE is a useful tool for data preparation, especially when dealing with mismatched data structures.

Related Functions

Excel OFFSET function

The Excel OFFSET function creates dynamic ranges by returning references shifted from a start point by specified rows and columns.

Content Navigation