Copy function

  • Thread starter Thread starter scott
  • Start date Start date
S

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
 
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
 
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?
 
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
 
$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
 
Back
Top