9

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?

20

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:

enter image description here

After the double click on the fill handle:

enter image description here

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.

  • 1
    I don't know about the OP, but personally I would find this answer even better if it said how I would know how far Excel would autofill in this scenario... – a CVn Jul 31 '15 at 23:51
  • @MichaelKjörling, good point. I added some more details about how far down Excel will typically fill. It depends a bit on how "established" the range is and whether there is data on either side of the column that you want to fill. – teylyn Aug 1 '15 at 2:18
  • This is amazing, the trick for me was to put the column to auto-fill adjacent to a populated column. If you skip a column (leave one blank between the data and the thing you're filling), this may not work. – Matt Aug 16 '17 at 15:37
4

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!

  • Another way to do this, if you have blank cells and can't reliably CTRL your way to the right spot, is to use CTRL-G, then type in the specific range you want e.g. "A1:A5000". Once you hit OK, you can use the steps above. In my experience this is used less than 1% of the time, but in some situations can be a godsend! – Jon Aug 4 '15 at 4:11
  • @Jon Nice suggestion; incorporated! – a CVn Aug 4 '15 at 8:39
  • my personal favorite way to skip around possibly blank cells in a long range is to CTRL+down in the empty column, then arrow over to a filled column and CTRL+up back up to the last (or near last) row with data. – Alex M Mar 8 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.