How to Transpose Data in Excel
Transpose data in Excel to switch rows into columns or columns into rows. Learn paste transpose, TRANSPOSE formulas, and when each method works best.
Transposing data in Excel switches rows into columns or columns into rows. It is useful when a report is laid out horizontally but your analysis needs a vertical table, or when a copied export is shaped for presentation instead of formulas.
There are two main approaches: paste transpose for a one-time change, and the TRANSPOSE function when the output should stay linked to the source.
Paste transpose
Use paste transpose when you want a static copy.
Step 1. Select the source range, such as A1:F4.
Step 2. Copy it.
Step 3. Select the top-left cell where the transposed output should start.
Step 4. Open Paste Special.
Step 5. Choose Transpose.
Excel flips the copied range so rows become columns and columns become rows.
This is the fastest option for reshaping a small report, a list of categories, or a one-off planning table.
Use the TRANSPOSE function
Use the formula when the output should update as the source changes.
=TRANSPOSE(A1:F4)In current Excel, enter the formula in the top-left output cell and Excel spills the result into the needed range. In older Excel versions, you may need to select the full output range first and confirm it as an array formula.
WATCH OUT
Practical example: reshape a monthly budget
Suppose a small business budget export has months across the top and categories down the left. That is readable for a manager, but sometimes you need each month as its own row for charting or pivot-table work.
Paste transpose can quickly flip a small summary. If the budget updates every week, use TRANSPOSE so the reshaped view stays connected to the original.
The same issue appears in sales pipeline summaries, employee schedules, and content calendars when a human-friendly layout needs to become analysis-friendly.
Which method should you use?
| Need | Best method |
|---|---|
| One-time cleanup | Paste transpose |
| Output updates with source | TRANSPOSE formula |
| Final report for sharing | Paste values after transposing |
| Analysis table for pivots | Reshape into normal rows and columns |
Preserve the source when changing orientation
Use Paste Special > Transpose for a one-time layout change when the output should no longer follow the source. Use TRANSPOSE when the result should update as the original range changes. Dynamic-array results need an empty destination area; a value or formula in the spill range will block the output.
Before transposing, decide whether formulas should keep relative references or become values. For reporting handoffs, paste values after checking the output. For a live dashboard, keep the formula and document the source range so a future editor does not overwrite it. Check merged cells, blank rows, and dates after the change because orientation changes can expose formatting or reference assumptions that were hidden in the original layout.
The Griddy way
Transposing is simple for small ranges, but real workbooks often need the flipped data cleaned, labeled, and connected to formulas.
"Turn this horizontal monthly budget into a clean table with one row per month and keep the totals correct"
Griddy can reshape the range, preserve formulas where needed, and make the output easier to analyze.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.