How to drag/paste formula on looong spreadsheet.

**mud_shark** · 01-14-2010, 11:54 PM

I am working with a spreadsheet with around 20,000 rows.

In a smaller spreadsheet, if I wanted to copy a formula down to the cells in the column below, I would simply drag and drop, or copy paste - easy..

Since this spreadsheet is so long, I'd like to be able to copy/drag the formula to all 20000 rows without manually scrolling down with the mouse button held down (as it takes ages)

Appreciate any suggestions for a shortcut.

**Paul** · 01-15-2010, 12:02 AM

Hi Mud_shark,

Two quick ways come to mind:

1. If there are values in the column to the left or right of the one you want to fill, and they go all the way down without any blanks, you can double-click on the lower-right corner of the cell with the formula and it will fill down all cells until it reaches a blank cell to the left (or right) of it.

2. Select the cell with the formula (let's say it's B2). Press F5 to open the GoTo dialog. In the Reference textbox, type the last cell you want the formula to go to, e.g. B21005. Now's the important part: Hold down the SHIFT key and then click OK. This will select all cells from B5:B21005. You can then use the Fill -> Down menus to fill the formula down the entire selection.

**mud_shark** · 01-15-2010, 12:09 AM

Perfect - I knew it had to be something simple like that..

Thank-you!

**Tiresidedown** · 01-15-2010, 02:00 AM

Two other easy ways.

In most cases you can copy and then paste just as fast (usually faster for me) as dragging.

If you are jumping back and forth repeatedly, you can put 'fake' or placeholders info in the cells at the top or bottom and then use CTRL+ARROW to jump there. If you do it while holding shift, you'll select everything in the middle.

I've done this a bunch, creating a row or column of periods or x's that I can jump to without having to type in the address of a particular cell.

Best