# How to transpose data with a formula in Excel

Use `=TRANSPOSE(A1:D4)`: rows become columns, and the result updates when the source changes.

**Excel:** `=TRANSPOSE(A1:D4)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Metric | Jan | Feb | Mar |
| Visitors | 18240 | 19870 | 22410 |
| Sign-ups | 412 | 455 | 538 |
| Orders | 96 | 104 | 131 |

Formula in A7: `=TRANSPOSE(A1:D4)`

Result (spills 4 × 4): Metric, Visitors, Sign-ups, Orders; Jan, 18240, 412, 96; Feb, 19870, 455, 104; Mar, 22410, 538, 131

## How it works

TRANSPOSE swaps rows and columns: the first row of the source becomes the first column of the result. Unlike Paste Special > Transpose, it's a live copy, so changing a number in the source table changes it in the flipped one.

## Other ways

- `=INDEX(A1:D4,SEQUENCE(1,ROWS(A1:D4)),SEQUENCE(COLUMNS(A1:D4)))`: INDEX with the row numbers across and the column numbers down does the same flip. Useful to see how the swap works.

## Common mistakes

- In Excel 2019 and earlier, select the whole target area first and confirm TRANSPOSE with Ctrl+Shift+Enter. In Excel 2021 and later it spills.
- Number formats don't come along. Format the result cells yourself.
- Empty source cells show as 0 in the result. Use `=TRANSPOSE(IF(A1:D4="","",A1:D4))` to keep them blank.

## Questions

**How do I transpose without a formula?**

Copy the range, then Paste Special > Transpose. The result is a static copy that doesn't update.

**How do I transpose and keep only some rows?**

Filter first, then transpose: `=TRANSPOSE(FILTER(A1:D4,A1:A4<>"Orders"))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/transpose-data-with-a-formula