How to Transpose Data in Excel: Convert Rows to Columns With Paste Special

You may have data in a row that needs to be rearranged into a column, or vice versa. Manually copying and pasting each cell is slow and error-prone. Excel’s Paste Special feature includes a Transpose option that instantly flips your data orientation. This article explains how to use Paste Special to convert rows to columns and columns to rows in a few clicks.

What the Transpose Feature Does

The Transpose option in Paste Special reorients your copied data. It turns a horizontal row of cells into a vertical column. It also turns a vertical column into a horizontal row. The feature works on any selected range, including multiple rows and columns. It creates a new, transposed copy of your data while leaving the original cells untouched.

You need to copy your source data first. The transposed data will be pasted into a new location. Ensure the destination area is empty to avoid overwriting existing information. The transposed data is static. It will not update if you change the original source cells.

Steps to Transpose Data Using Paste Special

Follow these steps to flip your data from rows to columns or columns to rows.

1. Select and copy your source data
   Click and drag to select the range of cells you want to transpose. Press Ctrl+C on your keyboard to copy the selection.
2. Select the top-left cell of the destination
   Click on the cell where you want the transposed data to begin. This cell will become the top-left corner of the new, flipped range. Make sure there is enough empty space below and to the right.
3. Open the Paste Special dialog
   Right-click on the destination cell. From the context menu, click Paste Special. Alternatively, you can go to the Home tab on the ribbon. Click the small arrow under the Paste button, then select Paste Special.
4. Enable the Transpose option
   In the Paste Special dialog box, locate the Transpose checkbox in the bottom-right corner. Click the checkbox to select it. Then, click the OK button.

Your data will instantly appear in the new location with rows and columns swapped. You can now delete the original data if it is no longer needed.

Using the Keyboard Shortcut

For faster access, use this keyboard sequence after copying your data.

1. Copy the data with Ctrl+C
   Select your range and press Ctrl+C.
2. Select the destination cell
   Click on the cell where the transposed data should start.
3. Press Alt+E, then S, then E
   This key sequence opens the Paste Special dialog and checks the Transpose box. Press Enter to confirm.

Common Mistakes and Limitations

Transpose Option is Grayed Out

If the Transpose checkbox is unavailable, you likely did not copy data first. The feature requires data to be on the clipboard. Select your source range and press Ctrl+C again. Also, ensure you are not trying to paste into a merged cell, as this can disable the option.

Data Does Not Update Automatically

Paste Special Transpose creates a static copy. If you change a number in the original source, the transposed copy will not change. To create a linked, updating transposed range, you must use the TRANSPOSE function instead of Paste Special.

Formula References Break After Transposing

If your original data contains formulas with cell references, those references will still point to the original cell locations after transposing. They will not adjust to refer to the new transposed positions. You may need to edit the formulas in the pasted range manually.

Paste Special Transpose vs. TRANSPOSE Function

Item	Paste Special Transpose	TRANSPOSE Function
Result Type	Static values	Dynamic array
Updates with source	No	Yes
Ease of use	Simple copy and paste	Requires array formula entry
Best for	One-time data rearrangement	Creating a live, linked transposed view
Excel version required	All versions	Excel 365 and Excel 2021 for dynamic arrays

You can now quickly reorganize tables and lists by flipping rows and columns. Use the Alt+E+S+E keyboard shortcut to transpose data without touching your mouse. For a dynamic solution that updates automatically, explore the TRANSPOSE function by typing =TRANSPOSE( into a formula bar.