To switch rows and columns in Excel, type =TRANSPOSE(A1:D3) where the result should start: the first row of A1:D3 becomes the first column of the result. The result stays linked, so changing a number in the source changes it in the copy too. Change B2 to 50 and watch B6 follow.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Q1 | Q2 | Q3 |
| 2 | Ann | 10 | 12 | 9 |
| 3 | Ben | 8 | 11 | 14 |
| 4 | ||||
| 5 | Rep | Ann | Ben | |
| 6 | Q1 | 10 | 8 | |
| 7 | Q2 | 12 | 11 | |
| 8 | Q3 | 9 | 14 |
The source has 3 rows and 4 columns, so the result has 4 rows and 3 columns. In Excel 2021 and Microsoft 365, TRANSPOSE spills: you type it in one cell and press Enter.
How to transpose with Paste Special
When you only need the swapped table once, Paste Special is quicker and leaves no formula behind:
- Select the range and copy it (Ctrl+C, Cmd+C on a Mac).
- Click the top-left cell of where the result should go, outside the copied range.
- On the Home tab click the arrow under Paste and choose Transpose, or press Ctrl+Alt+V (Cmd+Ctrl+V on a Mac), tick Transpose and click OK.
On Windows the keyboard path is Alt, H, V, T after copying. The pasted copy keeps formatting but is not linked: when the source changes, paste again. Excel adjusts formulas inside the copied range to their new positions; check any that use relative references, or paste as values if you only need the numbers.
TRANSPOSE syntax
=TRANSPOSE(array)
The only argument is the range or array to flip. TRANSPOSE has been in Excel for a long time, but before Excel 2021 it is an array formula: select a destination of the right shape first (4 rows by 3 columns for the example above), type the formula and press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac). Excel then shows it in braces, {=TRANSPOSE(A1:D3)}. In Excel 2021 and Microsoft 365 plain Enter is enough, and a value in the way gives #SPILL!.
Google Sheets has TRANSPOSE too and spills it the same way.
Turn a column into a row
The same function flips a single column into a row, for example a list of names into column headers:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | ||||||
| 2 | Ann | Ann | Ben | Cara | Dan | Eve | |
| 3 | Ben | ||||||
| 4 | Cara | ||||||
| 5 | Dan | ||||||
| 6 | Eve |
C2 spills Ann to Eve across C2:G2. Add a name in A7 and it is not included, because the formula reads A2:A6; extend the range to A2:A7 or larger to include it.
Why TRANSPOSE shows 0 for blank cells
A formula pointing at an empty cell returns 0, and TRANSPOSE does too. March has no sales below, so the plain version shows 0 where the source is empty:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 120 | 95 | 150 | |
| 3 | |||||
| 4 | Month | Sales | Month | Sales | |
| 5 | Jan | 120 | Jan | 120 | |
| 6 | Feb | 95 | Feb | 95 | |
| 7 | Mar | 0 | Mar | ||
| 8 | Apr | 150 | Apr | 150 |
B7 shows 0 for Mar. The second formula first turns each empty cell into empty text with IF, so E7 stays blank. Type a number in D2 and both formulas show it.
Transpose several rows into one column
To stack a whole grid into a single column, use TOCOL (Microsoft 365 and Excel 2024). It reads the range row by row; TOROW does the same into one row:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Q1 | Q2 | Q3 | All values | |
| 2 | Ann | 10 | 12 | 9 | 10 | |
| 3 | Ben | 8 | 11 | 14 | 12 | |
| 4 | 9 | |||||
| 5 | 8 | |||||
| 6 | 11 | |||||
| 7 | 14 |
F2 lists 10, 12, 9 from Ann's row and then 8, 11, 14 from Ben's. To read down each column instead, set the third argument to TRUE: =TOCOL(B2:D3,,TRUE). =TOCOL(B2:D3,1) skips empty cells.
Practice: months down instead of across
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 120 | 95 | 80 | 150 |
| 3 | |||||
| 4 | Months down |
Your turn: In A5, turn the table in A1:E2 around so the months run down column A and the sales down column B.
TRANSPOSE or a lookup across the row
People often transpose a wide table only to run VLOOKUP on it. That is not necessary: HLOOKUP and XLOOKUP search a row directly. =XLOOKUP("Mar",B1:E1,B2:E2) returns March sales from the wide table above without flipping anything. Transpose when the layout itself has to change, for a chart, a report or a system that expects one record per row.
Frequently Asked Questions
How do I transpose data in Excel?
Copy the range, right-click the cell where the result should start and choose Paste Special > Transpose (Ctrl+Alt+V, then E and Enter). That pastes a fixed copy. For a copy that updates with the source, type =TRANSPOSE(A1:D3) in the destination cell.
Why does TRANSPOSE show 0 for empty cells?
A formula that refers to an empty cell returns 0, and TRANSPOSE is no exception. Replace empty cells with empty text first: =TRANSPOSE(IF(A1:E2="","",A1:E2)).
How do I use TRANSPOSE in Excel 2019 or older?
Select a destination range with the rows and columns swapped (4 rows by 3 columns for a 3 by 4 source), type =TRANSPOSE(A1:D3) and press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac). Excel 2021 and later need only Enter.
How do I turn several rows into one column?
Use TOCOL in Microsoft 365 or Excel 2024: =TOCOL(B2:D3) reads the range row by row and stacks every value in one column. =TOCOL(B2:D3,1) skips empty cells, and TOROW does the same into a single row.
