I have a formula in Excel that I would like to autofill to 999,999 other cells. How can I do this without dragging the autofill handle down the entire sheet?
Excel – AutoFill Large Number of Cells in Excel
microsoft excelworksheet-function
Related Solutions
Enter the formula into the first cell, and then click and drag the black square on the bottom right of the cell to cover all the cells you want to contain the formula. Excel is clever enough to update the cell references appropriately as it copies the formula.
Edit You want to add dollar symbols before both parts of the static cell:
LOG(<cell>, $B$6)
I was able to write a Excel Macro to accomplish this:
Sub AddEntry()
yr = "2012"
x = ActiveCell.Row
y = ActiveCell.Column
For mon = 6 To 12
Cells(x, y) = "01/" + CStr(mon) + "/" + yr
Cells(x + 1, y) = "15/" + CStr(mon) + "/" + yr
x = x + 2
Cells(x, y).Select
Next mon
End Sub
I actually used this to see to correct formatting, note the single quote. But this changes the entry from a date to a string.
Cells(x, y) = "'01/" + CStr(mon) + "/" + yr
Cells(x + 1, y) = "'15/" + CStr(mon) + "/" + yr
Best Answer
The key is to select all the cells from the one that has the formula all the way down and then fill down the formula. These steps do that quickly:
Select the cell with the formula, then while holding down the Shift key, press the End key and then press the down arrow; now release the Shift key. This will resize the selection to include the blank cells below the active cell; if all cells below are blank, it will resize the selection all the way to the end of the sheet.
Once the range is selected, press Ctrl-D to fill down the formula.