Have you ever felt like switching the rows and columns of a data set on an Excel sheet? I am sure that all Excel users have come across such a requirement.
The question is how do you go about doing it? Do you put in manual effort to reshuffle the data or is there a trick available to do that? Yes, there is one and we are going to see that today. The feature is called transpose and is available as a special paste option. It is just a matter of clicks that can change the axis of data arrangement. Let us check it out.
Steps to Transpose Rows and Columns
The feature has been available since Office 2010. We will see how to do it on MS Excel 2013 edition. Here we go:-
Step 1: Open the sheet on which you have the data you wish to transpose.
Step 2: Select the cells containing the data with the help of the mouse. Shown below is the sample data that I am going to transpose.
Step 3: Copy the data by using Ctrl + C keys or via the right-click context menu.
Note: Do not use the Cut command. This feature will not work with Cut-Paste. It works only with Copy-Paste.
Step 4: Select the cells (select the top left cell of the desired paste location) where you wish to paste the transposed data.
Note: The paste area should not overlap the copy area. Thus, ensure that you select paste cells outside and beyond the portion you have just copied.
Step 5: Now, right click on the selection and choose the option reading Paste Special. You may take this option from the ribbon as well.
Step 6: The Paste Special window will show up. From the last section, check Transpose and hit on Ok. Your data will be placed at the new cells as shown in the image below.
Alternatively, you can skip Step 6 and choose the transpose option from the ribbon while you are on Step 5. Choose the icon shown below.
Step 7: At this moment you will have both, original and transposed set of data on your sheet. If you wish (which will be true in most cases) you can delete the original set and adjust the new set accordingly.
Note: During the process, if your cells have formulas, they are adjusted automatically to refer the transposed cells. So, you may want to adjust thing on that end as well. Check this link to learn more about formula references.
There have been so many instances when I have created a sheet and then realized that I would be better with the transposed arrangement. Well, before I started using Excel 2010 or 2013 I had to do that manually. And, I hope you can understand the effort required in doing that. Now, it is just a few clicks away. Love it? Tell us in the comments section.