Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

There are answers out there to this question, but all of them have been under explianed so hence to difficult to coprehend and use them to my advantage.

I want to do the seemingly simple (but not) task of Draging a Formula (Filling a series) across Column's while increasing the formula row number relativley.

For Example to drag this formula:

| =A1 | =A2 | =A3

Some other notes, Transposing by copy paste has proven too difficult for the amount of data.

Offset and Indirect has been used by other people to do this but I don't get how they work at all so when I attempt to use them I don't know how to format it to my range.

Here's a example photo

Idealy we want the dragged section to continue on to fill the formula. Column to Row 2

share|improve this question
I don't think this is possible by dragging. Transposing usually works quite well in my experience. Can you expand upon the problems you have with the size of your data with the transpose method? – Mike Fitzpatrick Jan 11 '11 at 9:54
my data is about 400 down and 400 across and what you see here i sa very basic question so I can incorporate an if statement the formula but you need not worry about that. I know the transpose method may seem the easiest but... it's not very good with changing the formulas once you've transposed it. – B-Ballerl Jan 13 '11 at 7:50
up vote 1 down vote accepted

Offset is the simpliest solution.


Step by step:

  1. Choose B2 and write


  2. Hit left arrow and then F4

    $A$1 It's like an ancor for the formula.

  3. Write

    ,column()-1,) The second argument is number of rows we want to offset from ancor. Third is number of columns. We'll leave it empty.

  4. Fill all cells you need

    Sweet part is formula must be the same in all cells. So you can drag, copy and move it without fear.

share|improve this answer
Works Like a Charm! Hope it can help others aswell who have had the same problem. – B-Ballerl Jan 13 '11 at 7:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .