Excel TRANSPOSE Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 89 of 105 · Full Excel course

The Excel TRANSPOSE function flips a range so that rows become columns and columns become rows, returning the rotated array. Use TRANSPOSE in Excel when data arrives horizontally but your report needs it vertically, or when you want a linked, formula-driven copy that updates whenever the source changes.

Table of Contents

TRANSPOSE syntax

=TRANSPOSE(array)

Arguments

Argument	Required	Meaning
array	Required	The range or array to rotate. A range of R rows by C columns returns C rows by R columns.

TRANSPOSE returns an array, so it behaves differently depending on your Excel version. In Excel 365 and 2021 the result spills automatically into adjacent cells. In Excel 2019 and earlier you must select the output block first and confirm with Ctrl+Shift+Enter.

Step-by-step example

Names John, Peter, Mary and Anne are in A1:A4 and you want them across B1:E1.

Excel 365 / 2021

Select B1 and type =TRANSPOSE(A1:A4).
Press Enter. The four names spill into B1:E1 with a thin blue border marking the spill range.

Excel 2019 and earlier

Select B1:E1 (1 row by 4 columns, the mirror of 4 by 1).
Type =TRANSPOSE(A1:A4).
Press Ctrl+Shift+Enter. Excel wraps the formula in braces {=TRANSPOSE(A1:A4)}.

Practical use cases

1. Monthly figures from a column into a report row

=TRANSPOSE(A2:A13)

Twelve vertical values become B1:M1, and they stay linked to the source.

2. Rotate a whole table

=TRANSPOSE(A1:C10)

A 10-row by 3-column table becomes 3 rows by 10 columns, headers included.

3. Feed a horizontal range to a vertical function

=SUMPRODUCT(TRANSPOSE(B1:F1), B3:B7)

Multiplies a row of weights by a column of values without a helper range.

4. Horizontal lookup list for a drop-down

Data validation lists must be vertical or a single row; TRANSPOSE lets you reference a row of headers as a vertical list in a helper range.

5. One-time static flip

If you do not need a live link, copy the range and use Paste Special > Transpose. The result is plain values, with no formula to maintain.

Common mistakes and errors

#VALUE! in older Excel – the formula was entered with Enter instead of Ctrl+Shift+Enter, or the selected output block is the wrong shape.
#SPILL! in Excel 365 – something is blocking the spill range. Clear the cells to the right or below the formula.
Blank cells become 0 – TRANSPOSE converts empty source cells to zero. Wrap it: =IF(TRANSPOSE(A1:A10)="", "", TRANSPOSE(A1:A10)).
Cannot edit part of an array – in legacy array mode you must select the entire output block to change or delete the formula.
Source grows, output does not – a fixed range like A1:A4 will not pick up A5. Point TRANSPOSE at an Excel Table column or a dynamic range.

Tips and best practices

Check your Excel version first. If formulas spill automatically you have dynamic arrays and can skip Ctrl+Shift+Enter entirely.
Use Paste Special > Transpose for one-off flips; keep the TRANSPOSE formula for data that must stay linked to its source.
Point TRANSPOSE at a Table column (Data[Month]) so the output grows when rows are added.
Format after transposing. Number formats and column widths are not carried over, so apply them to the output block.
Combine with FILTER or SORT in Excel 365 for a transposed, filtered report in a single formula.