How Do I reverse axis in a spreadsheet

[Guest]

I have a fairly large (6MB) and mature spreadsheet that I need to have the
columns and rows reversed? (ie. names in the left column and measurements in
the top row) Is there a global function or is this one large pivot table?

 

[Max]

If its a one time job, try a copy > paste special > transpose > ok over to a
new sheet.

If it's to be dynamic to the source, one way is via using TRANSPOSE()

Assume source range is in Sheet1, in A1:T5 (a 5R x 20C range)

In Sheet2,

Select A1:E20 (a 20R x 5C converse range)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:T5)
Array-enter the formula by pressing CTRL+SHIFT+ENTER

A1:E20 returns a dynamic transpose of what's in A1:T5 in Sheet1

For a neater look, we could suppress extraneous zeros display via: Tools >
Options > View tab > Uncheck "Zero values" > OK

 

[Biff]

I'm guessing that a 6MB file might have more than a 5R x 20C range.

<vbg>

Cheers!

Biff

If its a one time job, try a copy > paste special > transpose > ok over to
a
new sheet.

If it's to be dynamic to the source, one way is via using TRANSPOSE()

Assume source range is in Sheet1, in A1:T5 (a 5R x 20C range)

In Sheet2,

Select A1:E20 (a 20R x 5C converse range)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:T5)
Array-enter the formula by pressing CTRL+SHIFT+ENTER

A1:E20 returns a dynamic transpose of what's in A1:T5 in Sheet1

For a neater look, we could suppress extraneous zeros display via: Tools >
Options > View tab > Uncheck "Zero values" > OK

 

[Max]

I'm guessing that a 6MB file might have more
than a 5R x 20C range. <vbg>

Most probably! <g>

Purely for ease of set-up, especially for huge source ranges, I'd prefer
using the non-array OFFSET (rather than TRANSPOSE)

Assume source range is in Sheet1, in A1:IV50 (a 50R x 256C range)

In Sheet2, with A1 containing:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Just fill across & down (or down & across)
to cover the converse grid size (256R x 50C)