How do you transpose rows to columns?

[Guest]

I am trying to transpose a bunch of data with 50 variables going down a
column and 75 variables running across the top in a row. When I try it says
that my cells are of different sizes and so I can not transpose it all at
once. I have tried transpose 1 row at a time. At this time, I am asked to
update values for, I'm pretty sure, every value. It does not retain all and
some cells have REF#.
Does anyone have any suggestions?

 

[Peo Sjoblom]

You need to copy the range to either a new sheet or outside the range you
are copy, you cannot select let's say select A1:BX50 and copy it and then in
place do edit>paste special and select transpose. Just copy it to a new
sheet and paste special transpose

 

[Guest]

Another option: you can transpose on a second sheet like this.

Assume the upper left corner of your table is at B1 (so it is in column #2,
row #1 you need to remember that)

on a second sheet pick a location to be the upper left corner, can even be
same as in the original sheet, put a formula similar to this:
=OFFSET('Sheet1'!$B$1,Column()-2,Row()-1)
Notice the use of -2 and -1

You can now fill this formula across and down to get the values from the
original sheet. If you want you can now select the entire area and use Edit
| Copy followed immediately with Edit | Paste Special with the "Values"
options chosen to make the changes 'permanent', i.e. turn them into values
instead of formulas.

 

[David Biddulph]

Can he not just use the TRANSPOSE function instead of OFFSET?
=TRANSPOSE(Sheet1!A1:BX50) as an array formula?

 

[Guest]

Quite possibly he can! I actually didn't think about the TRANSPOSE()
function since it's not one in my 'standard' vocabulary. Sometimes I'm just
not the sharpest pencil in the box when it comes to worksheet functions: I'm
basically a coder at heart <g>. But that comes back to embarrass me often
enough when I write 40 lines of code to do what a single worksheet function
can do (think of coding the equivalent of one of the more complex
SUMPRODUCT() things we see so often in here).

 

[Peo Sjoblom]

There is a drawback using the TRANSPOSE function, you need to enter it as an
array in one fell swoop over 50x75 cells. Also IMHO it's better to use INDEX
as opposed to OFFSET when transposing since it is non volatile.


=INDEX(Sheet1!$A$1:$BX$50,COLUMNS($A:A),ROWS($1:1))


copied across and down

 

[Guest]

Well, I'm adding both your INDEX() solution along with David's TRANSPOSE()
solution to my list of handy-dandy ways to get things done. Nice to know the
difference in volatility between OFFSET() and INDEX() - always helps to make
decisions on what to pick at times. And with choices, you have options in
special cases where for some reason one or more of the choices won't work in
the unique scenario for whatever reason.