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.

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 EditCopy
Hit function key F5
Enter A2:A40000
Click OK
With that range now selected....
Goto EditPaste

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

Biff

"busman" wrote in message
...
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.

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

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


On Thu, 19 Jan 2006 20:29:02 -0800, busman
wrote:

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.