If you have a worksheet with data in columns that you want to rotate so it’s rearranged in rows, you can use the Transpose feature. It lets you rotate the data from columns to rows, or vice versa.
For example, if your data looks like this, with sales regions listed along the top and quarters along the left side:
You can rotate the columns and rows to show quarters along the top and regions along the side, like this:
Here’s how:
-
Select the range of data you want to rearrange, including any row or column labels, and either select Copy
Note: Make sure you copy the data to do this. Using the Cut command or CONTROL+X won’t work.
-
Select the first cell where you want to paste the data, and on the Home tab, click the arrow next to Paste, and then click Transpose.
Pick a spot in the worksheet that has enough room to paste your data. The data you copied will overwrite any data that’s already there.
-
After rotating the data successfully, you can delete the original data.
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 and absolute references before you rotate the data.
-
If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first by using the Convert to Range button on the Table tab, or you can use the TRANSPOSE function to rotate the rows and columns.
-
If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so 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.
