Excel TRANSPOSE Function

last modified October 5, 2026

The TRANSPOSE function is a lookup and reference function in Excel that flips the orientation of a range or array. It turns rows into columns and columns into rows, so a 3x2 block of cells becomes a 2x3 block. In Microsoft 365 and Excel 2021 the result spills automatically; in older versions it is entered as a legacy array formula with Ctrl+Shift+Enter. This tutorial covers the syntax, array entry, blank cells, formatting, and how TRANSPOSE compares with Paste Special, TOCOL, and TOROW.

TRANSPOSE Function Basics

The TRANSPOSE function reads a source array and returns a new array whose first row becomes its first column, whose second row becomes its second column, and so on. The source range is never modified.

Component	Description
Function Name	TRANSPOSE
Syntax	=TRANSPOSE(array)
Arguments	array - the range or array to transpose
Return Value	A new array with rows and columns swapped
Availability	All versions of Excel (dynamic spill in Excel 365 and Excel 2021+)

The table below describes the single argument in more detail.

Argument	Meaning
array	The range or array whose orientation you want to flip, such as A1:C3 or an array constant such as {1,2;3,4}. TRANSPOSE accepts exactly one argument.

TRANSPOSE returns values only. It does not copy number formats, fills, fonts, borders, or column widths, and it does not return the underlying formulas of the source cells. Apply formatting to the output range separately.

Basic TRANSPOSE Example

This example demonstrates the simplest use of TRANSPOSE with a vertical range.

A	B	C
Apple
Banana
Cherry
=TRANSPOSE(A1:A3)

The table shows a vertical list in column A. The formula in A4 transposes the three-by-one range into a one-by-three row that spills across A4:C4 and reads Apple, Banana, Cherry.

=TRANSPOSE(A1:A3)

In Excel 365 or Excel 2021, simply enter the formula and it spills to the right. In older versions, select the three target cells, type the formula, and press Ctrl+Shift+Enter. The result displays the vertical list horizontally.

TRANSPOSE a Data Table

This example shows how to transpose an entire table, switching rows and columns.

A	B	C
Name	Age	Score
John	25	85
Mary	30	92
=TRANSPOSE(A1:C3)

The original table has headers in row 1 and data below. After transposing, the headers Name, Age, and Score become the first column and the names and values shift into rows, so the result is a three-by-three block. The transposed data reads Name, John, Mary on the first row; Age, 25, 30 on the second; and Score, 85, 92 on the third.

=TRANSPOSE(A1:C3)

This formula flips the entire table. Headers become the first column, and rows become columns. The result preserves every value and its relationships while changing the orientation.

Single Row to Single Column

A single row becomes a single column and a single column becomes a single row. This example flips a horizontal list of quarters into a vertical one.

A	B	C
Q1	Q2	Q3
=TRANSPOSE(A1:C1)

The one-by-three range in row 1 is returned as a three-by-one range. Because the source is a single row, the output is a single column that fills A2, A3, and A4 with Q1, Q2, and Q3.

=TRANSPOSE(A1:C1)

Dynamic TRANSPOSE with Spill Range

Transposing a two-dimensional array returns a two-dimensional result. Modern Excel spills that result into the surrounding empty cells automatically.

A	B	C
Q1	Q2	Q3
100	200	300
=TRANSPOSE(A1:C2)

The two-by-three source in A1:C2 becomes a three-by-two result that spills into A3:B5. The first column holds the quarter labels Q1, Q2, and Q3, and the second column holds the matching values 100, 200, and 300.

=TRANSPOSE(A1:C2)

In Excel 365 and Excel 2021 the result is a spilled range that resizes automatically when the source values change. The output stays linked to the source, so editing a source cell updates the transposed cell immediately.

Array Entry: Spill vs Ctrl+Shift+Enter

TRANSPOSE always returns an array, so how you confirm the formula depends on the Excel version.

Version	How to enter	Result
Microsoft 365, Excel 2021 and later	Type the formula in one cell and press Enter	A dynamic array that spills into the neighbouring cells
Excel 2019 and earlier	Select the whole target range, type the formula, press Ctrl+Shift+Enter	A legacy array formula wrapped in curly braces

For legacy array entry, the target range must have the same number of rows as the source has columns and the same number of columns as the source has rows. If you enter the formula in a single cell and press Enter only, the result collapses to one value instead of filling the range. In modern Excel there is no need to pre-select the target, but the spill area must be empty or Excel reports a #SPILL! error.

{=TRANSPOSE(A1:C3)}

Blank Cells Become Zero

A blank cell in the source is treated as the number zero when it is transposed, so empties appear as 0 in the result. This is one of the most common surprises with TRANSPOSE.

A	B	C
5
15
=TRANSPOSE(A1:A3)

With 5 in A1, a blank in A2, and 15 in A3, the transposed row reads 5, 0, 15. The empty source cell becomes 0 rather than an empty output cell.

=TRANSPOSE(A1:A3)

To keep the blanks blank, replace the empty cells with an empty string before transposing. The IF test returns an array, so the formula is still an array formula.

=TRANSPOSE(IF(A1:A3="","",A1:A3))

In Excel 365 and Excel 2021, TOROW offers an even shorter fix. Its second argument, ignore, accepts 1 to skip blanks, 2 to skip errors, and 3 to skip both, so =TOROW(A1:A3,1) returns only 5 and 15.

Formatting and Number Formats Are Not Carried

TRANSPOSE moves values, not presentation. A source cell formatted as currency, a percentage, or a date is returned as a plain value and adopts whatever number format the destination cells already have. Bold, italic, fill color, borders, and column widths are not copied either.

A	B
Price	$1,000
Tax	$80
=TRANSPOSE(A1:B2)

The currency-formatted values in B1 and B2 appear as 1000 and 80 in the transposed block unless the output cells are formatted as currency again. The same rule applies to formulas: if a source cell contains a formula, TRANSPOSE returns the calculated value, not the formula text. To copy formatting along with the values, use Paste Special with the Transpose option instead.

TRANSPOSE with Other Functions

TRANSPOSE can be combined with other dynamic array functions. This example pairs it with FILTER.

A	B
Product	Sales
Widget	150
Gadget	200
Doodad	75
	=TRANSPOSE(FILTER(A2:B4,B2:B4>100))

The inner FILTER keeps the two products whose sales exceed 100, Widget and Gadget, and TRANSPOSE turns the resulting two-by-two block so that Widget and Gadget sit side by side above 150 and 200.

=TRANSPOSE(FILTER(A2:B4,B2:B4>100))

The combination first filters the data, then reshapes the remaining records into a horizontal layout. Because both functions return arrays, the formula requires the same modern Excel that supports spilled results.

TRANSPOSE for Matrix Operations

TRANSPOSE is essential for matrix mathematics in Excel. This example shows a simple matrix transposition.

A	B
1	4
2	5
3	6
	=TRANSPOSE(A1:B3)

The three-by-two matrix in A1:B3 becomes a two-by-three matrix. The first column of the source, 1, 2, 3, becomes the first row of the result, and the second column, 4, 5, 6, becomes the second row. The result preserves all values while changing their positions.

=TRANSPOSE(A1:B3)

This operation is a building block for matrix multiplication with MMULT, where one operand often has to be transposed to satisfy the dimension rules.

TRANSPOSE vs Paste Special vs TOCOL and TOROW

Excel offers several ways to change the orientation of data. Each has a different behaviour, so the right choice depends on whether you need live updates, formatting, or a flattened list.

Method	Behaviour	Updates with source	Carries formatting
TRANSPOSE function	Swaps rows and columns, keeping the two-dimensional shape	Yes	No
Paste Special > Transpose	One-time copy with rows and columns swapped	No	Yes, if selected
TOCOL	Flattens the whole array into a single column	Yes	No
TOROW	Flattens the whole array into a single row	Yes	No

For a single row or column, TOCOL and TOROW produce the same result as TRANSPOSE and add an ignore argument that can drop blanks or errors. For a two-dimensional array they are not equivalent: TOCOL and TOROW collapse the grid into one row or one column, while TRANSPOSE keeps the grid and just swaps its axes. Paste Special is the only method that can carry formatting, but it creates a static copy that no longer follows the source. TOCOL and TOROW are available only in Excel 2021 and later and in Microsoft 365.

TRANSPOSE Errors and Troubleshooting

Cause	Symptom	Fix
Source range contains blank cells	Zeros appear in the output	Wrap the source in IF(range="","",range), or use TOROW/TOCOL with ignore
Source contains an error value	The same error spills into the output	Clean the source, or wrap the whole formula in IFERROR
Cells the result needs are not empty	#SPILL! error	Clear the spill area, or move the formula to an empty region
Legacy Excel, entered with Enter only	Only a single value appears	Select the reversed-size target and confirm with Ctrl+Shift+Enter
Legacy Excel, wrong target size selected	#N/A or a partial result	Make target rows equal source columns and target columns equal source rows
Formatting looks missing	Plain values without colors or number formats	Format the output separately, or use Paste Special > Transpose
Source range was deleted	#REF! error	Repair the reference or rebuild the formula against a valid range
Formula placed inside an Excel Table	Only a single value appears instead of a spill	Place the formula outside the table in a normal range

The TRANSPOSE function flips rows and columns without disturbing the source. A single row becomes a single column, a three-by-two block becomes a two-by-three block, and in Excel 365 and Excel 2021 the result spills and stays live as the source values change. Remember the two classic gotchas: blank source cells appear as zeros unless you guard them, and formatting is never carried because only values are returned. For one-time conversions that keep formatting, Paste Special > Transpose is the right tool, and for flattening a grid into a single row or column, TOCOL and TOROW are the modern alternatives. Mastering TRANSPOSE makes reshaping data fast and reliable.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.