AutoFill Large Number of Cells in Excel?

Question

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?

Answer 1

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.

Answer 2

The problem: User has a formula in a cell and wishes to copy it to the next 999,999 rows. Simple techniques like copy'n'paste are too primitive because they don't go fast enough, and 999,999 is a large number.

Here is one solution:

1. Type 1 into a cell at the top of the worksheet, let's say Cell A1 Result: 1 appears in Cell A1
2. Type +1+A1 into cell A2 Result: 2 appears in Cell A2
3. With the pointer at Cell A2, click -C to copy
4. Move the pointer to Cell A3
5. Hold down Ctrl and Shift and click , then click Down Arrow Result: All of Column A is highlighted, from A3 to A1048576
6. Hold down Ctrl and click V to paste Result: Column A now contains integers 1 to 1048576
7. Hold down Ctrl and click F to find
8. In the Find What box, enter 1000000 and press Enter Result: Cell A1000001 is highlighted Now your pointer is in Cell A1000001
9. Hold down Ctrl and Shift, then click End Result: Everything down to the bottom is highlighted
10. Click Delete Result: Only Rows 1 through 1000000 remain, showing values 1 to 1000000
11. Hold down Ctrl and click Home to return to Cell A1
12. In Cell A1, enter your formula, for example +"B"&ROW()
13. Click Ctrl and C to copy
14. Hold down Ctrl and Shift, then click End and Down
15. Hold down Ctrl and click V to paste Result: Column A now contains the text B1 through B1000000 but no further
16. Row 1000000 is now identified because it is the bottom-most non-blank cell
17. You may place text into other cells in Row 1000000 to identify bottoms

Answer 3

My solution is inspired from Jay Jacob's answer, and I do believe that following is the easiest way to do it.

1. Put the value in Formula cell A1 and copy it.
2. Use the Name Box (left to the Formula Bar), type in the last cell you need. [for example A999999 for 999999 rows in column A or C999999 for selecting 999999 rows across 3 columns]
3. Once the pointer is on this cell, hold Ctrl+Shift and tap the Left and Up keys or the Ctrl+Shift+Home bottons to select all the cells from the last desired cell to the first cell needed to be filled.
4. Finally Paste it.

Answer 4

Double clicking the auto-fill handle (small rectangle at bottom left of selected cells) will auto-fill the column, until it detects a blank cell to its left.

See for example: http://www.techrepublic.com/blog/microsoft-office/a-quick-fill-handle-trick-for-microsoft-excel/