I’m trying to do a simple auto fill, but I guess it’s not so simple, to me anyway.
I want to auto fill a formula. I want the contents of cell A1 to be multiplied by 2 then 3 then 4, etc. So, I start with
A1*2
and try to autofill downward. I get
A22
A32
A4*2
when what I want is A1 to stay fixed while the multiplier is what changes, like this:
A11
A12
A1*3
How do I do this? Thanks!
OK, I found a website that talks about absolute reference, so I now have
$A$12
$A$13
$A$1*4
but when I autofill from there, it gives me repeats like this
$A$12
$A$13
$A$14
$A$12
$A$13
$A$14
$A$12
$A$13
$A$1*4
instead of increasing the multiplier from 2, 3, and 4, up to 5, 6, 7, etc.
First, to keep the cell reference fixed, use $A$1. As to getting autofill to increment the second half of the formula, I can’t get Excel to do that.
Unless someone else can, what you’ll need to do is use autofill to create the column of numbers you want to multiply $A$1 by, then in the top cell of the column next to that column, write a formula that multiplies $A$1 by the contents of the cell to the left of that column. Then you can copy that formula down.
ok, thanks!
Try this:
Column C: Fill it with your number increment (say, 1 to 100). You can do this by entering 1 in cell C1, and then highlighting the cells you wish to fill with numbers from 1 to 100 and then going to Edit > Fill > Series. Include C1 in your highlight because the fill needs to know what number you want to start off with.
Column D: =CONCATENATE("$A$1*",C1) in cell D1. Copy this down to the bottom in parallel with column C. The result should look like this:
A B C D
[blank] [blank] 1 $A$1*1
[blank] [blank] 2 $A$1*2
[blank] [blank] 3 $A$1*3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[blank] [blank] 100 $A$1*100
Now, copy the contents of column D over to column B by using Copy and then Paste Special > Values.
Highlight column B and bring up the Find/Replace dialog box. Enter “$A” in “Find” and “=$A” in replace.
Hope this works!
I have used the function ROW() for this kind of thing.