How to Transpose a Table in Excel Without Scrambling the Data

Transposing switches a table’s rows and columns. Use Paste Special > Transpose for a fixed copy, or the TRANSPOSE formula when the rotated result should update with its source.

For example, this 3-row by 4-column table:

Metric	Jan	Feb	Mar
Visitors	1,200	1,400	1,600
Signups	84	98	112

becomes a 4-row by 3-column table:

Metric	Visitors	Signups
Jan	1,200	84
Feb	1,400	98
Mar	1,600	112

If this is the intended publication structure, rename the first header from Metric to Month after transposing.

Method 1: Make a fixed transposed copy

This is the simplest method when preparing a final table for export or publication.

1. Select the complete source range, including its headers.
2. Press Ctrl+C. Do not use Cut or Ctrl+X; Excel’s Transpose paste requires copied cells.
3. Select a blank cell that will become the upper-left corner of the result.
4. Right-click and choose Transpose under Paste Options.
5. Check the result before deleting or hiding the source.

The destination must have enough empty space. Existing data and formatting in the destination area will be overwritten. If the source is formatted as an Excel table, the Transpose paste command is unavailable; Microsoft says to convert the table to a normal range or use the TRANSPOSE function instead. These behaviors are documented in Microsoft’s Transpose instructions.

A pasted copy is independent of the source. Later source edits will not flow into it.

Transpose values without carrying formulas

For a publication-ready snapshot, you may want displayed results rather than live formulas:

1. Copy the source range.
2. Select the destination cell.
3. Press Ctrl+Alt+V to open Paste Special.
4. Choose Values.
5. Select Transpose, then confirm.

Microsoft’s Paste options reference defines Values as the displayed results of formulas and includes Transpose in the Paste Special dialog. This approach prevents the copied output from depending on formulas that refer to other sheets or workbook cells.

Method 2: Create a transposed result that updates

In Microsoft 365 Excel, select the upper-left destination cell and enter:

=TRANSPOSE(A1:D3)

Press Enter. Excel returns the 4-row by 3-column result as a dynamic array. Leave the output area empty so the result has room to fill it.

The result remains linked to A1:D3: changing a source value updates the transposed output. To change the result, edit the formula or source range rather than typing over an individual output cell.

In legacy Excel, first select an output range with the reversed dimensions, enter the formula, and confirm it with Ctrl+Shift+Enter. Microsoft documents both current dynamic-array and legacy array behavior on the TRANSPOSE function page.

If the final file must contain fixed values, copy the completed formula output and paste it elsewhere as values before publishing.

Check the table before publishing

A transpose can be technically correct but still produce a poor dataset. Run these checks:

• Confirm the dimensions. An r × c source should become c × r.
• Spot-check intersections. In the example, the Jan–Visitors value must remain 1,200 and the Mar–Signups value 112.
• Review formulas. Copied formulas can change according to whether their references are relative, absolute or mixed. Verify the references and calculated results after transposing.
• Use one header row. Remove title rows, blank spacer rows and merged cells from the publishable range.
• Rename the first field. The upper-left label often needs revision after rotation, as Metric did in the example.
• Check data types. Dates should still be dates, numbers should sort numerically, and identifiers with leading zeros should remain text where required.
• Keep sensitive material out. Publish only a sanitized output range, not hidden notes, private source tabs or confidential records.

Excel worksheets allow 1,048,576 rows but only 16,384 columns, so a very tall source may not fit after rotation (Excel specifications). More importantly, thousands of columns are difficult to filter and interpret on a public page.

Transposing also does not automatically make a cross-tab analysis-ready. A useful public dataset usually has variables in columns and observations in rows, the structure described in the research literature on tidy data. If years are spread across dozens of columns, you may need to unpivot them into fields such as Region, Year and Value rather than merely swapping rows and columns.

Once the structure is correct, choose the output format based on what must survive the handoff: CSV vs XLSX. CSV is suitable for one flat table; XLSX can retain multiple sheets, formulas and formatting.