# TRANSPOSE function in Excel

> TRANSPOSE flips a range so rows become columns and columns become rows.

Source: https://spreadsheetpractice.com/functions/transpose · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** TRANSPOSE flips a range so rows become columns and columns become rows.
- **Syntax:** `=TRANSPOSE(array)`
- **Most common use:** `=TRANSPOSE(A1:D3)` returns Region (The whole table turned on its side: months now run down the rows)
- **Available in:** Every current version
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** The same values with rows and columns swapped.
- **Google Sheets:** Available in Google Sheets with the same argument.

## What it does

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.

## Syntax

`=TRANSPOSE(array)`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to flip. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| A6 | `=TRANSPOSE(A1:D3)` | Region (spills 4 × 3) | The whole table turned on its side: months now run down the rows |
| E6 | `=TRANSPOSE(B1:D1)` | Jan (spills 3 × 1) | A row of month headers becomes a column |
| G6 | `=TRANSPOSE(A2:A3)` | North (spills 1 × 2) | A column of regions becomes a row |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |  |  |  |  |
| 2 | North | 120 | 135 | 128 |  |  |  |  |
| 3 | South | 95 | 110 | 102 |  |  |  |  |
| 4 |  |  |  |  |  |  |  |  |
| 5 | Flipped table |  |  |  | Months down a column |  | Regions across a row |  |
| 6 | Region | North | South |  | Jan |  | North | South |
| 7 | Jan | 120 | 95 |  | Feb |  |  |  |
| 8 | Feb | 135 | 110 |  | Mar |  |  |  |
| 9 | Mar | 128 | 102 |  |  |  |  |  |

## 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.

## Related functions

- [VSTACK](https://spreadsheetpractice.com/functions/vstack): VSTACK stacks ranges on top of each other into one list, such as this month's orders under last month's or one region's reps under another's.
- [HSTACK](https://spreadsheetpractice.com/functions/hstack): HSTACK places ranges side by side to build one table, so you can pick columns that aren't next to each other, put them in a new order, or add a calculated column, all in one formula that updates with the source.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.

## 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.

## Practice

A graded TRANSPOSE exercise ("Quarters down the side") is on [the TRANSPOSE page](https://spreadsheetpractice.com/functions/transpose). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
