TRANSPOSE in Excel: Flip Rows and Columns
By ExcelReady TeamUpdated 6 min read
The short answer
To transpose in Excel, copy the range, right-click the destination and choose Paste Special > Transpose. That makes a static copy with rows and columns swapped. For a copy that updates when the original changes, type =TRANSPOSE(A1:E2) in one cell; in Excel 2021 and Microsoft 365 it spills automatically.
Key takeaways
- Paste Special > Transpose makes a one-off, unlinked copy with rows and columns swapped.
- =TRANSPOSE(range) makes a live, linked copy that updates with the source.
- In Excel 2021, 2024 and Microsoft 365, TRANSPOSE spills from one cell; older versions need Ctrl+Shift+Enter.
- TRANSPOSE shows empty source cells as 0; Paste Special keeps them empty.
- The destination must not overlap the original range.
On this page+
To transpose in Excel means to flip a range so its rows become columns and its columns become rows. You can do it with Paste Special > Transpose, which makes a fixed copy, or with the TRANSPOSE function, which makes a live copy that updates with the source. This guide is for anyone reshaping a report, a pasted table or a list that arrived in the wrong direction.
What does transpose mean in Excel?#
Transposing swaps the orientation of a range. A row of four quarters becomes a column of four quarters, and a 2 × 5 block becomes a 5 × 2 block. The top-left cell stays at the top-left.
Before:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quarter | Q1 | Q2 | Q3 | Q4 |
| 2 | Sales | 48,200 | 51,750 | 46,900 | 60,300 |
After:
| G | H | |
|---|---|---|
| 1 | Quarter | Sales |
| 2 | Q1 | 48,200 |
| 3 | Q2 | 51,750 |
| 4 | Q3 | 46,900 |
| 5 | Q4 | 60,300 |
Which method to use depends on one question: should the flipped copy follow the original when it changes?
| Paste Special > Transpose | TRANSPOSE function | |
|---|---|---|
| Linked to the source | No, a fixed copy | Yes, updates automatically |
| Keeps formatting | Yes | No, values only |
| Empty cells | Stay empty | Show as 0 |
| Edit single cells | Yes | No, it's one formula |
| Works in | Every version | Every version (spills in 2021+) |
How to transpose with Paste Special#
Paste Special is the quickest way to flip data once.
- Select the range, here A1:E2.
- Copy it: Ctrl+C on Windows or ⌘C on a Mac. (Cut won't work: Transpose is only available after Copy.)
- Click the top-left cell of the destination, such as G1. Make sure there is room and that it doesn't overlap the original.
- Right-click and choose the Transpose icon under Paste Options, or go to Home > Paste > Transpose.
With the keyboard on Windows: after copying, press Ctrl+Alt+V to open the Paste Special dialog, press E to tick Transpose, then Enter. On a Mac, press ⌃⌘V, tick Transpose and click OK.
The result is a normal range with the original formatting. It won't change if the source changes. The copy, cut and paste lesson covers the basic shortcuts if they are new.
Watch out for formulas#
If the copied cells contain formulas, Paste Special transposes them too, and their relative references shift to match the new positions. That often breaks them or returns #REF!. To be safe, paste the source as values first, or use the TRANSPOSE function. Our guide on copying a formula without changing its references explains why references move.
How to use the TRANSPOSE function#
TRANSPOSE takes one argument, the range to flip:
=TRANSPOSE(array)In Excel 2021, Excel 2024 and Microsoft 365, click G1, type the formula and press Enter:
=TRANSPOSE(A1:E2)The result spills into G1:H5. Change Q4 sales in E2 and H5 updates straight away. This is the method to use when the source is updated often, such as a monthly import. Only G1 holds the formula; the other cells show it greyed out in the formula bar. To change the result, edit G1, and to remove it, delete G1. Spilling is explained in dynamic arrays explained simply.
TRANSPOSE in Excel 2019 and older#
Older versions don't spill, so TRANSPOSE must be entered as an array formula:
- Select a destination of the flipped size first: 5 rows × 2 columns, such as G1:H5.
- Type
=TRANSPOSE(A1:E2). - Press Ctrl+Shift+Enter, not just Enter.
Excel shows the formula with braces, {=TRANSPOSE(A1:E2)}. If you select too few cells, some data is cut off; too many, and the extra cells show #N/A.
How to fix zeros from empty cells#
TRANSPOSE returns 0 for a blank source cell, because a formula can't return "nothing". In Microsoft 365, Excel 2021 and Excel 2024, swap blanks for empty text first:
=TRANSPOSE(IF(A1:E2="","",A1:E2))IF checks every cell: blank cells become "", and the rest keep their values. Alternatively, hide zeros with a custom number format such as 0;-0;;@.
Combining TRANSPOSE with other functions#
TRANSPOSE is most useful as a step inside a bigger formula:
| Task | Formula |
|---|---|
| A sorted list across a row | =TRANSPOSE(SORT(A2:A9)) |
| Unique values across a row | =TRANSPOSE(UNIQUE(B2:B20)) |
| Numbers filled down first, then across | =TRANSPOSE(SEQUENCE(3,2)) |
| Top three names as a header row | =TRANSPOSE(INDEX(SORTBY(A2:A9,C2:C9,-1),SEQUENCE(3))) |
These need Excel 2021, Excel 2024 or Microsoft 365. The guide to SORT and SORTBY covers the sorting options, and our top 5 list with SORT and FILTER shows how to build a ranked list you can flip into a header.
Transpose vs fill right#
Transposing changes the shape of data you already have. If you only need to extend a formula across a row, fill it instead: select the cells and press Ctrl+R on Windows or ⌘R on a Mac. See fill down and fill right for when each shortcut applies.
Common transpose problems#
These are the issues you're most likely to hit:
- A warning that the copy and paste areas can't overlap. The flipped range would land on top of the original. Paste to a spot well away from it.
- Transpose is greyed out. You used Cut. Copy instead.
- #SPILL! Cells in the TRANSPOSE result area aren't empty, or the formula is inside an Excel table. Clear them or move the formula.
- #VALUE! in old Excel. You pressed Enter instead of Ctrl+Shift+Enter, or selected the wrong size.
- Changes don't flow through. You used Paste Special. Use the function for a linked copy.
Practise it#
Try the TRANSPOSE exercise above. Then open the Modern Excel lesson to practise spills and the # spill reference that dynamic formulas like TRANSPOSE rely on, and the copy, cut and paste lesson for the shortcuts behind Paste Special.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The quarterly figures run across a row, but your report needs them down a column. In G1, use TRANSPOSE on A1:E2 so the labels and values spill down two columns.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Q4 | |||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 |
Tip: TRANSPOSE needs one thing: the whole range to flip, A1:E2.
Practise this in a lesson
Frequently asked questions
What is the shortcut to transpose in Excel?
On Windows, copy with Ctrl+C, select the destination, press Ctrl+Alt+V to open Paste Special, press E to tick Transpose, then Enter. On a Mac, copy with ⌘C, press ⌃⌘V, tick Transpose and click OK.
Does transposed data update when the original changes?
Not with Paste Special, which pastes a fixed copy. Use the TRANSPOSE function instead: =TRANSPOSE(A1:E2) stays linked to the source and updates automatically.
Why does TRANSPOSE show 0 in some cells?
TRANSPOSE returns 0 for empty source cells. In Microsoft 365 you can keep them blank with =TRANSPOSE(IF(A1:E2="","",A1:E2)).
Why can't I edit one cell of a TRANSPOSE result?
The result is one spilled formula, so only the first cell holds it. Edit the formula in that cell, or copy the result and paste it as values if you need to change single cells.
Can I transpose an Excel table?
Yes, with Paste Special, but the result is a normal range, not a table. Spilled formulas such as TRANSPOSE can't be placed inside a table.