Fill a large range with a formula in Excel, without mouse-dragging to extend

Question

I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.

The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
It is tedious/infeasible to drag 5000 cells down.

Is there any shorter way?

Answer 1

Using the mouse does not mean that you have to drag a formula down hundreds of pages. You can use a simple double click instead.

Enter the formula into C1, then double-click the Fill Handle (the black square at the lower right corner of the cell)

Before the double click:

After the double click on the fill handle:

Edit: How far will Excel fill down? It depends. If you have just entered the data in column B, a double click on the fill handle in C will fill down to the last row of data in column B. If there are blank cells in column B, the fill will stop at the first blank cell. If the data lives in an Excel Table, then Excel will fill down to the last row of the table, regardless of gaps in neighbouring columns.

Answer 2

You can use Ctrl and the arrow keys, with or without Shift held down, to navigate around the spreadsheet quickly. Ctrl+arrow keys jumps directly to the "last" cell in that direction which has content. Holding down Shift selects cells as you move around. Depending on how your spreadsheet is laid out, you may also be able to use Ctrl+Home (jumps to the top left corner of the spreadsheet) and Ctrl+End (jumps to the bottom right corner of the spreadsheet), again with or without Shift for selecting.

Since you mention A1 and B1, I assume that your range extends from the top of the spreadsheet and for 5,000 rows down.

The easiest way then would be to go to A1 (Ctrl+Home will do the trick), then press Ctrl+down, which will take you to A5000 or thereabouts. Move into the C column on the last row, then hold Shift and press Ctrl+up. You should end up at C1 with the range C1 through C5000 selected, with input focus remaining on C5000.

As an alternative to the above navigating and selecting, as suggested by Jon in a comment, you can press Ctrl+G and then enter a range like A1:A5000.

Now enter the formula as normal, for the row that is highlighted for input. In your case, that would likely be =A5000-B5000. When finished, press Ctrl+Enter rather than Enter to accept. Excel will autofill into all other cells in the selected range, adjusting cell references as necessary.

As a bonus mention, LibreOffice Calc has the same feature, but uses Alt+Enter rather than Ctrl+Enter (at least on Linux).

No mouse necessary!