How to transpose data with a formula in Excel

Q: How do I transpose and keep only some rows?

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

Formula (Excel and Google Sheets)

=TRANSPOSE(A1:D4)

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

Result on the example: a 4-row list that spills from A7 in A7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in A7. Change any input and the result updates, or edit the formula to experiment.

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.

=TRANSPOSE(A1:D4)

Result{4 rows × 4 cols} "Metric", "Visitors", "Sign-ups", "Orders", …

A1:D44 rows × 4 cols: "Metric", "Jan", "Feb", "Mar", …
A range: 4 rows × 4 columns.
TRANSPOSE(A1:D4){4 rows × 4 cols} "Metric", "Visitors", "Sign-ups", "Orders", …
TRANSPOSE: Flips rows and columns.
- arrayA1:D44 rows × 4 cols: "Metric", "Jan", "Feb", "Mar", …

In Google Sheets

The same formula works in Google Sheets: =TRANSPOSE(A1:D4). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=INDEX(A1:D4,SEQUENCE(1,ROWS(A1:D4)),SEQUENCE(COLUMNS(A1:D4)))	spills 4 × 4	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Transpose data with a formula

In A7, flip the table A1:D4 so the months run down the rows and the metrics across the columns.

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")).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.