How to apply a formula to the entire column in Excel other than draging it down?

Question

Hi, I am working with a mega huge excel worksheet with more than 40000 rows and I want to apply a formula to a column for all these rows. But the only way I know is to insert the formula in the first cell and then drag it down to the end of the list and you can imagine how long it takes. Does anyone have any ideas how to do it fast?

Answer 1

yep...copy your formula. Then go to a column that has figures all the way down all 40000 rows, press . This will shoot you all the way to the very bottom of that column. Now move back over to the column where your formula is. Hold down your key, press . This will shoot you all the way back up to your original formula, and because you held down the key, it will highlight all 40000 rows. Now press . Voila!



FYI...learn to use the (up/down/right/left) arrows. Once you've mastered how they work, you'll wonder how you ever got around without them.



If you're on a column/row with data, pressing (somedirection) will take you to next blank cell in that column/row. Essentially excel goes to the "end" of the row/column (or until it hits a blank cell, whichever comes first).

Play around with it and you'll figure out pretty quick how it works (you can also press End/Home to get back to A1 very quickly).



----more----

FYI..you don't have to hold down the End key while you press the direction. Just press End, then press your direction. When you press End, you'll notice that in the bottom, right part of your excel status bar, it'll show the word "END". If you didn't mean to press END, press it again to cancel it.

Answer 2

Simply type the column or row number

=SUM(A1:A40000)

Answer 3

Enter your formula in the first cell you wish to contain the formula.



Position your mouse cursor over the small black square in the bottom right corner of the cell until the cursor becoms a '+'.



Double click the '+'.



Excel will fill the formula down the column through the last row containing data.