software

Is there any other way to copy a formula in a large column of data (gt;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 Editgt;Copy
Hit function key F5
Enter A2:A40000
Click OK
With that range now selected....
Goto Editgt;Paste

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

Biff

quot;busmanquot; gt; wrote in message
...
gt; Is there any other way to copy a formula in a large column of data (gt;40000
gt; rows) other than dragging the formula down. This can take up to five
gt; minutes
gt; to drag down this many rows. I've been searching for a way by have had no
gt; 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 lt;endgt; once followed by lt;downgt;, then move right until the
cursor is in the correct column. Type # (or any character to mark this
bottom cell) then lt;endgt; followed by lt;upgt;. Click lt;copygt; (or CTRL-C),
then lt;downgt; to the cell below, then hold down lt;shiftgt;, press lt;endgt; once
then lt;downgt;, release lt;shiftgt; and press lt;entergt;. Takes longer to
describe than to do.

Hope this helps.

PeteIf the formula column is adjacent to another column that extends down the gt;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 D141000 and
lt;ENTERgt;

Now hit CTRL D to copy to that range.Gord Dibben MS Excel MVPOn Thu, 19 Jan 2006 20:29:02 -0800, busman gt;
wrote:

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