Sometimes you need to transpose your Excel data to a different set of cells in MS Excel. In such cases, you can transpose a copy of the data or use a function.
2 Methods of Transposing Data in MS Excel:
- Using the transpose formula
- Using paste special
Method 1: Using the transpose formula
The Transpose Formula is a useful method if you want to reflect changes made in the original data to the transposed data as well.
Step 1: Choose a destination cell
Select a blank cell where you want your transposed data to go.
In our example above the destination cell is A12.
Step 2: Enter the formula
Enter the formula =TRANSPOSE(range) in the destination cell to transpose the data.
Step 3: Select the cells containing data
Use your cursor and select the range of cells constituting the data you want to transpose to the new location, including the headings.
In our example above, the range of cells to transpose is A1:B8.
Step 4: Hit Enter
Once you have selected the range, hit the Enter button.
As soon as you do it, your transposed data will show up at the new destination as well as at the old one. This new data will show up horizontally.
Method 2: Using Paste Special
If you use Paste special, the changes you make to the original data will not affect the transposed one.
Step 1: Copy the dataset
Select the range of cells constituting the data you wish to transpose, including the headings. Copy the data by right-clicking with the mouse or by using the keyboard shortcut Ctrl+C.
Step 2: Choose the Transpose icon
Right-click on the destination cell and choose the Transpose option. This will transpose your copied data to the set of new cells.
Transposing data in MS Excel is extremely quick and simple. You can transpose data to another sheet as well and decide whether you want the transposed data to be a static copy of the original data or not.