The fastest way to rotate your data

To change data from columns to rows in Excel, use the Paste Special feature with the Transpose option. Select the cells you want to move, copy them, right-click where you want the data to go, choose Paste Special, check the Transpose box, and click OK. Your columns become rows and your rows become columns in seconds.

This method works in Excel for Windows, Excel for Mac, and Excel Online. The data stays as values, formulas, or formatting—whatever it was before. If you need to keep the original data in its old layout, paste the transposed version in a different area of your sheet or on a new sheet.

Key Takeaways

  • Transpose moves your data 90 degrees: what was in a column now runs across a row, and what was in a row now runs down a column.
  • Copy your data first, then use Paste Special > Transpose to rotate it without deleting the original.
  • Formulas in transposed data will adjust their cell references automatically, so check the results if your data includes calculations.
  • You can transpose data that includes formatting, merged cells, and images, though merged cells may behave unexpectedly.

Step-by-step: Using Paste Special to transpose

Start by selecting the cells that contain the data you want to flip. Click on the first cell, then drag to the last cell, or click the first cell and hold Shift while clicking the last cell. Make sure you include all the data you want to move—headers, numbers, text, everything.

Copy the selection by pressing Ctrl+C (Windows) or Command+C (Mac). You will see a moving dashed border around the cells you copied, sometimes called the "marching ants."

Click on the cell where you want the transposed data to start. This is usually a blank area so the new data does not overwrite anything. Right-click and select Paste Special. In the Paste Special dialog box, find the Transpose checkbox (usually near the bottom), click it, and then click OK. Your data is now rotated.

What happens to formulas when you transpose

If your original data contains formulas, Excel adjusts the cell references automatically when you transpose. For example, if a cell in column A contains =B1+C1, and that cell ends up in row 1 after transposing, the formula becomes =B1+C1 adjusted to match its new position. This usually works correctly, but you should check a few cells to make sure the results are what you expect.

If the formula references are wrong after transposing, you may need to use absolute references (like $B$1) in your original formulas before you transpose. Absolute references do not change when you move or copy a formula, so they stay the same after transposing.

Transposing data with formatting and merged cells

Excel preserves most formatting when you transpose—colors, bold text, borders, and number formats all move with the data. However, merged cells can cause problems. If you have merged cells in your original data, Excel may not merge them correctly after transposing, or it may unmerge them entirely. Unmerge any cells before you transpose if you want to avoid surprises.

Images and shapes embedded in your data do not transpose. They stay in their original location. If you need images to move with your data, you will have to move them manually after transposing.

Using the TRANSPOSE function instead

If you want the transposed data to update automatically when the original data changes, use the TRANSPOSE function instead of Paste Special. Type =TRANSPOSE(A1:C10) in a cell, where A1:C10 is the range you want to flip. Press Ctrl+Shift+Enter (Windows) or Command+Shift+Enter (Mac) to enter it as an array formula. The transposed data will appear and will recalculate if you change the original.

The TRANSPOSE function works best when your data does not have merged cells or complex formatting. If you transpose a range that includes formulas, the formulas in the transposed result will reference the original cells, not recalculate based on the new layout. For straightforward data rotation with live updates, TRANSPOSE is fast; for complex sheets, Paste Special is usually safer.

Transposing large datasets

Excel can transpose thousands of rows and columns, but the process slows down with very large datasets. If you are transposing more than 10,000 rows, close other programs to free up memory, and do not try to undo the operation when ready after—let Excel finish first.

If your data is so large that Excel runs slowly, consider splitting it into smaller chunks, transposing each chunk separately, and then combining them. You can also use Power Query (in Excel for Windows) to transpose data more efficiently. Open the Data tab, select From Table/Range, load your data into Power Query, right-click the table header and choose Unpivot Columns, then load the result back to your sheet.

Undoing a transpose if something goes wrong

If the transposed data is not what you expected, press Ctrl+Z (Windows) or Command+Z (Mac) when ready to undo. Excel will restore your data to its original layout. You can undo multiple times if you need to go back several steps.

If you already closed the file or made other changes after transposing, you cannot undo. This is why it is a good idea to transpose on a new sheet or in a blank area first, check the result, and only then delete the original data if you are satisfied.

Frequently Asked Questions

Can I transpose data that has blank cells?

Yes. Blank cells transpose just like any other cell—they stay blank in their new position. If you have scattered blank cells in your data, they will be scattered in the same pattern after transposing, just rotated 90 degrees.

What if I transpose data and the columns are too narrow to show the numbers?

After transposing, your new columns may be narrower than your old rows were tall. Double-click the border between column headers to auto-fit the column width, or drag the border to make columns wider manually. This is a display issue, not a data problem.

Does transposing work on data from other programs pasted into Excel?

Yes. As long as the data is in cells in your Excel sheet, you can transpose it. If the data came from a web page, a PDF, or another program, paste it into Excel first, then use Paste Special > Transpose the same way.

Can I transpose only part of a table without moving the rest?

Yes. Select only the cells you want to transpose, copy them, and paste them transposed in a new location. The rest of your table stays where it is. You can then delete the original selection if you no longer need it.

Will transposing change the order of my data?

No. Transposing rotates the data 90 degrees but does not reorder it. The first column becomes the first row, the second column becomes the second row, and so on. The data inside each cell stays the same.