Home › Excel functions › TRANSPOSE
Transpose in Excel: Switch Rows and Columns
To transpose data in Excel means to turn rows into columns and columns into rows. There are two ways: Paste Special › Transpose for a one-time copy, and the TRANSPOSE function for a flipped version that updates when the original changes.
TRANSPOSE · In this guide
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,800 | 5,100 |
Transposed, this becomes:
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 4,200 |
| 3 | Feb | 3,800 |
| 4 | Mar | 5,100 |
Method 1: Paste Special › Transpose
- Select the data and press Ctrl+C.
- Click the cell where the result should start, outside the original range.
- Right-click and choose the Transpose paste option, or go to Home › Paste › Transpose. With the keyboard: Ctrl+Alt+V, tick Transpose, Enter.
The copy is static: change a number in the original and the transposed version stays the same. Formulas are adjusted to the new layout, which can break references; paste as values first if that happens. More options in Paste Special.
Method 2: the TRANSPOSE function
=TRANSPOSE(array)In Excel 365 and 2021, type it in one cell:
=TRANSPOSE(A1:D2)The result spills into 4 rows and 2 columns automatically. It stays linked: change a number in the original and the transposed copy follows.
In Excel 2019 and older, first select an empty range of the right size (here 4 rows × 2 columns), type the formula and confirm with Ctrl+Shift+Enter.
Formatting isn't copied. TRANSPOSE returns values only, so set number formats and borders yourself. Empty cells come back as 0; use =TRANSPOSE(IF(A1:D2="", "", A1:D2)) to keep them empty.
Method 3: Power Query
For data you import or refresh regularly, select the data, choose Data › From Table/Range, then Transform › Transpose and Close & Load. Next time, Refresh repeats the steps.
Which method should I use?
| You want | Use |
|---|---|
| A quick, one-off flip with formatting | Paste Special › Transpose |
| A flipped version that stays up to date | TRANSPOSE function |
| A repeatable step for imported data | Power Query |
Common problems
- #SPILL! Something is in the way of the result. Clear the cells where the transposed data needs to go.
- "Copy area and paste area aren't the same size". You selected a range before pasting. Select just one cell.
- Transpose is greyed out. You used Cut (Ctrl+X) instead of Copy. Transpose only works after Ctrl+C.
- Can't change part of a TRANSPOSE result. A spilled range is one formula. Delete it in the first cell, or paste the result as values.
Frequently asked questions
Is there a shortcut to transpose in Excel?
After Ctrl+C, press Ctrl+Alt+V, then E (Windows) to tick Transpose, and Enter.
Can I transpose a table (Ctrl+T)?
Not with Paste Special directly. Convert it to a range first (Table Design › Convert to Range), or use the TRANSPOSE function.
How do I turn one long column into several columns?
In Excel 365, =WRAPROWS(A1:A12, 3) turns 12 values into rows of 3, and WRAPCOLS fills columns instead.
Does TRANSPOSE work in Google Sheets?
Yes, with the same syntax, and the result spills automatically.