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.
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.
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.
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
-
Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
-
Fill this formula across to column F, and then down to row 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
