TRANSPOSE()

[Guest]

Friends,

I know how to copy / paste special / transpose, but I cannot get the
TRANSPOSE() worksheet function to work. Let's say I have 1,2,3 in the
horizontal array A1:C1. What, exactly, do I do to transpose the 1,2,3 into a
vertical array in (A1:A5), using only the TRANSPOSE() worksheet function.

I keep getting the #Value# error, so I must be missing something.

Thanks for your help ...

Bill Morgan

 

[Paul Corrado]

Highlight A3:A5

Enter formula

=Transpose(A1:C1)

It is an array function so enter while holding down CTRL+SHIFT+ENTER

 

[Myrna Larson]

Paul has given you the steps, but there are 2 additional points.

The range containing the transposed data cannot overlap the source. You have
data in A1. You can't put a the TRANSPOSE formula there without overwriting
the 1st data point.

Normally the two ranges should be the same size. Yours aren't. The source is 3
cells, the destination is 5 cells. If you put the formula in A1:A6, you'll see
#NA in the 2 extra cells. If the destination range is smaller, you won't see
all of the data.

You could, however, put this formula in A2:A3 =TRANSPOSE(B1:C1) which will
leave the data in A1 alone and put the data from the other two cells below it.

Remember, as Paul said, this is an array formula.

 

[Guest]

Paul,

Thank you for that concise, logical instruction. It worked perfectly.

 

[Guest]

Myrna,

Thanks for the clarification. I meant to say "transpose to A3:A5", so that
was a glitch on my part. Between your answer and Paul's I'm now clear on
TRANSPOSE(). Thanks again ...