The fastest way to convert a row into a column

To convert a row into a column in Excel, use the Paste Special feature with the Transpose option. Select the row you want to convert, copy it, right-click where you want the column to appear, choose Paste Special, check the Transpose box, and click OK. The data that ran left to right will now run top to bottom.

This works because Excel stores data in cells, and Transpose tells Excel to swap the row position with the column position for each piece of data. A single row becomes a single column. Multiple rows become multiple columns, and multiple columns become multiple rows — the entire grid rotates 90 degrees.

Key Takeaways

  • Copy the row first, then use Paste Special with Transpose checked to convert it to a column in a new location.
  • Transpose works on any rectangular selection — a single row, multiple rows, or an entire table.
  • The original row stays in place unless you delete it after pasting the transposed version.
  • If you need the column to update automatically when the row changes, use a formula instead of Paste Special.

Step-by-step: Converting a single row to a column

Start by clicking on the row number itself — for example, click the "3" on the left side if your data is in row 3. This selects the entire row. If your data only fills part of the row, you can instead click the first cell with data and drag to the last cell, or click the first cell and hold Shift while clicking the last cell.

Press Ctrl+C (or Cmd+C on Mac) to copy. Click on the cell where you want the column to start — this is usually in a different part of the spreadsheet so you can see both the original and the new version. Right-click that cell and select Paste Special. In the dialog box that appears, check the box labeled Transpose, then click OK.

Excel pastes the data vertically. The first item from the row becomes the first cell of the column, the second item becomes the second cell, and so on. The original row remains unchanged.

When to use a formula instead of Paste Special

Paste Special creates a snapshot — if you change the data in the original row later, the transposed column does not update. If you need the column to stay connected to the row, use the TRANSPOSE function instead.

Click the cell where you want the column to start. Type =TRANSPOSE(A1:E1), replacing A1:E1 with the actual range of your row. Press Ctrl+Shift+Enter (not just Enter) to make it an array formula. Excel will automatically fill down and create the column. Now if you change any value in the original row, the column updates when ready.

The trade-off: TRANSPOSE formulas take up more memory and can slow down very large spreadsheets. Paste Special is faster for one-time conversions. Use formulas when the row data changes regularly and you need the column to reflect those changes.

Converting multiple rows to multiple columns at once

Select all the rows you want to convert by clicking the first row number, holding Shift, and clicking the last row number. If your data does not fill entire rows, click the first cell of your data range, hold Shift, and click the last cell instead. Copy with Ctrl+C.

Click where you want the columns to start and use Paste Special with Transpose checked. All rows become columns in the same operation. The first row becomes the first column, the second row becomes the second column, and so on.

Transposing an entire table (rows and columns both)

If you have a table with both rows and columns — for example, months across the top and product names down the left side — Transpose flips the entire grid. Months move to the left side, product names move across the top, and all the numbers rotate with them.

Select the entire table by clicking the top-left cell and dragging to the bottom-right, or by clicking the top-left cell and holding Shift while clicking the bottom-right. Copy it, click where you want the transposed table to appear, and use Paste Special with Transpose. The table rotates 90 degrees.

Be careful: if your original table is in the way, the transposed version will overwrite it. Paste into an empty area first, then delete the original if you no longer need it.

Fixing common mistakes when transposing

If the transposed data appears in the wrong location, you selected the wrong destination cell. Click Undo (Ctrl+Z), click the correct starting cell, and Paste Special again.

If the transposed column or row is much narrower or narrower than you expected, Excel is hiding the width. Double-click the border between two column headers to auto-fit the width, or drag the border to make it wider manually. The data is there — it is just not visible.

If you used TRANSPOSE and the formula shows an error like #VALUE!, you may have forgotten to press Ctrl+Shift+Enter instead of just Enter. Click the cell, press F2 to edit, and press Ctrl+Shift+Enter to fix it.

When transposing does not work the way you expect

Transpose works with numbers, text, and dates. It does not work well with formulas that reference other cells — the formulas transpose too, and their references may break. If your row contains formulas, copy the row, Paste Special with Transpose, then use Paste Special again and choose Values Only to replace the broken formulas with their results.

Merged cells also cause problems. If your row has merged cells, unmerge them first (select the merged cell, go to the Home tab, click Merge and Center, and choose Unmerge Cells). Then transpose. After transposing, you can merge cells in the new column if you need to.

Frequently Asked Questions

Can I undo a transpose if I change my mind?

Yes. Press Ctrl+Z when ready after pasting to undo the transpose. If you have done other work since then, you can still undo — keep pressing Ctrl+Z to step backward through your recent actions until the transpose is gone.

Does transpose work in Google Sheets?

Yes. In Google Sheets, use the TRANSPOSE function the same way: type =TRANSPOSE(A1:E1) and press Enter. Google Sheets does not require Ctrl+Shift+Enter. There is no Paste Special Transpose option in Google Sheets, so the formula is the standard method.

What happens to formatting when I transpose?

Paste Special preserves formatting — colors, bold, italics, and number formats all move with the data. If you want only the values without formatting, use Paste Special and choose Values Only instead of Transpose, then manually rearrange the data.

Can I transpose data that has blank cells?

Yes. Blank cells transpose just like any other cell — they become blank cells in the new location. The structure stays the same; only the direction changes.

Is there a way to transpose without using Paste Special?

The TRANSPOSE formula is the main alternative and works in most spreadsheet programs. Some older versions of Excel have a Transpose button in the Paste menu instead of Paste Special. If you cannot find Paste Special, look for a Paste menu with a Transpose option listed separately.