Double Click the Fill Handle to Copy a Formula
February 22, 2018 - by Bill Jelen
My favorite Excel trick of all time: You have thousands of rows of data and have to copy the formula to the end. Don't try to drag the formula to the end. There is a better way.
Go to the exact same spot where you would drag the formula to the end: the tiny square dot in the lower right corner of the cell. It is called the Fill Handle. Hover over that square dot. Your mouse pointer will change from an arrow to a black plus. Double click. Presto! The formula copies to the bottom of the data set.
This trick works if the data is the the left or to the right of your formula. What if the data on the right is taller than the data on the left? Excel will copy to the farthest row with contiguous adjacent data.
The Fill Handle Double-Click logic is like dripping water finding its way to the lowest point. Even in the following figure, the Excel logic manages to follow the path of the arrows.
The cell that can screw things up is the cell immediately below the formula. If that cell is non-blank, then Excel ignores the columns to the left and to the right. The formula will only copy to N6 in this figure.
If you've been using this technique for ever, you might remember when some blank cells in the column to the left would cause the fill handle to fail. Starting in Excel 2010, Excel considers all adjacent columns to the left.
Fun fact of the day: the name for a person with only one name is a Mononym
Thoracic Thursday - my favorite heart-pounding features in Excel.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
“Using a simple formula when three complex functions could have been used is no shame, but being ashamed of it is” (thanks Benjamin Franklin)
Title Photo: Skeeze / pixabay
