Microsoft Excel offers many ways to manage data in the cells. One such trick is to Transpose Excel Cells (Column to Row and Reverse). This is a handy Microsoft Excel trick if you want to transpose or rotate or change a set of cells in Excel files.
Here are some scenarios where you can use transpose cells in Excel: If you want to transpose price quotes by vendor from column to row and vice versa. If your company lays out its employee directory by names in a row.
It can also be used for data with Sales Regions in the column headings and and Quarters along the left side. The Transpose feature will rearrange the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left.
How to Transpose or Rotate Excel Cells (Row to Column and Vice-Versa)
- Select the cells and copy them.
- Now select anywhere you want to transpose.
- Use ALT + E > S to open Paste Special window.
- Select Transpose.
Use Context Menu to Rotate Data from Rows to Columns or Vice Versa
Another way of doing this is using the coxtext menu.
- Select the range of data you want to transpose. You can also include including any row or column header. Then press Ctrl+C.
- Next, select a new location on the worksheet where you want the transpoised data. Make sure there is enough space and no other data on the same location as everything will be overwritten.
- Right-click over the top-left cell of where you want to paste the transposed table, then click on the Transpose icon.
Tips for Transposing Your Data
If the data includes formula, Excel will update the references in the same way as it does for others. If they are not absolute references, then you can switch between relative, absolute, and mixed references before you rotate.
If your analysis needs to view data from different perspective, you can instead create a PivotTable which offers better control to transpose.
I hope the post was easy to follow, and you have now learned a new trick. Transposing Columns and Rows are handy features because they help you work with data in different ways. For example, you might want to transpose your data in Excel to compare the data by columns (where each row represents one item) instead of by rows.
Related: Moving Quickly between parts of Excel sheets with Named Ranges
