# Excel- dragging down formula without using transpose

I need help on using a formula so that when I use autofill and drag the cell in will link the formula vertically/horizontally like in the picture.

I don't wanna use transpose since i want the data link to the other table everytime I did some changes.

Something like this formula

``````=OFFSET(\$A\$3,,ROW()-ROW(\$A\$3))
``````

in `A4:A9`?

Edit

In this formula, `ROW(\$A\$3)` is the row number of the cell `A3`, that is, just a number `3`. Dollar prefixes in front of `A` and `3` means that this is an absolute reference, and it will stay the same when copied to another cell(s). Just `ROW()` means the row number of the cell, where the formula is, so it would mean `4` for the cell `A4`, `5` for the cell `A5` and so on. The combination `ROW()-ROW(\$A\$3)` is a standard way to have a consequent numbers in a column of cells, so in the example, for the cell `A4`, expression

``````ROW()-ROW(\$A\$3)
``````

means

``````4 - 3 = 1,
``````

for `A5` - `=2`, and so on.

The whole formula

``````=OFFSET(\$A\$3,,ROW()-ROW(\$A\$3))
``````

for every cell can be translated as follows: add `ROW()-ROW(\$A\$3)` numbers of columns to the column number, where `\$A\$3` is (which is 1, since `A` is the first column), and copy the content of the cell found to the cell, where the formula is.

For more details and examples on functions `OFFSET` and `ROW`, check out the `Excel` help section `Lookup & Reference` functions.

• yes it works, thanks a lot. btw is these a two formula and if you can explain how offset and row works? Commented May 21, 2017 at 23:45
• @Jeffrey JR Villegas: Not sure what do you mean by "a two formula", the formula is the same for all the cells in selection (`A4:A9` in the example). Commented May 22, 2017 at 0:07
• because i saw a "row" in the index formula, sorry if i confused you,,,im still learning how index formula works and how to use it in different situations. thanks Commented May 22, 2017 at 0:14