TRANSPOSE flips a range on its side — rows become columns and columns become rows — with a live formula that updates as the source changes. It’s the dynamic alternative to Paste Special → Transpose.
The example
A wide 2×3 table flipped to a tall 3×2.
| A | B | |
|---|---|---|
| 1 | Transposed → | |
| 2 | Jan | 100 |
| 3 | Feb | 140 |
| 4 | Mar | 120 |
The formula
Flip the source range:
How it works
TRANSPOSE swaps the two dimensions:
- It reads the source range and outputs it rotated — what was a row becomes a column and vice versa.
- In Excel 365, enter it in one cell and the result spills into the flipped shape.
- In older Excel, first select a block of the right size (3×2 here), type the formula, and press Ctrl+Shift+Enter to array-enter it.
- Because it’s a formula, editing the source updates the transposed copy live — unlike a one-time paste.
One-time flip? Copy the range, right-click the destination, and choose Paste Special → Transpose — it pastes values rotated, no formula. Use TRANSPOSE when you want the flip to stay linked to the source.
Try it: interactive demo
A wide table, transposed to tall.
| Jan | Feb | Mar |
| 100 | 140 | 120 |
| Jan | 100 |
| Feb | 140 |
| Mar | 120 |
Variations
Transpose a single row to a column
Works on 1-D ranges too:
Transpose a spill result
Flip the output of another dynamic-array formula:
Paste Special (one-time)
Copy → Paste Special → Transpose for a static flip.
Pitfalls & errors
#SPILL! (365) or partial result (older). The destination must be clear and, in pre-365, exactly the right size before array-entering.
Pre-365 needs Ctrl+Shift+Enter. Entering TRANSPOSE as a normal formula in older Excel returns only the first value.
It’s a live link. The transposed range mirrors the source; you can’t edit the flipped cells directly. Use Paste Special for an independent copy.
Practice workbook
Frequently asked questions
How do I switch rows and columns in Excel with a formula?
What's the difference between TRANSPOSE and Paste Special Transpose?
Why does TRANSPOSE only show one value in older Excel?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class