Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to transpose rows to columns in Excel and Google Sheets
Topic:Data cleaning & shapingExcel & Google Sheets
=TRANSPOSE(A1:B3)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Month | Sales |
| Jan | 100 |
| Feb | 200 |
| Mar | 300 |
| Apr | 150 |
| May | 250 |
=TRANSPOSE(A1:B3)→Month, Jan, Feb | Sales, 100, 200
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Static method: Copy the source range, go to the destination cell, right-click → Paste Special → Transpose (Excel) or Paste Special → Transposed (Sheets).
- 2Formula method: In an empty area, enter =TRANSPOSE(A1:B3) — in Excel 365 this spills automatically; in older Excel enter it as an array formula with Ctrl+Shift+Enter.
- 3Adjust the source range to match your actual data dimensions.
Tips
- The TRANSPOSE formula updates automatically when source data changes; Paste Special is a snapshot. Use TRANSPOSE when the source data is dynamic.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can TRANSPOSE handle a single row?
Yes — TRANSPOSE of a 1×N range returns an N×1 column, and vice versa.
Why do I get a #VALUE! error with TRANSPOSE in older Excel?
You must select a range the exact size of the transposed output, type the formula, and press Ctrl+Shift+Enter to enter it as an array formula.
More on Data cleaning & shaping
See all →How-to
How to merge two columns
Combine two columns into one with the & operator — perfect for joining first and last names into a full name.
How-toHow to create a sequential invoice number
Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.
How-toHow to number rows within each group
Assign sequential row numbers that restart for each group using COUNTIF with a mixed reference in Excel and Google Sheets.
How-toHow to remove blank rows
Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.
How-toHow to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.
How-toHow to fill blank cells with a value
Replace empty cells with a default value using IF+ISBLANK, Go To Special fill-down, or a Power Query step for bulk data.