Copy function

[scott]

I am looking to copy a formula for a vertical array of data while referencing
the same column. I know that adding two $ before and after a cell letter
references that particular cell each time the formula is copied; however, i
want to reference the same column, not a particular cell. Also, I am working
with over 45,000 data points for 12 different worksheet; how do I copy a
formula down an entire worksheet without having to drag the little black box
at the bottom of a cell?

This will help GREATLY

Thanks!

Scott

 

[Mike H]

Hi

Double click the little black box (Fill handle) and the formula will fill
down as far as there are data in the adjacent column.

Mike

 

[scott]

Is there not a keyboard shortcut that is available? I noticed that using the
black box works only when the data in a column is right next to a column with
data in it. How about if you need to copy a formula down a spreadsheet that
is not directly next to a column with data, especially when large amounts of
data are involved, such as I am dealing with?

 

[Duke Carey]

A $ before the column letter indicates absolute column reference
A $ before the row number indicates absolute row reference.

In your case it sounds as though you want only the first $ before the column
number.

There is no single shortcut key for copying to the bottom of the range.

You can Ctrl-v to copy a cell or range of cells, then press Ctrl-down arrow
to go the next non-blank cell in the column - or to the bottom of the sheet
if you're in the last non-blank cell.

Other key combo would be Shift-Ctrl-End to get to the last used cell in the
sheet

 

[Gord Dibben]

$A1 will reference the same column but the row will increment.

Easiest way to copy down in a column with no adjacent data is to use the
namebox

Assume your formula is in A1

In namebox type A1:A45000 and ENTER

CTRL + d to copy down.


Gord Dibben MS Excel MVP