What it does
The transpose option in Paste Special also flips a table, but the result is fixed values. TRANSPOSE is a formula: a range of 3 rows × 5 columns comes back as 5 rows × 3 columns, and every change in the source is reflected in the flipped table.
In Microsoft 365 and Excel 2021 you type the formula into a single cell and the result spills right and down. If a cell in the spill area is not empty, you get #SPILL!. In Excel 2019 and earlier, select an area the size of the result first, type the formula and enter it as an array formula with Ctrl+Shift+Enter.
To take only some columns of a table use CHOOSECOLS; to look up in a table where months run across, use HLOOKUP or XLOOKUP.
Syntax
=TRANSPOSE(array)=DEVRİK_DÖNÜŞÜM(dizi)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range or array to flip, e.g. A1:C5. Include the header row if you want the headers flipped too. |
Examples
1Turn a vertical list into one row
The branches are listed down column A. The single formula in C2 spills the list to the right; D2:F2 are left empty in the data.
=TRANSPOSE(A2:A5)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Branch | Branches (across) | ||||
| 2 | Camden | Camden | Leeds | Bristol | Oxford | |
| 3 | Leeds | |||||
| 4 | Bristol | |||||
| 5 | Oxford |
- C2 (the result spills into C2:F2)
=TRANSPOSE(A2:A5)
The four-row list spills across four columns from C2 to F2; F2 shows Oxford. Change a branch name in column A and the row changes too.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Flip a table together with its headers
The months run down column A and the line items are in the header. Because the header row is part of the range, A1:C5 (5 rows × 3 columns) spills from E2 as 3 rows × 5 columns.
=TRANSPOSE(A1:C5)| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | Costs | Flipped Table | |||||
| 2 | Jan | 182,000 | 121,000 | Month | Jan | Feb | Mar | Apr | |
| 3 | Feb | 167,500 | 118,400 | Sales | 182,000 | 167,500 | 201,300 | 194,800 | |
| 4 | Mar | 201,300 | 133,900 | Costs | 121,000 | 118,400 | 133,900 | 127,250 | |
| 5 | Apr | 194,800 | 127,250 |
- E2 (the result spills into E2:I4)
=TRANSPOSE(A1:C5)
The headers line up in column E and the months along row 2: E3 shows Sales and F2 shows Jan. The March sales end up in H3: 201,300.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #SPILL!
Cause: A cell in the area the result spills into is not empty.
Fix: Clear the cells to the right of and below the formula; Excel outlines the spill area with a dashed border.
- #VALUE!
Cause: In Excel 2019 and earlier, the formula was entered without Ctrl+Shift+Enter.
Fix: Select an area the size of the result and enter the formula with Ctrl+Shift+Enter.
- Zeros in empty cells
Cause: Empty cells in the source show up as 0 in the flipped table.
Fix: To keep them empty, use
=TRANSPOSE(IF(A1:C5="","",A1:C5)).
Good to know
- To refer to the whole spilled result in another formula, add # after the first cell's address:
=SUM(C2#). - You cannot edit a single cell inside a spilled result; make the change in the source table.
In Nexell
Get Nexell to do it from a sentence
Make a summary table from my sales list with branches down the side and months across the top
Convert to tableBring your sales list into the tool and type this. Nexell builds the summary with real Excel formulas and shows it in the preview; in the downloaded file, the summary updates when an amount in the list changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between TRANSPOSE and transposing with Paste Special?
Paste Special copies the values once; the flipped table does not change when the source does. TRANSPOSE is a formula and stays linked to the source. The trade-off is that the cells of a spilled result cannot be edited one by one.