In case you need to transpose a dataset in Excel, that is, you want to convert the columns into rows and vice versa, it is very hard to do that manually. Lucky for you, Microsoft Excel allows doing that by following just a few very simple and easy steps. You can do that in more than one ways by adopting whichever way seems to be the simplest.
If you want to transpose your data in Excel, you can do that by:
Using Power Query
Using Find and Replace and Paste Special
Using Paste special
We are discussing how to transpose your excel data using the feature of Paste Special, as this is the simplest and the most convenient way to do that. Paste Special is a powerful tool that may do a lot of good stuff for you. Transposing your excel data is one of those features.
Consider you have a dataset containing quarters in rows while regions in the columns. If for some reason you are willing to transpose your data, you can easily do that by making use of the Paste Special function. All you have to do is to follow these simple steps:
First of all, you need to select all the data that needs to be transposed.
In the second step, you need to copy all the data that you want to process. You can do that by pressing Ctrl + C.
While you are pasting the data, you need to take a look at the dialogue box of paste special. There will be an option of ‘Transpose’ at the end of the box. You need to click on it.
That’s it! Your data would now be transposed. In case you are willing to link the transposed cells to the original ones, you can easily do that by making use of ‘Find and replace’ along with the Paste Special.