How to Use the TRANSPOSE Function in Google Sheets
=TRANSPOSE(array_or_range). A range with m rows and n columns becomes n rows by m columns.
What TRANSPOSE does
Sometimes a table is the wrong way around: months run down a column but you need them across a row. TRANSPOSE flips it. Unlike copy and paste special, the result is a live formula, so it updates when the source changes.
TRANSPOSE syntax
=TRANSPOSE(array_or_range)
Per Google's help page, a value in row n, column m moves to row m, column n. So a range with m rows and n columns becomes n rows by m columns.
A step-by-step example
Here is a small table with 2 rows and 3 columns in cells A1:C2.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Q1 | Q2 |
| 2 | Ann | 10 | 20 |
=TRANSPOSE(A1:C2)
This returns a table with 3 rows and 2 columns. The first row of the source (Name, Q1, Q2) becomes the first column, and the second row (Ann, 10, 20) becomes the second column. Type the formula into one cell and the result fills the cells below and to the right, so they must be empty.
Array constants
The help page also uses a typed array, TRANSPOSE({1,2;3,4;5,6}). Commas separate columns and semicolons separate rows in the US locale, so this array has 3 rows and 2 columns. Following the rule above, the result has 2 rows and 3 columns: 1, 3, 5 on the first row and 2, 4, 6 on the second. Your locale may use different separators.
Common mistakes
- Blocked output cells. The result spills into neighboring cells, which must be empty.
- Expecting to edit the result. It is one formula, so edit the source or the formula.
- Mixing up the new shape. Rows and columns exchange sizes.
Try it yourself
Use the table above (A1:C2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Flip a table
Write a formula that swaps the rows and columns of the table in A1:C2.
Exercise 2: Flip an array
Write a formula that transposes the array constant {1,2;3,4;5,6}.
What to learn next
- FLATTEN: turn many columns into one.
- SEQUENCE: generate a list of numbers.
- SORT: sort a range with a formula.
Syntax and rules in this guide were checked against Google's TRANSPOSE help page. See all guides.