The Excel TRANSPOSE function flips a range on its diagonal: rows become columns and columns become rows. Unlike copy → Paste Special → Transpose, the result is live — edit the source and the flipped copy updates instantly. In Excel 365 it spills automatically; in older versions it’s the classic Ctrl+Shift+Enter array formula.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to flip. An r rows × c columns input returns a c rows × r columns output. |
One function, two eras. Excel 365 / 2021: type it in one cell and the result spills. Excel 2019 and older: select the whole destination range (with the flipped dimensions), type the formula, and confirm with Ctrl+Shift+Enter.
Flip a horizontal table vertical
The 2-row × 5-column table below runs sideways. One formula in A4 turns it into a 5-row × 2-column table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | $8,200 | $7,900 | $8,600 | $9,100 |
| A | B | |
|---|---|---|
| 4 | Month | Sales |
| 5 | Jan | $8,200 |
| 6 | Feb | $7,900 |
| 7 | Mar | $8,600 |
| 8 | Apr | $9,100 |
The link is live: change Feb’s sales in the original and the transposed copy updates immediately. That’s the difference from Paste Special, which takes a one-time snapshot.
Try it: interactive TRANSPOSE demo
Click the button and watch the table flip on its diagonal — rows become columns, columns become rows.
TRANSPOSE vs Paste Special → Transpose
Excel gives you two ways to flip data — pick by whether the source will change:
| Behavior | =TRANSPOSE() | Paste Special → Transpose |
|---|---|---|
| Updates when the source changes | Yes — live link | No — one-time snapshot |
| Keeps formatting | No — values only | Yes |
| Result is editable cell by cell | No — it’s one formula | Yes |
| Source can be deleted afterwards | No | Yes |
TRANSPOSE also shines inside other formulas, reorienting data on the fly so two differently-shaped ranges can meet — the classic example multiplies a column by a row in one SUMPRODUCT:
Just flattening, not flipping? Excel 365’s TOROW and TOCOL turn any range into a single row or column — often what people actually wanted from TRANSPOSE.
Errors & common pitfalls
#SPILL! — the landing zone isn’t empty. The flipped result needs a clear block with swapped dimensions: a 2×5 source needs 5×2 of empty cells. Clear the blockers or move the formula.
#VALUE! in old Excel — forgot Ctrl+Shift+Enter. In Excel 2019 and older, TRANSPOSE entered with plain Enter (or into a wrongly-sized selection) fails. Select the full destination range first, then confirm with Ctrl+Shift+Enter.
Pitfall: empty cells become 0. TRANSPOSE converts blanks in the source to zeros in the result. Hide them with a wrapper: =IF(TRANSPOSE(A1:E2)="", "", TRANSPOSE(A1:E2)).
Pitfall: formatting doesn’t travel. TRANSPOSE moves values only — currency formats, colors, and borders stay behind. Reapply formats to the destination range.
Pitfall: you can’t edit one cell of the result. The flipped block is a single formula. To get independent, editable cells, use Paste Special → Transpose instead.
Practice workbook
Frequently asked questions
What's the difference between TRANSPOSE and Paste Special Transpose?
How do I use TRANSPOSE in older versions of Excel?
Why do blank cells show as 0 after transposing?
=IF(TRANSPOSE(A1:E2)="", "", TRANSPOSE(A1:E2)).Does TRANSPOSE keep my formatting?
Can I transpose data and keep it linked to the source?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class