Problem: I have a worksheet with thousands of rows of data. I often enter a formula in a new column and need to copy it down to all of the rows. I try to do this by dragging the fill handle. But as I try to drag, Excel starts accelerating faster and faster. Before I know it, I've overshot the last row by thousands of rows. I start dragging back up. Again, Excel starts accelerating. Soon, the cell pointer is moving somewhere close to the speed of sound, and I find that I've overshoot the last row in the other direction. I end up going down and up, down and up. I call this frustrating process the "fill handle dance." Is there a way to stop the madness?
- Copy this formula all the way down.
Strategy: You can very quickly copy a formula down to all the rows by double-clicking the fill handle. Excel will copy the formula down until it encounters a blank row in the adjacent data. The fill handle is the square dot in the lower-right corner of the cell pointer box. When you hover your mouse over the fill handle, the cell pointer changes to a plus.
Gotcha: In Excel 2007 and earlier, a single blank cell in the adjacent column would cause the copy action to stop prematurely. If the adjacent column is particularly sparse, you could hide that column and then Excel would look at the next visible column to the left. This algorithm was improved in Excel 2010 and will usually get you to the bottom of the data.