SheetsdrillTRANSPOSE
TRANSPOSE(range) returns the same cells with rows and columns swapped: a 3-row by 2-column range becomes 2 rows by 3 columns. The result is a live array that updates with the source; to make a one-time copy, use Paste special, Transposed instead.
Syntax
=TRANSPOSE(array_or_range)| Part | What it is |
|---|---|
array_or_range | The range to flip, or an array from another function such as SPLIT or FILTER. |
Examples
=TRANSPOSE(A1:A4)Result: The four values of A1:A4 laid out across one row, in the cell you typed the formula in and the three to its right
A column becomes a row; the formula needs that many empty cells to spill into.
=TRANSPOSE(SPLIT("red,green,blue", ","))Result: red, green and blue stacked in three rows
SPLIT returns a row; TRANSPOSE turns it into a column list.
=TRANSPOSE(FILTER(B2:B20, C2:C20="Yes"))Result: The confirmed names from column B written across a single row
=TRANSPOSE(A1:C3)Result: A 3 by 3 block flipped along its diagonal: what was in B1 is now in A2, and C1 moves to A3
Common mistakes
- #REF! appears when the cells the result needs to spill into are not empty; clear them or move the formula.
- Editing a cell inside the transposed block deletes the whole output, because it belongs to one array formula in the top left cell.
- The transposed values are references, not copies, so they change when the source changes; use Paste special and choose Transposed for a static copy.
- Formatting is not transposed: currency and date formats must be applied again to the new cells.
Practice TRANSPOSE
These drills check your answer on live data, free, no account needed.
- First name with SPLITNames are typed as "Last, First". Return just the first name in B2 and B3 using SPLIT.
- Revenue with ARRAYFORMULATotal revenue is quantity times unit price on every row, added up. Do it in one formula in F1, without a helper column, using ARRAYFORMULA.
Questions
How do I transpose in Google Sheets without a formula?
Copy the range, right-click the destination cell, choose Paste special, then Transposed. The result is plain values that do not update with the source.
Can I transpose a SPLIT result into a column?
Yes: =TRANSPOSE(SPLIT(A2, ",")) writes each piece of A2 on its own row, which is the usual way to turn a comma-separated cell into a list.
Does TRANSPOSE work with ARRAYFORMULA?
TRANSPOSE already returns an array, so it does not need ARRAYFORMULA on its own; wrap the whole thing only when another function inside it needs array evaluation.
Related
- SPLITHow SPLIT in Google Sheets breaks text into separate cells by a comma, space or other delimiter, how to keep only the first or last piece, and common errors.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- SORT and SORTNHow SORT and SORTN in Google Sheets sort data by one or more columns with a formula and return the top or bottom N rows, with examples and common errors.
- ARRAYFORMULAHow ARRAYFORMULA in Google Sheets fills a whole column from one cell, with examples for multiplying columns, IF across a range, and the common errors.