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

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.

share|improve this question
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
up vote 4 down vote accepted

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]


share|improve this answer
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
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
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())
share|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.

share|improve this answer

You must log in to answer this question.

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