Shortcut to copy a long Column of data and/or Drag a formula down a long column

[User Name]

I'm working with some spreadsheets that have nearly 200,000 rows. Sometimes I have to do manipulations in "smaller chunks of perhaps 100,000 cells in column A & B. Is there a quick way to select a large group so that I can cut and past without dragging the cursor all the way down the column? Alternately, is there a quick trick you can use to drag a formula all the way down a column.

 

[T. Valko]

I'm working with some spreadsheets that have nearly 200,000 rows. Sometimes I have to do manipulations in "smaller chunks of perhaps 100,000 cells in column A & B. Is there a quick way to select a large group so that I can cut and past without dragging the cursor all the way down the column? Alternately, is there a quick trick you can use to drag a formula all the way down a column.

To quickly select a range...

Type the range address in the name box then hit Enter. The name box is that little "box" immediately above the column A header where it shows the cell address of the currently selected cell.

For example, to select the range A1:B10 you'd type A1:B10 in the name box then hit Enter.

To quickly copy a formula DOWN A COLUMN...

Select the formula cell
Left double click the fill handle

The fill handle is that little black square at the bottom right of the selected cell.

The formula will be copied down as far as there is a contiguous range of data on either side of the formula cell.

 

[User Name]

You are too wonderful for words. Easy peasy.

 

[T. Valko]

You are too wonderful for words. Easy peasy.

You're welcome. Thanks for the feedback!

 

[User Name]

I've asked a lot of questions on this board and been given a lot of great answers. I don't know why I've put up with the time consumption of dragging formulas down columns and/or copying huge sets of data but I have. Of all the great answers people have freely provided me on this board this trick (which I should have learned long ago) is probably going to save me day-to-day the most time and frustration.

Thank you again and Merry Christmas, Joyous Festivus and Happy New Year.

 

[T. Valko]

I've asked a lot of questions on this board and been given a lot of great answers. I don't know why I've put up with the time consumption of dragging formulas down columns and/or copying huge sets of data but I have. Of all the great answers people have freely provided me on this board this trick (which I should have learned long ago) is probably going to save me day-to-day the most time and frustration.

Thank you again and Merry Christmas, Joyous Festivus and Happy New Year.

Isn't it great when we learn something new that is "earth-shattering" ?

Thanks for the great feedback!