The TRANSPOSE function
Turns rows into columns and columns into rows. The result spills and stays linked to the original cells.
Syntax
TRANSPOSE(array)- array
- The range to flip.
Examples
F1=TRANSPOSE(A1:D2)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Month | Sales | |
| 2 | Sales | 120 | 95 | 140 | Jan | 120 | |
| 3 | Feb | 95 | |||||
| 4 | Mar | 140 |
C1=TRANSPOSE(A1:A3)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ann | Ann | Ben | Cy | |
| 2 | Ben | ||||
| 3 | Cy |
Questions
What is the difference between TRANSPOSE and Paste Special > Transpose?
Paste Special flips a copy once, and later changes to the source do not follow. The TRANSPOSE function stays linked, so the flipped table updates when the original does.
How do I use TRANSPOSE in older versions of Excel?
Select the target range first, with the rows and columns swapped, type =TRANSPOSE(A1:D2) and press Ctrl+Shift+Enter. Excel 365 and Google Sheets spill the result by themselves.