Help me understand Transpose

[S Davis]

Hello all,

I don't seem to understand Transpose.

I have a set of data 10 columns in length, cells A1 - J1. All I want
to do is have a formula take this line and transpose it downwards.

I have tried:

=transpose($A$1:$J$1)
=transpose(A1:J1)
{=transpose($A$1:$J$1)}

.....

It simply will not transpose my data. Do I have this set up correctly?
Do I need my data on the right side of my formula?

I should note that copy -> past special -> transpose works fine, but
this needs to be automated.

Thanks
-SD

 

[Dave Peterson]

Did you hit ctrl-shift-enter when you entered the =transpose() array formula?

If you did, what happened so that it looked like it didn't work.

 

[Dave Peterson]

ps. Those {}'s are inserted by excel when you use ctrl-shift-enter. Don't type
them yourself.

 

[Guest]

TRANSPOSE is an array function. For array formulas,you need to hold down
[Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].

With A1:J1 as your source range

Select cells A2:A11
Type this formula in cell A2: =TRANSPOSE($A$1:$J$1)
Hold down [Ctrl]+[Shift] and press [Enter]

Note: Excel will put the braces "{ }" around the formulas and transpose the
source range.

Post back with more questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Hi Davis,

the edit paste special>transpose command can logically copy values and
formats...

while the transpose function links the transposed data from its source...
for ten cells in a column...
select 10 cells in a row
then
type either in the first or last cell =transpose(A1:J1
then press control-shft-enter
the { } braces will appear and see if the data were correctly transpose in
order.

regards

 

[Guest]

Do the following:

1) Select a column of 10 cells, or whatever column length is equal to the
number of cells in your horizontal range.
2) Enter in the first (top) cell the following formula: =TRANSPOSE(A1:J1)
DO NOT HIT ENTER. Make sure the formula bar is still active.
3) Hold down the CTRL+SHIFT+ENTER keys and release

Your data will be transposed into the column you selected. You will notice
that Excel automatically inserts the { } marks when CTRL SHIFT ENTER is hit.
Manually typing these symbols does NOT do what you want.

Dave

 

[S Davis]

Do the following:

1) Select a column of 10 cells, or whatever column length is equal to the
number of cells in your horizontal range.
2) Enter in the first (top) cell the following formula: =TRANSPOSE(A1:J1)
DO NOT HIT ENTER. Make sure the formula bar is still active.
3) Hold down the CTRL+SHIFT+ENTER keys and release

Your data will be transposed into the column you selected. You will notice
that Excel automatically inserts the { } marks when CTRL SHIFT ENTER is hit.
Manually typing these symbols does NOT do what you want.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.










- Show quoted text -

Thanks one and all for the kind responses

FYI, I know about arrays - my typing the {'s were just to show that I
had tried using an array formula.

My mistake was using an array formula without first highlighting the
target range (A2:A11) - my method, as I have done with other array
formulas [ie. {=sum((range)*(range)*1)} ] I would normally drag down,
but for this formula I see that I must first select my target area.
This seems rather cumbersome to me, as I essentially have to count out
the number of cells I want transposed. But I can live with it - by
highlighting my target range and array-committing it (ctrl-shift) the
formula works.

Again, thanks