How Do I reverse axis in a spreadsheet

[ChrisH]

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?
--
Thank you,
Chris H

[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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ChrisH" <[email protected]> wrote in message
news:[email protected]...
> 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?
> --
> Thank you,
> Chris H

[Biff]

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

<vbg>

Cheers!

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "ChrisH" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>> --
>> Thank you,
>> Chris H
>
>

[Max]

"Biff" wrote:
> 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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----