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
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
Step 4:
Tick Transpose in the Paste Special window
After Transposing / Re-arranging Data from Columns to Rows
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.