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: Excel Fill Handle To Copy Formula Rules Deep Dive - Episode 2611 Sample Files - MrExcel Publishing
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
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: Excel Fill Handle To Copy Formula Rules Deep Dive - Episode 2611 Sample Files - MrExcel Publishing
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
Transcript of the video:
More than you ever wanted to know about how it works when you double-click the fill handle.
With copy of formula down. So a great question this morning from someone.
Who thought that the fill handle was broken. They said, "Hey, check this out.
It seems to be using the formatting down there in rows 11, 12".
They actually sent a video where they deleted this to show that it's completely blank, selected the formulas, expected it to get copied down to row 10 and couldn't figure out why it was getting copied down to rows 11 and 12.
These cells are blank. Is it using the formatting?
No, it's definitely not using the formatting. And here's how I know that.
I've been doing this exact same demonstration in my Excel seminars going all the way back.
I pulled out a book that I wrote for Excel 2007 and in that book I talk about double click the fill handle and how double click the fill handle will break if the column to the left has a blank cell. So here we have first name, last name.
If you happen to have Cher or Madonna or Prince or Pele, they're in A10.
And B10 is blank.
Double click the fill handle would stop at row nine. This is something I always warn people about.
But when Excel 2010 came out and then I wrote this book, Rev Up to Excel 2010: Upgraders Guide to Excel 2010, right on page 102 I said that they've improved double click the fill handle and it now uses all of the columns to the left.
So here it finds its way to the bottom, even though cell B10 is blank.
It was a great improvement back in Excel 2010.
The way that you can think about this is let's say I was about to double click the fill handle here from C2.
It's as if you press control asterisk, which is a shortcut key to select the current region.
And you see that control asterisk goes down to row 16.
The formatting here in 17, 18, 19 is ignored.
And so when I double click the fill handle, it's going to go down to row 16. Beautiful.
Also in Excel 2010, they started looking both to the left and to the right.
So if for some reason this data in the right goes down further, here it goes down to row 18, again, control asterisk would select down to row 18.
So when we double click the fill handle, it's going to go down to row 18.
Sometimes I love to show off that this works even if it's just a diagonal connection. So from C2 it goes to B3 and then A4, right?
It has no problem following this.
And my joke in the seminar is I said, "It's only going to be a problem if you have someone with no first name and someone with no last name.
That will cause it to stop". Right?
And of course, who would have no first and last name?
Now, look, there's one important rule here that trumps all of these rules that I've talked about so far in this video.
The very first thing that Excel looks for is the cell immediately below.
If this cell, the cell, in this case C3, is non-blank, then all of the rules are off, right?
All of the things I've talked about go away.
All they're going to do is start from C3 and go down until they encounter a blank cell.
The diagonals here, the current region, none of that's going to work.
It's simply going to copy down to, in this case, C6.
That blanket C7 will cause it not to make it all the way to the bottom, right?
So there's something very special about that cell C3 that is overriding all of the rules. There's another way to override the rules.
Okay?
So in this case, the form is going to look left and right and it's going to find its way down to row 20.
Yes. Okay.
I'm going to undo that.
But let's say that something in this column was non-blank.
We'll just put something here, "Stop!" The double click the fill handle will not overwrite anything in this column.
So it's going to stop, in this case, in row 15. All right.
So going back to the original question, why is this copying down to row 12?
It's because of A11 and A12. So double click the fill handle, right?
You get it copied all the way down, but if I would undo that...
And just to prove, this is a horrible thing to do, but let's insert a blank column there.
And now that I've separated the names in A from the data in C, it will only copy down to the right spot. So I think the solution is that second exception.
Let's, instead of clearing these totals, fill the totals in and copy them over into these cells, and the averages, copy them over into those cells.
And now when we double click the fill handle, what's it going to do?
It's going to do control asterisk, which says go down to row 12.
But our rule that we're not going to overwrite anything in these columns will win and this will successfully copy down to row 10, leaving these formulas uninterrupted. Well, hey, I want to thank you for stopping by.
We'll see you next time for another NetCast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
With copy of formula down. So a great question this morning from someone.
Who thought that the fill handle was broken. They said, "Hey, check this out.
It seems to be using the formatting down there in rows 11, 12".
They actually sent a video where they deleted this to show that it's completely blank, selected the formulas, expected it to get copied down to row 10 and couldn't figure out why it was getting copied down to rows 11 and 12.
These cells are blank. Is it using the formatting?
No, it's definitely not using the formatting. And here's how I know that.
I've been doing this exact same demonstration in my Excel seminars going all the way back.
I pulled out a book that I wrote for Excel 2007 and in that book I talk about double click the fill handle and how double click the fill handle will break if the column to the left has a blank cell. So here we have first name, last name.
If you happen to have Cher or Madonna or Prince or Pele, they're in A10.
And B10 is blank.
Double click the fill handle would stop at row nine. This is something I always warn people about.
But when Excel 2010 came out and then I wrote this book, Rev Up to Excel 2010: Upgraders Guide to Excel 2010, right on page 102 I said that they've improved double click the fill handle and it now uses all of the columns to the left.
So here it finds its way to the bottom, even though cell B10 is blank.
It was a great improvement back in Excel 2010.
The way that you can think about this is let's say I was about to double click the fill handle here from C2.
It's as if you press control asterisk, which is a shortcut key to select the current region.
And you see that control asterisk goes down to row 16.
The formatting here in 17, 18, 19 is ignored.
And so when I double click the fill handle, it's going to go down to row 16. Beautiful.
Also in Excel 2010, they started looking both to the left and to the right.
So if for some reason this data in the right goes down further, here it goes down to row 18, again, control asterisk would select down to row 18.
So when we double click the fill handle, it's going to go down to row 18.
Sometimes I love to show off that this works even if it's just a diagonal connection. So from C2 it goes to B3 and then A4, right?
It has no problem following this.
And my joke in the seminar is I said, "It's only going to be a problem if you have someone with no first name and someone with no last name.
That will cause it to stop". Right?
And of course, who would have no first and last name?
Now, look, there's one important rule here that trumps all of these rules that I've talked about so far in this video.
The very first thing that Excel looks for is the cell immediately below.
If this cell, the cell, in this case C3, is non-blank, then all of the rules are off, right?
All of the things I've talked about go away.
All they're going to do is start from C3 and go down until they encounter a blank cell.
The diagonals here, the current region, none of that's going to work.
It's simply going to copy down to, in this case, C6.
That blanket C7 will cause it not to make it all the way to the bottom, right?
So there's something very special about that cell C3 that is overriding all of the rules. There's another way to override the rules.
Okay?
So in this case, the form is going to look left and right and it's going to find its way down to row 20.
Yes. Okay.
I'm going to undo that.
But let's say that something in this column was non-blank.
We'll just put something here, "Stop!" The double click the fill handle will not overwrite anything in this column.
So it's going to stop, in this case, in row 15. All right.
So going back to the original question, why is this copying down to row 12?
It's because of A11 and A12. So double click the fill handle, right?
You get it copied all the way down, but if I would undo that...
And just to prove, this is a horrible thing to do, but let's insert a blank column there.
And now that I've separated the names in A from the data in C, it will only copy down to the right spot. So I think the solution is that second exception.
Let's, instead of clearing these totals, fill the totals in and copy them over into these cells, and the averages, copy them over into those cells.
And now when we double click the fill handle, what's it going to do?
It's going to do control asterisk, which says go down to row 12.
But our rule that we're not going to overwrite anything in these columns will win and this will successfully copy down to row 10, leaving these formulas uninterrupted. Well, hey, I want to thank you for stopping by.
We'll see you next time for another NetCast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
