rows to columns conversion (or 90 degrees data turn)

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

Hi all,
I'm looking for an easy way to manipulate my data in Excel

I've got a big spreadsheet with data organized in columns (easy to
fill in manually as input used earlier is suggested to the user), what
I need though is the same organized in rows.

of course I can transfer data to a different sheet with a row of
=Sheet1!A1 =Sheet1!A2 =Sheet1!A3 =Sheet1!A4... etc

but that seems not the most productive way to do it

TIA
Chris
 
Hi Chris

Select your data in the columns
Ctrl c to copy

Go to a new sheet
Right click on A1
Choose paste special
Check Transpose
OK
 
Note: 97-2003 have 256 columns
Excel 2007 16000+
Click to expand...

It's all in 2007 and transposition is yet another "manual" solution I
grew tired with. Would be happy to have something more "automatic"...
and within Excel... and I don't want to even start thinking about
writing a macro... oh well that's just me

chris
 
Record a macro when you do it manual and look at the code
Post back if you need help with more info

Range of data
Where do you want to copy to ? New sheet or existing sheet
 
chris said:
Hi all,
I'm looking for an easy way to manipulate my data in Excel

I've got a big spreadsheet with data organized in columns (easy to
fill in manually as input used earlier is suggested to the user), what
I need though is the same organized in rows.

of course I can transfer data to a different sheet with a row of
=Sheet1!A1 =Sheet1!A2 =Sheet1!A3 =Sheet1!A4... etc

but that seems not the most productive way to do it

TIA
Chris
Click to expand...

The Copy and then Paste Special Transpose method is perfect if you want to
manipulate your data once.

If you want to have updates in the columns carried over to your rows then
you can use the Transpose function.

Say you have values in A1 to A10, then select cells C1 to L1 (10 cells to
receive 10 values), type the formula:

=TRANSPOSE($A$1:$A$10)

and then press CTRL + SHIFT + Enter (enters the input as an array).

Now when you update a value in the column, the corresponding data point in
the row will be updated.

Thomas
 
Here's a non-volatile formula that can be entered anywhere and copied across
as needed:

=INDEX(Sheet1!$A:$A,COLUMNS($A:A))

This is configured to start at Row 1, and go down as you copy across.

If you want to start at Row 2, use:
COLUMNS($A:B)

Row 5:
COLUMNS($A:E)

.... etc.
 
Back
Top