rows to columns conversion (or 90 degrees data turn)

[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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

Note: 97-2003 have 256 columns
Excel 2007 16000+

 

[chris]

Note: 97-2003 have 256 columns
Excel 2007 16000+

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

 

[Ron de Bruin]

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

 

[Thomas Hardy]

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

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

 

[RagDyer]

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.

 

[chris]

Thanks all - I think I'll be using index or transpose functions

Chris

 

[RagDyeR]

Appreciate the fed-back.