Skip to content

How to Switch Data From Columns to Rows or Rows to Column in Excel Quickly by Using Transpose Data

transposing data in ms excel

In my previous post, we looked at how to create conditional formatting in order to highlight or make some cells standout from other cells based on it’s values. Today, I am going to show you how to re-arrange data in Excel from columns to rows and rows to columns easily by using transpose data feature.

What Does Transposing Data Mean?

Transposing data in Excel means re-arranging data entered in columns (vertical range) to rows (horizontal range) or rows to column because you may want to re-layout your spreadsheet. Many time what most of us tend to do is to start copying each data one by one from columns to rows if you want to re-arrange or vice versa. Do you know that Microsoft Excel can do this so quickly, like in 2/3 clicks? That’s what I am going to show you in this post.

How to Transpose Data from Column to Rows and Vice versa?

If you have formulas in your spreadsheet, you may want to consider using Absolute referencing (e.g. $B$10 instead of B10, if possible). Excel automatically adjust all the referencing in your formulas when you transpose, however, it is a fool-proof method if you can use absolute referencing. If you can’t, ensure all your formulas are referring to the correct cells after transposing data.

To transpose / re-arrange data:

Here’s a video tutorial showing you how to re-arrange data in Excel:

Step 1:

Select the range of cells you want to re-arrange. In this example, I want to re-arrange the data in column to rows and Click Copy

transposing data in ms excel

Step 2:

Select the cell where you want the re-arranged data to appear

Step 3:

Click on the small arrow under Paste button | Click Paste Special

transposing data in ms excel

Step 4:

Tick Transpose in the Paste Special window

transposing data in ms excel

After Transposing / Re-arranging Data from Columns to Rows

transposing data in ms excel

You can select other options in Paste Special window, for e.g. if you want to paste only the values, select values.

Do you find this information useful? Share it with your friends by on Facebook, Google+, Twitter or other Social Media. You can also follow me on Twitter @sarayoo.info or Google+ or Like me on my Facebook or on my LinkedIn forĀ  more updates, technology tips and tricks, iPhone, iPad, other iOS devices tips, iOS App Deals, Blogging tips, etc. Please leave your comments in the comment section or contact me if you have any other questions.