How to copy a value or a formula over a very large range

If we need to copy a value or a formula over a small-ish range, we could do it in a couple of ways (that I know of):

1. Enter something in A2, then drag-fill as far down as desired.
2. Highlight the desired range of cells (say, A2:A20). Type something and hit Control+Enter, while the cursor is still blinking in the cell.

What if the entry needs to be copied over to thousands of rows? Either of those ways will require dragging the mouse pointer up and down with the left button pressed.

A tip shared by Joe Rubin almost a decade ago comes to the rescue.

Let us say that an entry (value or formula) needs to be copied over the range A2:A9999.

In A2, make that entry, hit Enter, then Copy that cell. Now you should see scurrying ants surrounding A2.

At this time, navigate over to the Name Box (which is a box to the left of the Formula Box).

Enter A9999 in that box. Hit Shift+Enter. Now, the entire range A2:A9999 should be selected. Just hit Enter.

You will see your entry being populated over A2:A9999, without the hassle of having to drag-select or drag-fill around the worksheet.

I like it!

Just hope I can remember it.