MS-Excel: How to alter data in a row to column?

Asked By 140 points N/A Posted on -
qa-featured

Simple excel question. How can i convert a row of data into column

I have an excel in Row 1 i have 100+ cells of data.

I want to get them into a Column

Can anyone help me with a script or a inbuilt way to achieve this.

 

SHARE
Best Answer by Sharath Reddy
Best Answer
Best Answer
Answered By 590495 points N/A #85516

MS-Excel: How to alter data in a row to column?

qa-featured

Select the data in the row and press CTRL+C 

Open a new worksheet or a new sheet in same workbook and select the top left cell (A1).
Choose the menu command Edit Paste Special
 
Click Transpose, and click OK.
 
We can convert data in Rows to a Column.
 
There are different ways to achieve this but i have given you the simplest way.
 
Hope this helps.
Answered By 140 points N/A #85518

MS-Excel: How to alter data in a row to column?

qa-featured

Thanks for the quick solution it was helpful and achieved what I wanted.

 

Answered By 10 points N/A #85519

MS-Excel: How to alter data in a row to column?

qa-featured

The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout).

This is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format.

Example

In a new worksheet, type the following data:

A1: Smith, John

A2: 111 Pine St.

A3: San Diego, CA

A4: (555) 128-549

A5: Jones, Sue

A6: 222 Oak Ln.

A7: New York,

A8: (555) 238-1845

A9: Anderson, Tom

A10: 333 Cherry Ave.

A11: Chicago, IL

A12: (555) 581-4914
 
  1. Type the following formula in cell C1:

    =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
     
  2. Fill this formula across to column F, and then down to row 3.
     
  3. Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through F3 as follows:
Smith, JoHN 111 Pine St.San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914

The formula can be interpreted as: 

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

Where:

  • f_row = row number of this offset formula
  • f_col = column number of this offset formula
  • rows_in_set = number of rows that make one record of data
  • col_in_set = number of columns of data

Related Questions