How to Use TRANSPOSE in Google Sheets: Comprehensive Guide

TRANSPOSE flips a range: rows become columns and columns become rows. It is the fix when data arrived in the wrong orientation, or when a function needs a row but you have a column.

Syntax

=TRANSPOSE(array_or_range)

array_or_range: The range or array to rotate. The first row of the input becomes the first column of the output, and so on.

The result spills, so the cells it needs must be empty.

Basic example

With this table in A1:C2:

   A      B      C
1  Name   Dept   Salary
2  Ana    Sales  52000

=TRANSPOSE(A1:C2)

Returns a 3-row, 2-column table:

Name    Ana
Dept    Sales
Salary  52000

A row of 3 cells becomes a column of 3 cells, and a 3x2 range becomes a 2x3 range.

When to use it

Turn a column of data into a row (or the reverse)

=TRANSPOSE(A2:A13)

This lays a 12-item column out horizontally, useful for headers built from a list.

Make a header row from a list

If your category list lives in a column but you want it across the top of a table, =TRANSPOSE(Lists!A2:A8) keeps the headers in sync with the list.

Sort or filter horizontally

SORT, FILTER and UNIQUE work on rows. To use them on a horizontal range, rotate, apply the function, and rotate back:

=TRANSPOSE(SORT(TRANSPOSE(A1:H1)))

This sorts a single row of values.

Combine with other functions

Let VLOOKUP read a horizontal table: =VLOOKUP(E2, TRANSPOSE(A1:H2), 2, FALSE) rotates the table so a vertical lookup works on it.
Match orientations: when one array is a row and another is a column, TRANSPOSE one of them so they line up before combining.

Matrix operations

TRANSPOSE is a building block for matrix math with MMULT. For example =MMULT(A1:C3, TRANSPOSE(E1:G1)) multiplies a matrix by a column vector.

Transpose vs. Paste special > Transposed

Edit > Paste special > Paste transposed copies the values once and leaves static data. TRANSPOSE stays live: if the source changes, the output changes. Use paste-transposed for one-off reshaping, and TRANSPOSE when the layout must follow the source.

Common errors

#REF!: Array result was not expanded because it would overwrite data. Cells in the output area are not empty. Clear them or move the formula.
#VALUE!. The input was not a valid range or array, for example a single value passed where a range is expected.
Unexpected blanks. The source range includes empty cells, which come through as blanks. Trim the range or use FILTER first.
Wrong orientation after combining with other functions. Remember that functions like SPLIT return a row, and that TRANSPOSE(SPLIT(A1, ",")) returns a column.

Frequently asked questions

Does TRANSPOSE work with whole columns? Yes, but TRANSPOSE(A:A) produces over a million columns and fails. Use a bounded range like A1:A100.

Can I edit the transposed cells? No. They are formula output. Change the source cells instead.

What is the difference between TRANSPOSE and FLATTEN? TRANSPOSE rotates and keeps the shape. FLATTEN combines everything into a single column.

Related Functions

FLATTEN: Combine a range into a single column.
SORT: Sort the rows of a range.
SPLIT: Divide text across cells.
SEQUENCE: Generate an array of numbers.
ARRAYFORMULA: Apply a formula across a range.

TRANSPOSE