Saturday, 20 October 2012 10:46

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

Written by
Rate this item
(0 votes)

transposing data in excel - re-arrange data

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 refercing 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. Click here to view in YouTube, if you can't view the video below:

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 excel - re-arrange data

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 excel - re-arrange data

Step 4:

Tick Transpose in the Paste Special window

transposing data in excel - re-arrange data

After Transposing / Re-arranging Data from Columns to Rows

transposing data in excel - re-arrange data

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? Why don't you tell your friends by sharing it on Facebook, Google+ or Twitter. You can also follow me on Twitter or Google+ or Like me on my Facebook 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.


Read 7152 times Last modified on Monday, 26 November 2012 15:19
Wednesday the 20th. © 2012. Reproduction without permission is not permitted. All rights reserved.
Copyright 2012


Joomla template poker