Flipping data with TRANSPOSE
The months run down column A, from May. Turn the table on its side so they run across, starting in D1.
keyboard Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | |||||||||
| 2 | May | 750 | |||||||||
| 3 | Jun | 3525 | |||||||||
| 4 | Jul | 5925 | |||||||||
| 5 | Aug | 5850 | |||||||||
| 6 | Sep | 2225 | |||||||||
| 7 | Oct | 3975 | |||||||||
| 8 | |||||||||||
| 9 | |||||||||||
| 10 | |||||||||||
| 11 | |||||||||||
| 12 |
How TRANSPOSE works
What does
TRANSPOSEdo?It turns a table on its side: rows become columns and columns become rows. A list running down column A comes out running across row 1.
=TRANSPOSE(A1:B7)What does "spill" mean?
TRANSPOSEreturns a whole range, not one value. You type it in one cell and Excel fills the cells beside and below by itself. This is called spilling, and the spilled cells show a thin blue border in Excel. Change the original table and the flipped copy updates.What is #SPILL!?
The cells the result needs must be empty. If anything is in the way, even a space, the formula shows #SPILL! until you clear it.
How is it different from Paste Special?
Copy, then Paste Special with Transpose ticked, makes a one-off copy that never updates.
TRANSPOSEstays linked to the original. In Excel before 365 it had to be entered over the whole range with Ctrl+Shift+Enter.Can I transpose without a formula on this site?
Yes: the Table Workspace has a Transpose step for whole files.
Know someone fighting with a spreadsheet? Send them this page.