Copying Formulas in LARGE Files

B

busman

Is there any other way to copy a formula in a large column of data (>40000
rows) other than dragging the formula down. This can take up to five minutes
to drag down this many rows. I've been searching for a way by have had no
luck.
 
B

Biff

Hi!

Try this:

Assume you have a formula in A1 and want to copy it down to A40000.

Make sure your formula is using the correct reference style (relative,
absolute, mixed)

Select cell A1
Goto Edit>Copy
Hit function key F5
Enter A2:A40000
Click OK
With that range now selected....
Goto Edit>Paste

Note: The formulas still have to calculate. This just saves the trouble of
dragging for such a large range.

Biff
 
P

Pete

I frequently work with sheets of this size - here's a couple of quick
ways, assuming that there are no gaps in your data:

If your formula is intered in a column immediately to the right of
another column with data in, just double-click the fill handle (the
small square in the bottom right hand corner of the cursor). This will
copy all the way down.

Using mainly key-presses: move cursor left until you meet a column with
data in. Press <end> once followed by <down>, then move right until the
cursor is in the correct column. Type # (or any character to mark this
bottom cell) then <end> followed by <up>. Click <copy> (or CTRL-C),
then <down> to the cell below, then hold down <shift>, press <end> once
then <down>, release <shift> and press <enter>. Takes longer to
describe than to do.

Hope this helps.

Pete
 
G

Gord Dibben

If the formula column is adjacent to another column that extends down the >40000
rows just double-click on the fill handle of of the formula cell.

Alternative if not an adjacent column.................

Put your formula in say D1 then click on the namebox and type D1:D41000 and
<ENTER>

Now hit CTRL + D to copy to that range.


Gord Dibben MS Excel MVP
 
Top