Instead of clicking and dragging the square in the lower right corner of the cell, you should try instead to double click it. This will autofill it down.
Also, you should get used to using "ctrl" and "shift". When I use excel I go extremely fast because I use lots of keyboard shortcuts. Let me walk you through an example and let you know exactly what I would type. Lets say you have data in Columns A:E with 20000 rows, and want to create a formula for F. Follow these steps:
- First put your formula in F1.
- Now hit ctrl+C to copy your formula.
- Hit left, so E1 is selected.
- Now hit Ctrl+Down. This will travel all the way down until a blank is hit, in this example its E20000.
- Now hit right so F20000 is selected.
- Now hit ctrl+shift+up. Now you will be selecting F1:F20000.
- Finally either hit ctrl+V or just hit enter to fill the cells.
Once you get used to doing things like this, these 6 keystrokes happen very fast in a fraction of a second.
i use the 6-keystroke way all the time :) – wilson – 2010-08-25T04:27:39.973
Can't you just do ctrl+c, ctrl-shift-down, ctrl-v? – Worse_Username – 2017-10-18T08:53:46.057
@Worse_Username Try it -- you'll end up at the very last row, not the bottom of the data. You can do it, but your filesize will shoot up. – Fund Monica's Lawsuit – 2018-02-02T18:10:24.247
Beautiful, learned how the fast people do it like..!! – Irfan – 2019-10-23T09:16:27.463