TRANSPOSE function
TRANSPOSE flips a range so rows become columns and columns become rows. Unlike Paste Special > Transpose, the flipped copy stays linked to the source and updates when it changes. In Excel 2021 and later it spills; in older versions you select the output area and press Ctrl+Shift+Enter.
- Available in
- Every current version
- Category
- Dynamic array functions
- Returns
- The same values with rows and columns swapped.
- Google Sheets
- Available in Google Sheets with the same argument.
Syntax
=TRANSPOSE(array)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or array to flip. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| A6 | =TRANSPOSE(A1:D3) | Regionspills 4 × 3 | The whole table turned on its side: months now run down the rows |
| E6 | =TRANSPOSE(B1:D1) | Janspills 3 × 1 | A row of month headers becomes a column |
| G6 | =TRANSPOSE(A2:A3) | Northspills 1 × 2 | A column of regions becomes a row |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Region | Jan | Feb | Mar | ||||
| North | 120 | 135 | 128 | ||||
| South | 95 | 110 | 102 | ||||
| Flipped table | Months down a column | Regions across a row | |||||
| Region | North | South | Jan | North | South | ||
| Jan | 120 | 95 | Feb | ||||
| Feb | 135 | 110 | Mar | ||||
| Mar | 128 | 102 |
Practice TRANSPOSE
Graded instantly, then re-checked on changed data.
Quarters down the side
A5, flip the table A1:E3 so the quarters run down column A and the regions run across.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #SPILL! | The flipped result needs as many rows as the source has columns. Clear that space below and to the right. |
| Only one value shows | You're on Excel 2019 or earlier. Select the whole output area first, type the formula, then press Ctrl+Shift+Enter. |
| Zeros where cells were blank | TRANSPOSE turns empty cells into 0. Use =TRANSPOSE(IF(A1:D3="","",A1:D3)) to keep them blank. |
Tips
- For a one-off flip with no link back to the source, copy the range and use Paste Special > Transpose instead.
Questions
How do I switch rows and columns in Excel?
Copy the range and use Paste Special with Transpose checked, or use =TRANSPOSE(A1:D3) to get a flipped copy that updates with the source.
Why does TRANSPOSE return only one value?
In Excel 2019 and earlier it has to be entered as an array formula: select the full output range, type it, and press Ctrl+Shift+Enter.
Can I edit the transposed cells?
No. They are results of one formula. Change the source cells, or copy and paste the results as values if you need an editable copy.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.