The fastest way to turn rows into columns

To swap rows and columns in Excel, use the Paste Special feature with the Transpose option. Select your data, copy it, right-click where you want it to go, choose Paste Special, check the Transpose box, and click OK. This flips your layout when ready without retyping anything.

The Transpose function works on any rectangular block of data — a single row, a single column, or a full table. It reverses the orientation: whatever ran left to right now runs top to bottom, and whatever ran top to bottom now runs left to right.

Key Takeaways

  • Paste Special with Transpose is the built-in Excel tool for flipping rows to columns; it takes about 10 seconds once you know where to find it.
  • You must copy the data first — Transpose only works when pasting, not on data already in place.
  • The transposed data lands in a new location; the original stays unchanged unless you delete it afterward.
  • Transpose preserves formulas, formatting, and merged cells, though merged cells may behave unexpectedly in the new layout.

Step-by-step: Using Paste Special to Transpose

Start by selecting the data you want to flip. Click the first cell, then drag to the last cell that contains data, or click the first cell and Shift+click the last one. The selection can be a single row, a single column, or a block of rows and columns together.

Copy the selection by pressing Ctrl+C (Windows) or Command+C (Mac). You will see a moving dotted border around the selected cells — that tells you the data is copied and ready to paste.

Click the cell where you want the transposed data to start. This is usually a blank area to the right or below your original data, because the transposed version will be a different shape and may overlap if you paste it on top of the original. Right-click and select Paste Special from the menu. On Mac, you may need to use the Edit menu instead.

In the Paste Special dialog box, find the Transpose checkbox — it is usually near the bottom left. Check it, then click OK. Excel will paste your data with rows and columns flipped. The original data remains in place; delete it if you no longer need it.

What happens to formulas and formatting

Transpose preserves formulas, but the cell references inside them shift to match the new layout. If a formula in your original data refers to cell B3, the transposed formula will adjust to point to the correct cell in the new arrangement. This usually works as expected, but check a few cells if your data contains complex calculations.

Formatting — colors, fonts, borders, number formats — also moves with the data. A cell that was bold and blue in the original will be bold and blue in the transposed version, just in a different position.

Merged cells can cause problems. If your original data has merged cells, the transposed version may have unexpected blank spaces or alignment issues. If this happens, unmerge the cells before transposing, or manually fix the merged cells in the transposed data afterward.

Transposing a single row or column

Transpose works just as well on a single row or single column as it does on a full table. A row of 10 cells becomes a column of 10 cells stacked vertically. A column of 10 cells becomes a row of 10 cells spread horizontally.

This is useful when you have data entered horizontally but need it vertical for a chart or report, or vice versa. The process is identical: select, copy, Paste Special, Transpose, OK.

When to use Transpose instead of rearranging manually

For small datasets — a handful of cells — it may be faster to retype. For anything larger, Transpose saves time and eliminates typing errors. If your data has formulas or complex formatting, Transpose preserves all of it automatically.

Transpose also works on data that is already in a table or range with a name. The transposed result will be plain data, not a named range, so if you need to reference it later, you may want to name the new range yourself.

Transposing data from another sheet or workbook

You can transpose data that lives on a different sheet or in a different workbook. Copy the data from wherever it is, switch to the sheet or workbook where you want the transposed version, and follow the Paste Special steps as usual. Excel handles the cross-sheet or cross-workbook copy without any extra steps.

If you are copying from a closed workbook, open it first, copy the data, then switch back to your working file and paste. Transposing data from a closed file is not possible directly.

Frequently Asked Questions

Can I undo Transpose if I change my mind?

Yes. Press Ctrl+Z (Windows) or Command+Z (Mac) when ready after pasting to undo the Transpose. If you have done other work since then, undo will reverse all changes back to that point, so undo Transpose only if it is your last action.

What if my data has blank cells or empty rows?

Transpose includes blank cells and empty rows in the transposed layout. A blank cell in row 3 becomes a blank cell in column 3. This usually works fine, but if you have intentional gaps in your data, check the result to make sure the layout looks right.

Does Transpose work on filtered or hidden data?

Transpose includes hidden rows and columns in the transposed data. If you have filtered your data to show only certain rows, copy only the visible cells you want, and Transpose will work on just those. To exclude hidden data, unhide it first, select only the rows you need, then copy and transpose.

Can I transpose data without using Paste Special?

Paste Special is the standard built-in method. Some third-party add-ins offer transpose functions, but they are not necessary — Paste Special is free and built into every version of Excel.

What if the transposed data does not fit in the available space?

Excel will paste as much as it can and stop if it runs out of room. If your data is very large and you are pasting near the edge of the sheet, move to a location with more space and try again. You can also insert new rows or columns to make room before pasting.