transposing cols to rows

[Dave R.]

I am sure this is an easy one, but Excel tends to crash as I experiment.

I have rows like (cells A1:C3)

Apple 12343 Bicycle
Orange 23123 Pancake
Create 40593 Scissors

... and I would like to tranpose this to read like:

Apple
12343
Bicycle
Orange
23123
Pancake
Create
40593
Scissors

I can successfully transpose 3 columns (to 3 rows) at a time, but when I try
to copy the transpose formula down, since I copy it down 3 rows it skips
A2:C2 and goes to A4:C4. How can I set up the formula to be able to copy it
down and get all sequential rows from my original range?

 

[Frank Kabel]

Hi
try the following (assumptions that your target range starts in row 1)
=OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))
and copy down

 

[Dave R.]

Thanks Frank! I had done it using indirect after I posted, but will try your
formula.