How do I transpose a range without overwriting existing data?

Place the TRANSPOSE formula in a cell where the spill area is completely empty. If the spill would collide with other data, you will see a #SPILL! error. Clear or move the obstructing cells, or use the @ operator to return only the top-left value.

Can TRANSPOSE be used on a single cell?

Yes, a single-cell array transposes to itself, so the result is identical to the input. This is useful when you want to force a spill-compatible reference without changing the value.

Why does my TRANSPOSE formula return #VALUE! even though the source range looks fine?

#VALUE! appears when the source array contains an error value such as #DIV/0! or #N/A. TRANSPOSE does not filter out errors; it propagates them. Fix the error in the source range or wrap the source in IFERROR to replace the offending cells.

Is there a way to transpose only part of a table, like just the scores column?

Select the specific column you want to flip, e.g., =TRANSPOSE(D2:D7) for the Score column. The function works on any rectangular range, so you can isolate a single column, a single row, or a multi-column block before transposing.

TRANSPOSE function

TRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.

=TRANSPOSE(array)

Generate a TRANSPOSE formula

Describe what you need. The generator will reach for TRANSPOSE where TRANSPOSE is the right tool, and tell you when it is not.

Arguments

How TRANSPOSE reads its arguments

Argument	Required	Description
array	Required	A range or array constant; if omitted or empty the function returns #VALUE!, and if the reference is invalid it returns #REF!.

Returns

It returns a spilled array whose dimensions are the original row count as columns and column count as rows.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Turn header row into a vertical list

Student

Subject

Assignment

Score

Max Score

=TRANSPOSE(A1:E1)

Result: A five-cell column containing Student, Subject, Assignment, Score, Max Score.

The source range A1:E1 is a single horizontal row of five headers. TRANSPOSE swaps rows and columns, so the single row becomes a single column of five cells. Because the result spills downwards, each header appears in its own row, preserving the original order.

2. Convert a single student's scores to a horizontal list

=TRANSPOSE(FILTER(D2:D7, A2:A7="Alice"))

Result: A single row with the values 85, 92, 90.

FILTER extracts the Score column (D) for rows where the Student column (A) equals "Alice", producing a three-row single-column array. TRANSPOSE then turns that vertical array into a horizontal one, so the three scores appear side-by-side in one row. This is handy for creating a quick summary of a student's performance across assignments.

3. View the entire gradebook rotated

Alice	Math	HW1	85	100
Bob	Math	HW1	78	100
Alice	Science	Lab1	92	100
Bob	Science	Lab1	88	100
Alice	Math	HW2	90	100
Bob	Math	HW2	82	100

=TRANSPOSE(A2:E7)

Result: A 5-by-6 array where the original rows become columns and the original columns become rows.

The range A2:E7 contains six rows and five columns of grade data. When TRANSPOSE is applied, each of the six original rows becomes a column in the result, and each of the five original columns becomes a row. The spilled result therefore has five rows and six columns, giving a rotated view that can be useful for comparing subjects across students in a column-oriented layout.

Common errors

Which TRANSPOSE error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The destination area where the transposed array would spill already contains data or a table.	Clear the obstructing cells, move the formula to a blank region, or use @ to force a single-cell result.
#REF!	The array argument refers to a range that has been deleted or is otherwise invalid.	Update the reference to point to an existing range, or replace the deleted range with a new one.
#VALUE!	The source array includes an error such as #DIV/0!; TRANSPOSE propagates that error to the entire spill.	Correct the underlying error in the source range before applying TRANSPOSE.

Tips and when to use something else

Use TRANSPOSE when you need a quick, dynamic flip of rows and columns without copying data manually.
If you only need the first element of a transposed range, wrap the formula in INDEX to avoid a spill.
When you need to reshape data based on criteria (e.g., pivot-like summaries), consider using the newer BYROW or BYCOL functions instead of manual TRANSPOSE.
If you want to reshape data but also filter out blanks, combine TRANSPOSE with FILTER for cleaner results.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.

Frequently asked questions

How do I transpose a range without overwriting existing data?: Place the TRANSPOSE formula in a cell where the spill area is completely empty. If the spill would collide with other data, you will see a #SPILL! error. Clear or move the obstructing cells, or use the @ operator to return only the top-left value.
Can TRANSPOSE be used on a single cell?: Yes, a single-cell array transposes to itself, so the result is identical to the input. This is useful when you want to force a spill-compatible reference without changing the value.
Why does my TRANSPOSE formula return #VALUE! even though the source range looks fine?: #VALUE! appears when the source array contains an error value such as #DIV/0! or #N/A. TRANSPOSE does not filter out errors; it propagates them. Fix the error in the source range or wrap the source in IFERROR to replace the offending cells.
Is there a way to transpose only part of a table, like just the scores column?: Select the specific column you want to flip, e.g., =TRANSPOSE(D2:D7) for the Score column. The function works on any rectangular range, so you can isolate a single column, a single row, or a multi-column block before transposing.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17