Quick Navigation
TRANSPOSE Function
Summary
The Excel TRANSPOSE function flips the orientation of a range or array, converting vertical data into horizontal layout or horizontal data into vertical. This powerful array function rotates rows into columns and columns into rows without duplicating source data.
Syntax
TRANSPOSE(array)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array/Range |
Yes | Source range or array to rotate. Must select exact output dimensions before entering formula |
Using the TRANSPOSE Function
TRANSPOSE excels at restructuring data layouts for better analysis, reporting, or chart preparation. Unlike manual copy-paste transpose which creates static duplicates, TRANSPOSE formulas remain linked to source data and update automatically when originals change.
Common TRANSPOSE Examples
Basic Vertical to Horizontal
=TRANSPOSE(A1:A5)Converts single column A1:A5 (5 rows) into single row across 5 columns. Select 1x5 range first, then enter formula.
Table Rotation
=TRANSPOSE(A1:B4)Rotates 2x4 table into 4x2 table. Select exact 4 rows x 2 columns output range before formula entry.
Microsoft 365 Dynamic Spill
=TRANSPOSE(A1:B4)In current Microsoft 365, enter in single cell and results spill automatically without selecting range.
Frequently Asked Questions
Common Errors and Solutions
#SPILL! error
Cause: Output range blocked by data in Microsoft 365
Solution: Clear the spill range completely before entering formula
#VALUE! error
Cause: Output range dimensions don't match transposed input
Solution: Count input rows/columns, select matching columns/rows for output
Formula shows in all cells
Cause: Entered as regular formula instead of array formula
Solution: Select output range, retype formula, press Ctrl+Shift+Enter
Notes
- Microsoft 365: Enter in top-left cell, press Enter (dynamic array spills automatically)
- Legacy Excel: Select full output range → type formula → Ctrl+Shift+Enter
- Source data changes automatically update transposed results
- Does NOT copy formatting, comments, or data validation
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+