MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Double Click the Fill Handle to Copy a Formula


February 22, 2018 - by Bill Jelen

Double Click the Fill Handle to Copy a Formula

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.

Double click the fill handle
Double click the fill handle

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.


Excel finds its way to the bottom.
Excel finds its way to the bottom.

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.

This one impresses me.
This one impresses me.

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 the cell below the formula is non-blank then that column alone is considered.
If the cell below the formula is non-blank then that column alone is considered.

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.

Mononymous people won't screw up the fill handle anymore
Mononymous people won't screw up the fill handle anymore

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