TRANSPOSE
Converts a vertical range to horizontal, or horizontal to vertical.
TRANSPOSE(array)Arguments
arrayRange or array to transpose
=TRANSPOSE(A1:A5)Column to row
=TRANSPOSE(A1:E1)Row to column
=TRANSPOSE(A1:C3)Rows become columns
- •In older Excel, enter with Ctrl+Shift+Enter
- •In Excel 365, spills automatically
- •Rows and columns are swapped
- •Forgetting TRANSPOSE needs to be entered as an array formula in older Excel versions (Ctrl+Shift+Enter), or it only returns a single value instead of the full flipped range
- •Not selecting an output range with the correct swapped dimensions beforehand in legacy Excel - a 3x5 range needs a 5x3 output selection for TRANSPOSE to fill correctly
- •Using TRANSPOSE for a one-time layout fix when Paste Special > Transpose would be simpler, since TRANSPOSE creates a live formula link that updates with the source, which isn't always wanted
SORTReorders values within their existing row/column shape rather than flipping rows and columns like TRANSPOSE.INDEXCan be combined with ROW and COLUMN to build a manual transpose formula in tricky edge cases.UNIQUEOften combined with TRANSPOSE when arranging a list of unique values into a different orientation.Why does TRANSPOSE only show one value instead of the full range?
In older Excel versions, TRANSPOSE needs to be entered as an array formula across the correctly sized output range (Ctrl+Shift+Enter after selecting it), otherwise it only returns the top-left value.
Does TRANSPOSE update automatically if I change the source data?
Yes, since it's a live formula, not a one-time copy. Use Paste Special > Transpose instead if you want a static, disconnected copy.
What output range size do I need for TRANSPOSE?
The output needs to have swapped dimensions from the source - a 3-row by 5-column source needs a 5-row by 3-column output selection in older Excel versions.
Need to translate a formula using TRANSPOSE?
Use our translator to convert your complete formula