How to Transpose Data in Excel (Switch Rows and Columns)
You've built a table with months going down the side, and now you need them across the top. Or someone's sent data in a long row that you need as a column. Retyping it isn't necessary. Excel can flip it for you. This is called transposing.
Method 1: Paste Transposed (quick and simple)
1. Select the data you want to flip, including headings.
2. Press Ctrl + C to copy it.
3. Click an empty cell where you want the flipped version to start. Make sure there's enough space below and to the right.
4. Right-click and, under Paste Options, click the Transpose icon (two small squares with a curved arrow).
2. Press Ctrl + C to copy it.
3. Click an empty cell where you want the flipped version to start. Make sure there's enough space below and to the right.
4. Right-click and, under Paste Options, click the Transpose icon (two small squares with a curved arrow).
Rows become columns and columns become rows. Formatting comes too.
Keyboard version
After copying, press Ctrl + Alt + V to open Paste Special, press E to tick Transpose, then press Enter.
On a Mac, use Control + Command + V, then tick Transpose.
Once you're happy with the flipped copy, you can delete the original.
Method 2: The TRANSPOSE function (stays linked)
Paste Transposed creates a one-off copy. If the original changes, the copy doesn't. To keep them linked:
1. Click an empty cell.
2. Type =TRANSPOSE(A1:D5), using your own range.
3. Press Enter.
2. Type =TRANSPOSE(A1:D5), using your own range.
3. Press Enter.
In Microsoft 365 and Excel 2021, the results spill into the cells they need automatically, with a blue border around them. Change a value in the original and the transposed version updates too.
Older versions of Excel
In Excel 2019 and earlier, TRANSPOSE has to be entered as an array formula:
1. Select the exact destination area first. If the original is 5 rows × 4 columns, select 4 rows × 5 columns.
2. Type =TRANSPOSE(A1:D5).
3. Press Ctrl + Shift + Enter (not just Enter).
2. Type =TRANSPOSE(A1:D5).
3. Press Ctrl + Shift + Enter (not just Enter).
Excel adds curly braces { } around the formula to show it's an array formula.
Which method should I use?
| Paste Transposed | TRANSPOSE function |
|---|---|
| Quick, one-off copy | Stays linked to the original |
| Keeps formatting | No formatting copied |
| You can edit any cell | You can't edit individual cells in the result |
| Works everywhere | Easiest in Microsoft 365 / Excel 2021 |
Transposing formulas
Paste Transposed adjusts cell references in formulas as it flips them, which can produce odd results or #REF! errors. If your data contains formulas, it's often easier to Paste Values first, then transpose the values.
Troubleshooting
"The Copy area and paste area aren't the same size" or an overlap error. You tried to paste over the original data. Paste into an empty area of the sheet, or a new sheet.
#SPILL! Something is blocking the TRANSPOSE results, such as text or even a space in one of the cells it needs. Click the warning icon and choose Select Obstructing Cells, then clear them.
The Transpose option is greyed out. You used Cut (Ctrl + X) instead of Copy. Transpose only works with copy.
Blank cells became zeros with TRANSPOSE. Use =TRANSPOSE(IF(A1:D5="", "", A1:D5)) to keep blanks blank.
Wrapping up
For a quick flip, copy and choose Paste > Transpose. If you want the flipped version to update when the original changes, use =TRANSPOSE() instead.
