Cellwise Start free

Dynamic arrays function

Excel TRANSPOSE function

TRANSPOSE flips a range so its rows become columns and its columns become rows. A list down column A becomes a row across. Because it is a formula, the flipped copy updates when the original changes; in Excel 365 it spills into as many cells as it needs.

=TRANSPOSE(array)

Arguments

array
The range or array to flip.

TRANSPOSE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1MonthSalesTarget
2Jan1820017000
3Feb1690017500
4Mar2140019000
5Apr1980020000

Download this sheet as an Excel practice file (.xlsx)

  1. =TRANSPOSE(A1:C5)

    Result: spills 3 rows × 5 columns

    MonthJanFebMarApr
    Sales18200169002140019800
    Target17000175001900020000

    The five-row, three-column table becomes three rows and five columns, with the months across the top.

  2. =TRANSPOSE(A2:A5)

    Result: spills 1 row × 4 columns

    JanFebMarApr

    A column of months becomes a row: Jan, Feb, Mar, Apr. Handy for a header row.

  3. =ROWS(TRANSPOSE(A1:C5))

    Result: 3

    The flipped table has 3 rows, one for each original column.

  4. =TRANSPOSE(SORT(A2:B5,2,-1))

    Result: spills 2 rows × 4 columns

    MarAprJanFeb
    21400198001820016900

    Sort first, then flip: the months run across from best to worst sales, Mar first.

How to use TRANSPOSE

  1. Click the top-left cell where the flipped copy should go.
  2. Type =TRANSPOSE( and select the range.
  3. Press Enter. In Excel 365 it spills; in Excel 2019 and earlier, select the full output area first and press Ctrl+Shift+Enter.

Common TRANSPOSE mistakes

Not enough empty cells

The result needs as many empty cells as the flipped shape. Anything in the way gives #SPILL!.

Editing one cell of the result

A spilled result is one formula. To change values, change the source, or copy and Paste Special > Values first.

Empty cells turning into 0

Blank source cells show as 0 in the result. Use =TRANSPOSE(IF(A1:C5="","",A1:C5)) to keep them blank.

TRANSPOSE FAQ

How do I convert a row to a column in Excel?

Use =TRANSPOSE(range) for a live copy, or copy the range and use Paste Special > Transpose (Ctrl+Alt+V, then E) for a one-off static copy.

What is the difference between TRANSPOSE and Paste Special Transpose?

TRANSPOSE is a formula that updates with the source. Paste Special > Transpose pastes fixed values and formats that do not update.

Why does TRANSPOSE return #SPILL!?

Some cells where the result needs to go are not empty. Clear them or move the formula.

Guides that use TRANSPOSE

Related functions

Updated