Excel : Increment Row number on fill right

Question

When one "Fills right in excel" , the Column name updates

eg.

1st cell is =A2

If I fill right the next cell will be =B2

But rather than the column incrementing I want to increment the row number

so 1st cell is =A2

The cell on its right is =A3

how do i do that?

Answer 1

You cannot do this using Microsoft Excel. I would recommend that you rewrite your own spreadsheet program if this is what you want to achieve.

Answer 2

You can do this using the INDIRECT function. Suppose we want formulas in cells B1, C1, D1... that should reference A2, A3, A4... Starting in cell B2 we can enter:

=INDIRECT("R[" & COLUMN(RC)-1 & "]C1", FALSE)

In cell B2, this will render as:

=INDIRECT("R[1]C1", FALSE)

In this situation, I'm using R1C1 format. The second parameter to the INDIRECT function tells the function to use R1C1 format. The function itself (again in cell B2), says "give me what is in the cell that is one row down and the first column". In the original formula, by basing the row value on the current column number, it will update the formula to a new row as you fill right.

Now, if you are asking if there is a way to do this using the GUI like say using the Fill box with the mouse, the answer is no. There is no means to transpose the fill using the GUI.

Answer 3

If you're putting the first formula in B2 then

=OFFSET($A$2,COLUMN()-2,0)

The -2 part is because we're starting in column B. You need to change it for whatever column you're actually starting in such that the second argument equal zero.