How to transpose in MS Excel

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.


Conclusion

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.

Leave a Comment