Microsoft Excel Tutorial – Troubleshooting when you double-click fill handle to copy a formula.

A great question this morning: Is there a bug in double-click the fill handle to copy a formula? Someone was convinced that Excel was being fooled by the formatting.

The behavior of copying a formula by double-clicking the fill handle changed in Excel 2010. Excel now looks at all columns to the left and the right when figuring out how far to copy down. Imagine if Excel does Ctrl+* to select the current region. Excel will copy down to the last row in the current region.

There are two exceptions:
a) If the cell immediately below the formula is non-blank, then Excel will only copy down to the cell above the blank cell in that column
b) If the cell below the formula is blank, but any other cell in that column is non-blank, Excel will stop copying to prevent overwriting any cells in the current column.

To download this workbook: https://www.mrexcel.com/youtube/MR_BDx3Lx9w/

Table of Contents
(0:00) Problem Statement: Is Fill Handle broken?
(0:12) Does formatting fool the fill handle in Excel?
(0:37) Fill Handle Blank Cells in Excel 2007 & Earlier
(1:07) Excel 2010 Improvements with Double-Click Fill Handle
(1:29) It is like Current Region
(1:50) Excel 2010 looks to right as well
(2:08) Diagonal connections work too
(2:33) Exception if cell below formula is non-blank
(3:23) Do not overwrite if cell below formula is blank
(3:50) Solution to original problem
(4:18) Make use of second exception
(4:45) Wrap-up