Copy a Formula to All Data Rows

September 09, 2021 - by Bill Jelen

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?

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.

Alternate Strategy: From E2, press Left Arrow to go to D2. Ctrl+Down Arrow to get to the bottom, Press Right Arrow to go back to column E. Ctrl+Shift+Up Arrow selects from the last row to E2. Ctrl+D will fill down and the formula in E2 will get copied. Practice for a week, and you can do all the keystrokes in less than a second. You will be on the team if Excel ever becomes an Olympic sport.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pawel Czerwinski on Unsplash