If you have a worksheet with data in columns that you need to rotate to rearrange it in 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 and Quarters along the left side:
The Transpose feature rearranges the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left, like this:
Here’s how to do it:
Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C.
Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.
Choose a new location in the worksheet where you want to paste the transposed table, making sure there is plenty of room to paste your data. The new table that you paste there will entirely overwrite any data / formatting that’s already there.
Right-click over the top-left cell of where you want to paste the transposed table, and then choose Transpose .
After rotating the data successfully, you can delete the original table and the data in the new table remains intact.
Tips for transposing your data
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.
If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so that you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.
You can paste data as transposed data within your workbook. Transpose reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.
Here's how you can transpose cell content:
Copy the cell range.
Select the empty cells where you want to paste the transposed data.
On the Home tab, select the Paste icon, and select Paste Transpose.