Jul 12 2019 10:10 PM
I have a spreadsheet that is 40,000 rows longs. A simple formula is on line 1. I want to copy this all the way down the column to the last row. I know how to drag it. But dragging for 40,000 rows is too long! Is there a way to have excel repeat it every row right to the end?
thx
Jul 12 2019 10:54 PM
Double click the small square at the bottom right edge of the cell with the formula you want to copy.
Jul 13 2019 01:02 PM
I suggest first following @Twifoo suggestion of selecting the cell with the formula, then doubleclicking the little green square at bottom right corner of the selection marquee. It will stop copying down when it encounters the first blank cell in the column to its left.
Should that approach not take you down all 40,000 rows, the next easiest way of copying down the formula is to use the Address Bar just above the intersection of the row numbers and column letters. Type the range of cell addresses where you need the formula (e.g. B2:B40000), then hit Enter. This will select all those cells for you. Next, type the formula in the formula bar, and hit CTRL + Enter. That will copy down the formula in all those cells, making adjustments for any cell addresses with relative addresses for row numbers (i.e. no $).
Jul 14 2019 04:13 AM
If collect methods, slight modification of what Brad suggested
- type in B2 value/formula and stay on B2
- in Address Bar type B40000
- Shift+Enter
- Ctrl+D
Oct 07 2020 06:30 AM
Thank you for the huge time saving tip. @Twifoo