Say I have some values in rows like follows:

A1 - 5
A2 - 8
A3 - 3
A4 - 4

In row A5 I have a formula: "0,5 * A1". I want to autofill columns right so that they have formulas like so:

  A5   │   B5   │   C5   │   D5
       │        │        │
0.5*A1 │ 0.5*A2 | 0.5*A3 | 0.5*A4

So I want to increment rows as the columns increment.

  • 1
    Switch to R1C1 reference mode. You'll find it in Excel settings. After that you can use numbers for columns, so you can probably do what you want. – Peter Jenkins Nov 15 '12 at 20:49

You can use INDEX which has some advantages over the INDIRECT method. 1) it's not volatile 2.) you don't have to "hardcode" the column, so if you add a column at A the formula will still work.

For that use this fomula in A5 copied across [edited as per Daniel Cook's comment]


|improve this answer|||||
  • 2
    You're right. Index is better. Indirect was the first thing that came to mind. :) Though I think you mean =0.5*Index($A:$A,Columns($A5:A5)) if you want to copy the formula across. – Daniel Nov 15 '12 at 21:03
  • shoot yeah, need $s - edited - thanks Daniel – barry houdini Nov 15 '12 at 21:05
  • 2
    Right, but you still need to use $A:$A instead of A:A or when you "copy across" the first column passed to the index column will keep shifting as well. – Daniel Nov 15 '12 at 21:07
  • 1
    Ouch! Now I remember why I usually test these first....... – barry houdini Nov 15 '12 at 21:11

This will work for each cell you indicated:

=0.5*Indirect("A" & Column())
|improve this answer|||||
  • If this works, it's great, although pretty obscure. – Peter Jenkins Nov 15 '12 at 20:51
  • @PeterJenkins Indirect lets you provide the reference as a string. So Indirect("A" & Column()) returns the value of A1 within the first column, A2 within the 2nd column etc... – Daniel Nov 15 '12 at 20:54

A different approach, for the sake of that and admittedly less versatile than the other answers, would be to transpose and then either apply a formula or, as shown below, use a Paste Special Operation, such as Multiply (by 0,5). It can sometimes involve fewer key strokes:

SU506269 example

Note (i) the results (highlighted) are shown is Row8 in the image but would appear in Row5 and (ii) the results this way are values, not formulae.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.