TRANSPOSE Function in Excel
TRANSPOSE flips rows into columns and columns into rows. A 3-row × 5-column range becomes 5-row × 3-column, with values swapped diagonally. Essential when data comes in the wrong orientation for your report, chart, or formula pipeline.
What it does: Returns array with rows and columns swapped. If input is R rows × C columns, output is C rows × R columns. Values at [row, col] become values at [col, row]. In modern Excel, results spill automatically. In older Excel, must be entered as an array formula.
What TRANSPOSE does
Data comes in two orientations: tall (many rows, few columns) or wide (few rows, many columns). Sometimes you have one but need the other. TRANSPOSE is the formula that does the rotation.
=TRANSPOSE(A1:E3) takes a 3-row × 5-column range and returns a 5-row × 3-column spilled array with the same values in swapped positions. Unlike Paste Special → Transpose (which produces a static copy), TRANSPOSE is a live formula — the output updates when the source changes.
Syntax breakdown
array Required
The range or array to transpose. Can be any rectangular range — a single row, a single column, or a 2D block. Named ranges and Table structured references work.
5 real-world examples
Example 1: Rotate a row into a column
Cells A1:E1 contain "Q1", "Q2", "Q3", "Q4", "Q5". Get them as a column:
=TRANSPOSE(A1:E1)Result: Five cells stacked vertically containing Q1, Q2, Q3, Q4, Q5. Spills automatically in Excel 365; enter as array formula (Ctrl+Shift+Enter) in older versions.
Example 2: Rotate a column into a row
Column A2:A10 contains months. Turn them into a horizontal header row:
=TRANSPOSE(A2:A10)Result: Nine cells side by side with the month names. Perfect for building a pivot-style header from a column-format list.
Example 3: Rotate a 2D block
Range B2:F11 has products as rows and months as columns. Flip so months are rows and products are columns:
=TRANSPOSE(B2:F11)Result: A 5-row × 10-column spilled array. Rows become columns and vice versa. Values follow their diagonal partners — B2 goes to the transposed position, C2 goes below it, and so on.
Example 4: Live transpose that updates with the source
Reference a Table column vertically, display it as a row in a dashboard:
=TRANSPOSE(Sales[Amount])Result: Amounts arranged horizontally. Add a row to the Sales Table and the horizontal display grows automatically. Impossible with Paste Special.
Example 5: TRANSPOSE inside another formula
Sum a horizontal range using a vertical reference (or vice versa):
=SUMPRODUCT(A2:A10, TRANSPOSE(B1:J1))Result: Element-wise product then sum, where A2:A10 is vertical and B1:J1 is horizontal — TRANSPOSE aligns their orientations so SUMPRODUCT can process them together.
Common errors and how to fix them
In older Excel (pre-365), TRANSPOSE must be entered as an array formula with Ctrl+Shift+Enter. Simply typing and pressing Enter gives #VALUE!. In Excel 365 and 2021+, the spill happens automatically — no CSE needed.
The transposed result can't fit — cells in the destination range are occupied. Clear those cells or move the formula to an empty area. See the #SPILL! error guide.
TRANSPOSE only moves values, not cell formatting (colors, borders, fonts). For formatting preservation, use Paste Special → Transpose which copies formatting along with values.
In pre-365 Excel, you had to pre-select the destination range with the correct size (columns become rows, rows become columns), then enter as an array formula. If you pre-selected too few cells, results were truncated. Modern Excel auto-sizes.
TRANSPOSE vs Paste Special
| Approach | Best for | Trade-off |
|---|---|---|
| TRANSPOSE function | Live updates when source changes | Values only, no formatting; formula overhead |
| Paste Special → Transpose | One-time static conversion | Doesn't update with source changes |
| Power Query Unpivot | Complex reshaping beyond simple rotation | Not real-time; batch refresh |
| WRAPROWS / WRAPCOLS | Reshape 1D array into 2D grid | Excel 365 only; different use case |
Version compatibility
In Excel 2019 and 2016, enter as array formula (Ctrl+Shift+Enter). In Excel 2021+ and 365, TRANSPOSE spills automatically like any dynamic array function.
Download the practice workbook
TRANSPOSE examples with both static and dynamic sources, plus side-by-side comparison with Paste Special → Transpose.
Related functions
Frequently asked questions
What's the difference between TRANSPOSE the function and Paste Special → Transpose?
The function is live — the result updates when the source changes. Paste Special is a one-time static copy. Use Paste Special when you're doing a one-off restructure of data that won't change. Use TRANSPOSE when the source is dynamic and you want the transposed view to stay in sync.
Does TRANSPOSE preserve formatting?
No — TRANSPOSE moves only values. Colors, borders, and fonts don't transfer. If you need formatting preservation, use Paste Special → Transpose, which copies formatting alongside the values (but produces a static result).
Why does TRANSPOSE only return one value in my old Excel?
Because you didn't enter it as an array formula. In Excel 2016 and 2019, you must (1) pre-select the destination range with the swapped dimensions, (2) type the TRANSPOSE formula, (3) press Ctrl+Shift+Enter. Excel 2021+ and 365 handle this automatically via spill.
Can TRANSPOSE handle very large arrays?
Yes, but performance degrades on huge ranges (100,000+ cells). Each cell in the output has to reference back through the transposition, which is more expensive than a direct reference. For very large transpositions, Paste Special or Power Query are usually faster.
How do I transpose only certain rows or columns?
Wrap in FILTER first: =TRANSPOSE(FILTER(data, criterion)). This transposes only the filtered rows. Or use CHOOSECOLS / CHOOSEROWS in Excel 365 to pre-select before transposing.
Does TRANSPOSE work with Table structured references?
Yes: =TRANSPOSE(Sales[Amount]) returns the Amount column as a row that grows/shrinks with the Table. Perfect for building horizontal dashboards from vertical Table data.
Is TRANSPOSE volatile?
No. TRANSPOSE is non-volatile — recalculates only when the input array changes. Safe to use in performance-sensitive workbooks unlike OFFSET or INDIRECT.
Reshape data instantly — with the Sheets & Cells AI Add-in
Describe how you want data rotated or reshaped — "flip this column to a row" or "transpose but only for active customers" — and the Add-in writes the TRANSPOSE (plus FILTER if needed) formula. All inside Excel.
Learn about the Add-in →