TRANSPOSE Function

Flips rows into columns and columns into rows.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

A spilled, flipped array

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

TRANSPOSE rotates a range: the first row becomes the first column, and so on. Unlike Paste Special → Transpose, the result stays linked to the source.

In Microsoft 365 it spills automatically; in older versions you select the destination and press Ctrl+Shift+Enter.

Syntax

=TRANSPOSE(array) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to flip.

Examples

Example 1 — Months down the side

Turn a horizontal row of months into a column.

A3fx=TRANSPOSE(A1:C1)
ABC
1JanFebMar
2
3Jan
4Feb
5Mar
=TRANSPOSE(A1:C1) Explain

Example 2 — Flipping a whole table

Swap a report's orientation while keeping it live.

=TRANSPOSE(A1:D10) Explain
Blank source cells come through as 0. Use =TRANSPOSE(IF(A1:D10="", "", A1:D10)) to keep them blank.

Common mistakes

  • #SPILL! errors. The flipped shape doesn't fit — clear the destination area.
  • Older Excel. Forgetting Ctrl+Shift+Enter shows only one value.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.