How to Transpose Data in Excel: Rows to Columns (4 Methods)

What Transpose Does to Your Data

Transposing swaps the row and column orientation:

• A range that is 5 rows tall and 3 columns wide becomes 3 rows tall and 5 columns wide.
• What was in row 1 becomes column 1. What was in column A becomes row 1.
• Cell values move to their transposed positions. Formulas may or may not carry over depending on the method.

Transpose does not reformat, sort, or filter your data — it only rotates the orientation. The total number of cells stays the same.

Method 1: Paste Special Transpose (One-Time Static Copy)

The most commonly used method. Produces a static copy — the output does not update when the source changes.

1. Select the data range you want to transpose.
2. Press Ctrl + C to copy.
3. Click an empty cell where the transposed data should start. Make sure the destination has enough empty space — a 10-column by 5-row source needs 5 columns and 10 rows of empty space in the destination.
4. Right-click the destination cell and choose Paste Special (or press Ctrl + Alt + V).
5. Check the Transpose checkbox in the bottom-right of the dialog.
6. Click OK.

The result is a static copy with values and formatting. You can edit it independently of the original. If the original changes, the transposed copy does not update — use the TRANSPOSE formula for a live link.

To paste only values without formatting or formulas, check Values in addition to Transpose in the Paste Special dialog.

Method 2: TRANSPOSE Formula (Live Linked Copy)

In Excel 365 and Excel 2021, the TRANSPOSE function returns a spilled array that updates whenever the source data changes:

=TRANSPOSE(A1:E5)

Enter this in a blank cell. Excel automatically fills the required range with the transposed output. No Ctrl+Shift+Enter is needed in modern Excel — it spills automatically.

Use cases for the TRANSPOSE formula:

• A summary table that needs to show the same data in two different orientations for different readers
• A dashboard that pulls data from a monthly input sheet that adds new columns as the year progresses
• A reporting template where source data orientation may change and the transposed view must stay in sync

Limitation: The output cells are controlled by the TRANSPOSE formula and cannot be edited individually. To make the output editable, copy the spill range (Ctrl + C) and paste as values to remove the formula link.

Method 3: Power Query (For Complex Reshaping and Repeatable Workflows)

Power Query is the right choice when you need to transpose as part of a larger data cleaning workflow, or when the data arrives regularly and needs the same transformation each time.

For simple row-to-column transpose:

1. Select the data and go to Data > From Table/Range.
2. In Power Query Editor, go to Transform > Transpose.
3. Click Use First Row as Headers if the column headers are now in the first row after transpose.
4. Click Close & Load.

For the common scenario of "months across columns, need months as a single column" (wide to long format), Power Query's Unpivot Columns is more appropriate than Transpose. Select the month columns, go to Transform > Unpivot Columns, and Power Query creates two columns: Attribute (month name) and Value (the number).

Method 4: Free Excel Transpose Tool

For a quick one-off transpose without modifying your original file:

1. Upload your Excel workbook to the Excel Transpose tool.
2. Choose the sheet and data range to transpose.
3. Preview the result to confirm the orientation is correct.
4. Download the transposed file.

The tool runs entirely in your browser — the file is not sent to any server. It handles workbooks with multiple sheets and produces the transposed result as a new downloadable file.

When NOT to Transpose

Transposing is not always the right solution. Consider alternatives when:

• The data feeds a PivotTable: PivotTables require vertical data (one record per row). If the real goal is to summarize data differently, use a PivotTable field arrangement rather than physically transposing the data.
• The data has formulas referencing fixed positions: After transposing, formula references that use absolute addresses ($A$2:$A$100) will point to wrong locations. Use Paste Special > Values to paste without formulas, then rebuild any needed formulas in the transposed layout.
• The issue is a report formatting preference: If you just want the data to look different in a printed report, use a PivotTable or chart axis settings rather than physically reorganizing the data.

Transpose Across Multiple Sheets

Excel has no built-in multi-sheet transpose. For multiple sheets:

1. Combine the sheets into one using Power Query Append or VSTACK in Excel 365.
2. Transpose the combined result.

Alternatively, use a TRANSPOSE formula on each sheet individually, then consolidate the transposed outputs:

=VSTACK(TRANSPOSE(Sheet1!A1:E5),TRANSPOSE(Sheet2!A1:E5))

Common Transpose Problems and Fixes

• Formulas break after Paste Special Transpose: Relative cell references shift based on the transpose operation. A formula like =B2 may end up pointing to an entirely different cell after transposing. After transposing, review formula results carefully. If you only need values, paste as Values to avoid formula reference issues entirely.
• Column widths look wrong after transpose: Column widths and row heights are not transposed — they stay with their respective rows/columns. After transposing, select all and double-click any column boundary to auto-fit widths.
• Dates show as serial numbers after transpose: Paste Special Transpose does not always carry over cell formatting. Select the date column after pasting and apply the date format via Format Cells > Date.
• TRANSPOSE formula shows #SPILL! error: There is existing content in the cells where the output needs to spill. Clear the cells in the expected output range and the error resolves.

Validation Checklist After Transposing

• Row count of the transposed output equals the original column count.
• Column count of the transposed output equals the original row count.
• First and last values in the output match the expected transposed positions from the source.
• Dates display as dates, not serial numbers.
• Formulas (if any were included) return the expected results in their new positions.
• Total values (SUM, COUNTA) in the transposed output match totals from the original data.

Frequently Asked Questions

Can I transpose data in Excel without copy and paste?

Yes. Use the TRANSPOSE formula: =TRANSPOSE(A1:E5). This creates a transposed live view of the source data without any copy-paste steps. In Excel 365 and 2021, it spills automatically into the required output range. For one-way transpose, the free Excel Transpose tool also works without any copy-paste.

How do I undo a transpose in Excel?

If you used Paste Special Transpose, press Ctrl + Z immediately to undo. If you have saved after transposing, you need to transpose again to get back to the original orientation — or restore from a backup copy. This is why saving a backup before any structural transformation is recommended.

Does transposing change the data or just the layout?

Values are moved to new positions — this is a structural change to the workbook, not a display change. The original data remains intact unless you delete it. The transposed copy is independent of the original when using Paste Special (unless you used the TRANSPOSE formula for a live link).

How do I transpose a table and keep all the formulas working?

This is difficult because formula references are position-dependent and do not automatically remap after a transpose. The safest approach: transpose values only (Paste Special > Values + Transpose), then manually recreate any formulas in the new transposed positions. For complex workbooks, this is often faster than debugging misaligned formula references after the fact.