Google ExcelAutomate.com: Switch (transpose) columns and rows in Excel

Switch (transpose) columns and rows in Excel

Hi Webies after some gap I'm here with some new articles, Most of the guys are already 
aware of this feature of transpose that is present in Microsoft Excel.

Transposing data is nothing but switching the columns and rows or vice versa.

I am writing this post, because one of my friend got some troubles copying 
and pasting (switching manually). This process will kill your time when you do it 
manually. But excel has built in functionality for  this transposing. 

So here we go...


If data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other.
For example, the regional sales data that is organized in columns appears in rows after transposing the data, as shown in the following graphics.
Data in columns
Regional data in rows
  1. On the worksheet, do the following:
    • To rearrange data from columns to rows, select the cells in the columns that contain the data.
    • To rearrange data from rows to columns, select the cells in the rows that contain the data.
  2. On the Home tab, in the Clipboard group, click Copy Button image.
    The Clipboard group on the Home tab
    Keyboard shortcut  To copy the selected data, you can also press CTRL+C.
    NOTE   You can only use the Copy command to rearrange the data. To complete this procedure successfully, do not use the Cut Button image command.
  3. On the worksheet, select the first cell of the destination rows or columns into which you want to rearrange the copied data.
    NOTE   Copy areas 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.
  4. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Transpose.
  5. After the data is transposed successfully, you can delete the data in the copy area.
TIP   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.




Reference: support.office.com

No comments:

Post a Comment