Microsoft Excel: Transpose (rotate) Data from Rows to Columns or Inversely

Microsoft Excel: Transpose (rotate) Data from Rows to Columns or Inversely

Microsoft Excel: Transpose (rotate) Data from Rows to Columns or Inversely


If you have a worksheet with data in columns that you need to rearrange into rows; use the Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa.
For example, if your data looks like this, with Sales Regions in the column headings and Quarters along the left side:




The Transpose feature will rearrange the table so that the Quarters are showing in the column headings and the Sales Regions can be seen as rows on the left, like this:




NOTE: If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first, or you can use the TRANSPOSE function to rotate the rows and columns.


Here is the Transpose process:

1. Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C to Copy (do not Ctrl+X or Cut –  it will not work). 




2. Choose a new location in the worksheet where you want to paste the transposed table, ensuring plenty of room to paste your data. The new table pasted will entirely overwrite any data or formatting already there.

Right-click over the top-left cell where you want to paste the transposed table, then choose the Transpose Icon.




3. Alternatively, you can right-click the location, click on Paste Special then select Transpose.




4. You will see both sets of data; the column and the row.




5. After rotating the data successfully, you can delete the original column; the data in the new row will remain intact.




Tips for Transposing Data

  1. If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative, absolute, and mixed references before you rotate the data.
  2. You are not limited to Data Ranges, you can grab an entire Row, copy it, paste it into a different cell, and select Transpose.
  3. You can also do the inverse; turn a row into a column.




    • Related Articles

    • Microsoft Excel: How to select rows and columns

      Microsoft Excel: How to select rows and columns In this article, we will guide you through various methods to select rows and columns in Excel, including some helpful shortcuts. Efficiency is the name of the game when it comes to Excel. Selecting ...

    • Microsoft Excel: Text to Columns

      Microsoft Excel: Text to Columns Data imported from other spreadsheets or databases is already separated into fields, using something called a field delimiter: a comma, tab, space, or custom character – to separate one field from another. These ...

    • Microsoft Excel: How to freeze Rows & Columns

      Microsoft Excel: How to freeze Rows & Columns Freeze the Top Row or First Column Click on the View tab on the Excel Ribbon. Click the drop-down for Freeze Panes. Click Freeze Top Row – allows scrolling down many rows and still shows the top row. OR ...

    • Microsoft Excel: How to select alternate columns

      Microsoft Excel: How to select alternate columns This article will guide you step-by-step through the process of selecting specific columns in Excel, such as every other or every nth column. Microsoft Excel, the go-to spreadsheet software for data ...

    • Microsoft Excel: How to print row and column headers on every page

      Microsoft Excel: How to print row and column headers on every page In printing spreadsheets; the top row is printed only on the first page. Here is how to repeat the header row on your print jobs. Repeat Excel header rows on every page Your Excel ...