RSS

Transpose in Excel

30 Jun

Sometimes it happens that we enter tabular data in some arrangement and then realize that it should not be in this arrangement.  We may want to switch the columns to row or row to columns.  Now there is no need to retype or manually cutting and pasting each and every cell.  Excel has a built in option, Transpose, for that.  Switching data from ‘Rows to Columns’ or ‘Columns to Rows’ is called Transposing.

Transpose1

In the above figure Zones are in column and Months are in Rows.  Now you want to arrange Months in Columns and Zones in Rows.

Follow the below steps to accomplish the same:

Select the source data and copy.  You may use Ctrl-C for copying or click on Home tab –> Clipboard group –> Copy button.

Transpose2

Select target cell by clicking on it.  Click on down arrow below Home tab –> Clipboard group –> Paste button and select Transpose from available Paste options.

Transpose3

Transpose4

Points to note:

  1. Do not use Cut command.
  2. Copy and Paste areas cannot overlap.  Make sure that you select a cell in a paste area that falls outside of the area from which you copied the data.
  3. If the cells that you transpose contain formulas, the formulas are transposed and cell references to data in transposed cells are automatically adjusted. To make sure that formulas continue to refer correctly to data in nontransposed cells, use absolute references in the formulas before you transpose them.
Advertisements
 
Leave a comment

Posted by on June 30, 2014 in MS Excel

 

Tags: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: