Lookup & Reference

TRANSPOSE Function in Excel

Converts a vertical range to horizontal, or vice versa.

Syntax

  • =TRANSPOSE(array)

Arguments

  • array (required): The range or array to transpose

Examples

  • =TRANSPOSE(A1:A5) - Convert column to row - Result: Horizontal array
  • =TRANSPOSE(A1:E1) - Convert row to column - Result: Vertical array

Excel TRANSPOSE function documentation

  • TRANSPOSE converts a vertical range to horizontal or vice versa — rows become columns.
  • Syntax: =TRANSPOSE(array). In M365 returns dynamic spill; legacy required Ctrl+Shift+Enter.
  • Use when pasting orientation would break live links — formula stays connected to source.
  • Related: [TOROW](/functions/torow/), [TOCOL](/functions/tocol/), [HSTACK](/functions/hstack/), [VSTACK](/functions/vstack/).

TRANSPOSE syntax and spill behavior

  • Output size swaps row/column count — 5×3 input becomes 3×5 output.
  • Destination must fit — #SPILL! if not enough empty cells.
  • TRANSPOSE of TRANSPOSE returns original shape.
  • Paste Special → Transpose is static — TRANSPOSE formula updates with source.

Step-by-step: flip monthly row to column

  • Step 1 — Jan–Dec revenue in B2:M2 (one row).
  • Step 2 — Select vertical block with 12 rows free — enter =TRANSPOSE(B2:M2).
  • Step 3 — Spill fills 12×1 column for chart-friendly layout.
  • Step 4 — Link chart to spilled column.
  • Step 5 — M365 alternative: =TOCOL(B2:M2) for single-column vector.

TRANSPOSE vs TOROW vs TOCOL vs Paste Special

  • TRANSPOSE — full 2D flip row↔column.
  • TOCOL — force any range into one column vector.
  • TOROW — force into one row vector.
  • Paste Special Transpose — values only; no auto-update.

Worked examples to copy

  • Row to column: =TRANSPOSE(A1:J1).
  • Column to row: =TRANSPOSE(A1:A10).
  • Matrix flip: =TRANSPOSE(A1:C5).
  • With SORT: =SORT(TRANSPOSE(scores_row)).

People also ask

  • TRANSPOSE not working? — Legacy Excel needs array entry; M365 spills automatically.
  • TRANSPOSE change source? — Yes — transposed output updates when source changes.
  • TRANSPOSE vs index paste? — Formula keeps link; paste breaks it.
  • TRANSPOSE large range? — Ensure spill area is empty.

Common errors

  • Result array must fit in available space

Use cases

  • Rotate data
  • Change data orientation
  • Matrix operations

Frequently asked questions

  • What is TRANSPOSE in Excel? TRANSPOSE rotates data orientation - converting rows to columns or columns to rows. In Microsoft 365/Excel 2021+, it spills automatically. In older versions, select the destination range, type the formula, and press Ctrl+Shift+Enter.
  • How do I transpose and paste values only? Copy your data, right-click destination, select Paste Special, check 'Transpose' and 'Values'. This pastes transposed values without formulas. Alternatively, use TRANSPOSE formula then copy and paste values.
  • Can TRANSPOSE handle mixed data types? Yes, TRANSPOSE works with numbers, text, dates, formulas, and empty cells. It preserves all data types and formatting references. The transposed result maintains the original cell values exactly.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use TRANSPOSE

  • Rotate data — common Lookup & Reference scenario for TRANSPOSE.
  • Change data orientation — common Lookup & Reference scenario for TRANSPOSE.
  • Matrix operations — common Lookup & Reference scenario for TRANSPOSE.

TRANSPOSE in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • TRANSPOSE syntax: =TRANSPOSE(array)
  • array (required): The range or array to transpose
  • Confirm TRANSPOSE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm TRANSPOSE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • INDIRECT (/functions/indirect/): Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Errors to watch for

  • Result array must fit in available space — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter TRANSPOSE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

TRANSPOSE worked examples to copy

  • =TRANSPOSE(A1:A5) — Convert column to row. Expected result: Horizontal array.
  • =TRANSPOSE(A1:E1) — Convert row to column. Expected result: Vertical array.

TRANSPOSE reference summary for crawlers and offline review

  • TRANSPOSE belongs to the Lookup & Reference category in Excel. Converts a vertical range to horizontal, or vice versa.
  • Full syntax: =TRANSPOSE(array). Open /functions/transpose/ for parameters, FAQs, and related pages.
  • Common mistakes: Result array must fit in available space
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.