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 with a formula in Excel and Google Sheets
Excel & Google Sheets
=TRANSPOSE(A1:B2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| header1 | header2 |
| data1 | data2 |
| data3 | data4 |
=TRANSPOSE(A1:B2)→header1, data1 | header2, data2
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
- 1Select the range of cells you want to transpose.
- 2In an empty cell where you want the transposed data to start, enter the TRANSPOSE formula.
- 3Ensure the formula references the correct range of your original data.
Tips
- Make sure the target area has enough space to accommodate the transposed data without overlapping other data.
- In Excel, after entering the formula, press Ctrl+Shift+Enter instead of Enter if you're using an older version or if the formula is part of an array formula.
- Always double-check the referenced range to avoid errors in your transposed data.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my data is too large to fit in the target area?
Ensure the target area is large enough to accommodate the transposed data. You may need to clear some space or adjust your worksheet layout.
Can I transpose data with formulas that include functions?
Yes, you can transpose data that includes formulas. The TRANSPOSE function will copy the formulas as well, recalculating them based on their new orientation.
Is there a limit to the amount of data I can transpose?
There is no specific limit, but performance may decrease with very large datasets. Consider breaking down large datasets into smaller chunks if necessary.