How to Use the TRANSPOSE Function in Excel (and Google Sheets)
The TRANSPOSE function flips a rectangular range so that rows become columns and columns become rows.
Because it returns an array, the result spills into the surrounding cells automatically in modern Excel (365/2021 +) and in Google Sheets. In older Excel versions you must confirm the formula with Ctrl + Shift + Enter (CSE); otherwise only the first cell of the result is shown.
1. Syntax
=TRANSPOSE(array)
| Argument | Description |
|---|---|
| array | The range, literal array ({1,2,3}), named range, or another function that returns an array. It must be a single, contiguous block. |
Result: an array whose dimensions are the opposite of the source (rows ↔ columns).
2. Basic Example (Excel)
| A | B | C |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
Enter =TRANSPOSE(A1:C2) in any empty cell (e.g., E1). The formula spills into a 3‑row × 2‑column block:
| E | F |
|---|---|
| 1 | 4 |
| 2 | 5 |
| 3 | 6 |
Google Sheets behaves identically; just press Enter.
3. Excel vs. Google Sheets
| Feature | Excel (365/2021+) | Google Sheets |
|---|---|---|
| Dynamic‑array spill | ✅ (no CSE) | ✅ (always spills) |
| Need CSE in older versions | ✅ (pre‑365) | N/A |
| Volatile? | No | No |
| Errors when spill area is blocked | #SPILL! |
#REF! |
4. When to Use TRANSPOSE
- Re‑format data for charts that expect a specific orientation.
- Create lookup tables where the opposite axis is needed.
- Quickly turn a header row into a column (or vice‑versa) for reporting.
5. Key Points & Common Pitfalls
| Issue | Why it Happens | How to Fix It |
|---|---|---|
| Non‑contiguous range | TRANSPOSE only accepts a single rectangular block. |
Stack the ranges first with VSTACK (Excel 365) or {A1:B2; D1:E2} (Sheets), then transpose. |
| Blocked spill range | Destination cells contain data. | Clear the area or choose a larger blank range. |
| Pre-365 Excel needs a fixed output size | Legacy array formulas must be entered over a pre-selected range. | Select a range with swapped dimensions (3×2 for a 2×3 source) before pressing Ctrl+Shift+Enter. |
| Older Excel (pre‑365) | CSE not used. | After typing the formula, press Ctrl + Shift + Enter; Excel will wrap it in {}. |
6. Combining TRANSPOSE with Other Functions
| Goal | Formula (Excel 365/2021) | Formula (Google Sheets) | Description |
|---|---|---|---|
| Filter then transpose | =TRANSPOSE(FILTER(A2:A20, B2:B20="Approved")) |
Same | Returns only “Approved” rows from column A and flips them horizontally. |
| Remove blanks while transposing | =TRANSPOSE(FILTER(A2:A20, A2:A20<>"")) |
Same | Skips empty cells before transposing. |
| Sort before transposing | =TRANSPOSE(SORT(A2:A20, 1, 1)) |
=TRANSPOSE(SORT(A2:A20, 1, TRUE)) |
Sorts the source range ascending, then transposes. |
| Unique, sorted list in a row | =TRANSPOSE(SORT(UNIQUE(A2:A12))) |
Same | Removes duplicates, orders alphabetically, and places the result in a horizontal row. |
Example – Unique, Sorted List
=TRANSPOSE(SORT(UNIQUE(A2:A12)))
| A | Result (horizontal) |
|---|---|
| B12 | B12 A01 C07 D03 … |
7. FAQ (2‑3 sentences each)
What is the syntax for the TRANSPOSE function in Excel?
=TRANSPOSE(array) where array is a single, contiguous range, a literal array ({}), or any function that returns an array. The function returns a spilled array whose rows and columns are swapped.
Can I use TRANSPOSE in Google Sheets?
Yes. Google Sheets supports the same syntax and automatically spills the result; no special settings are required.
How does TRANSPOSE work with dynamic arrays?
In Excel 365/2021 and Google Sheets, TRANSPOSE is a dynamic‑array function, so you enter it in a single cell and the result expands (spills) into the necessary rows and columns. In legacy Excel you must confirm the formula with Ctrl + Shift + Enter to get the same behavior.
What happens if the source range is larger than the destination?
If the spill area is blocked, Excel returns a #SPILL! error (Sheets returns #REF!). Clear or enlarge the destination range so it can accommodate the transposed matrix.
Can I transpose multiple ranges at once?
No. TRANSPOSE only accepts one contiguous block. To combine several ranges you must first gather them into a single array (e.g., =TRANSPOSE(VSTACK(A1:B2, D1:E2)) in Excel 365, or =TRANSPOSE({A1:B2; D1:E2}) in Sheets) and then apply TRANSPOSE to that combined array.
All formulas shown are valid in the indicated platforms and require Excel 365/2021 or later for dynamic‑array behavior.