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

Asked By Shruthi 140 points N/A Posted on - 04/15/2011

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.

 

• Status: Open
• Question Views: 1532
• Answer Count: 3
• Vote Up 14 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Sharath Reddy

Go To Solution

Best Answer

Best Answer

Answered By Sharath Reddy 590495 points N/A #85516

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

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.

About Sharath Reddy

Questions
1

Answers
14599

Best Answers
2290

• Vote Up 0 Vote Down

• Posted on - 04/15/2011
• Question Category: MS Excel

Answered By Shruthi 140 points N/A #85518

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

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

 

About Shruthi

Questions
1

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/15/2011
• Question Category: MS Excel

Answered By sardar83 10 points N/A #85519

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

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

About sardar83

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 08/04/2011
• Question Category: MS Excel