How can I transpose reference cells by Auto-fill?
 

Hi Folks,
for example, satrt from a reference =A1 then Auto-fill right (Ctrl+R), I can
get =B1, =C1...and so on, is there any easy way that I could got the same
result list in same column as:
=A1
=B1
=C1
....
but Auto-fill down (Ctrl+D) as;
=A1
=A2
=A3
....

What I'm doing now silly is to edit each cell =A$1, =B$1...copy...paste
special...transpose...

--
CKKwok

Answer: How can I transpose reference cells by Auto-fill?
 

1. Enter the reference cell in the first cell of the column where you want to transpose the data. For example, enter "=A1" in cell A2.
2. Select the cell with the reference formula (A2 in this example).
3. Hover your mouse over the bottom right corner of the selected cell until the cursor changes to a plus sign.
4. Click and drag the cursor down the column to the last cell where you want to transpose the data. Excel will automatically fill in the reference formulas for each cell in the column, incrementing the row number in the formula.
5. Release the mouse button to complete the Auto-fill.

Now you should have a column of cells with the reference formulas transposed from the original row to the column. You can copy and paste this column of formulas as values if you want to remove the references and keep only the values. To do this, select the entire column, right-click and choose "Copy", then right-click again and choose "Paste Special". In the Paste Special dialog box, choose "Values" and "Transpose", then click "OK". This will replace the formulas with their values and transpose them to the rows.

How can I transpose reference cells by Auto-fill?
 

Your example was not too much clear but lets suppose your row 1:1 consist the
data that you want to transpose in column A2 and below as:
A1
B1
C1

then hopefully this formula, in cell A2 shall help:

=+OFFSET($A$1,0,ROW()-2)

Copy down as long as you want
--

Best Regards,
FARAZ A. QURESHI


"CKK" wrote:

Hi Folks,
for example, satrt from a reference =A1 then Auto-fill right (Ctrl+R), I can
get =B1, =C1...and so on, is there any easy way that I could got the same
result list in same column as:
=A1
=B1
=C1
...
but Auto-fill down (Ctrl+D) as;
=A1
=A2
=A3
...

What I'm doing now silly is to edit each cell =A$1, =B$1...copy...paste
special...transpose...

--
CKKwok

How can I transpose reference cells by Auto-fill?
 

another way
presume yr auto-filled area (=A1,=B1...) is in A2, B2,...
select $A$2:$H$2 area, go to formula bar and insert the formula (this
is an array formula so CTRL+SHIFT+ENTER it)

=TRANSPOSE($A$2:$H$2)

How can I transpose reference cells by Auto-fill?
 

Thx! both answers is work
--
CKKwok


"Jarek Kujawa" wrote:

another way
presume yr auto-filled area (=A1,=B1...) is in A2, B2,...
select $A$2:$H$2 area, go to formula bar and insert the formula (this
is an array formula so CTRL+SHIFT+ENTER it)

=TRANSPOSE($A$2:$H$2)