copying formula without scrolling 9000 rows

D

Dave F

Assume the range to which the formula is being applied is A1:A9000, and the
formula is being applied to cells B1:B9000.

Go the bottom of the range by selecting cell A1 and hitting CTRL + down
arrow. Go to B9001 and enter a period, or some other holding character. Hit
CTRL + Up arrow to go to B1. Enter your formula. SHIFT + CTRL + DOWN arrow.
CTRL + D.

Dave
 
M

Martin Fishlock

Another way to do it is to use te address bar on the left of the formula bar.

1. Enter A1:A9000 in the address bar
2. control+c to copy it
3. Enter B1:B9000 in the address bar
3. control+v to paste it

Just a twist.
 
D

Dave F

Interesting. I try to do everything witht he keyboard and avoid the mouse.
But I can see how this would be easier to people used to using the mouse.....
 
P

Pete_UK

I also like to use the keyboard, but I think this way is faster:

click on B1, then double-click the fill icon (the small black square
at the bottom of the cursor).

This will fill down for as many contiguous values as exist in column
A.

Pete
 
H

Harlan Grove

Dave F said:
Interesting. I try to do everything witht he keyboard and avoid the mouse.
But I can see how this would be easier to people used to using the mouse.....
Click to expand...
....

If copying A1:A9000 to B1:B9000,

[Ctrl]+[Home]
[Shift]+[Ctrl]+[Down]
[Ctrl]+C

[Ctrl]+V

If copying B1 down into A1:A9000 with A1:A9000 filled in but B2:B9000
blank and B1 the active cell,

[Ctrl]+C
[Shift]+

[Tab]
[Shift]+[Ctrl]+[Down]
[Tab]
[Shift]+

[Ctrl]+V

or

[Ctrl]+C
[F5]B2:B9000[Enter]
[Ctrl]+V

Latter is quicker when you know the rows, but former is more general.​
 
K

Kevin Vaughn

This is very helpful. I used to be able to do something similar when using
Lotus 123, but had not been able to make it work in Excel. One thing, I
can't seem to make the steps using Tab work What I did, for a smaller test
range, FWIW was:
To copy from B1 to B2:B(whatever) where whatever is the corresponding range
in A that has data in it (in my case, with no blanks interspersed)
from B1, Cntl-c
shift-left
shift-end-down
shift-right
cntl-v

BTW, I did try following the steps exactly, but may not have as I was
switching back-and-forth between Outlook Express and Excel.

--
Kevin Vaughn


Harlan Grove said:
Dave F said:
Interesting. I try to do everything witht he keyboard and avoid the mouse.
But I can see how this would be easier to people used to using the
Click to expand...
mouse.....
...

If copying A1:A9000 to B1:B9000,

[Ctrl]+[Home]
[Shift]+[Ctrl]+[Down]
[Ctrl]+C

[Ctrl]+V

If copying B1 down into A1:A9000 with A1:A9000 filled in but B2:B9000
blank and B1 the active cell,

[Ctrl]+C
[Shift]+

[Tab]
[Shift]+[Ctrl]+[Down]
[Tab]
[Shift]+

[Ctrl]+V

or

[Ctrl]+C
[F5]B2:B9000[Enter]
[Ctrl]+V

Latter is quicker when you know the rows, but former is more general.
​
Click to expand...
​
 
H

Harlan Grove

Kevin Vaughn said:
This is very helpful. I used to be able to do something similar when using
Lotus 123, but had not been able to make it work in Excel. One thing, I
can't seem to make the steps using Tab work What I did, for a smaller test
range, FWIW was:
Click to expand...
....

You have Transition Navigation Keys enabled. With A1:A10 filled in
(none blank), B1 filled in but the rest of column B blank, B1:A1
selected with B1 the active cell, [Shift]+[End] [Shift]+[Down] selects
B1:A10 with Transition Navigation Keys enabled but B1:A65536 with
Transition Navigation Keys disabled. That is, 123-like [End],[arrow
key] sequences extend from the last cell in the selected range, in
this example from A1 down to A10. Excel-like [End], [arrow key] (or
[Ctrl]+[arrow key]) sequences extend from the first cell in the
selected range, in this example from B1 down to B65536. IMO, this is
one place Lotus got it right and Microsoft wrong - most users would
likely prefer the 123-like behavior.
 
Top