When you drag the fill handle, Excel offers an extremely useful AutoFill Options dropdown. Well, it is extremely useful UNLESS you really want to get rid of the dropdown so you can see the value in the next row, then the dropdown is really annoying! In Episode 510, you will learn how to control whether Excel will copy or fill the series so that you can turn this annoying icon off.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Terry.
If you have a question for the podcast please either call and leave us a voicemail or drop me an email at bill@MrExcel.com and we'll get to you on a future podcast.
Now Terry had a question about the fill handle, uses the fill handle a lot but uses the autofill options box that pops up to control whether he should fill the series or copy and I have to admit that I never really even paid attention to this little icon that pops up.
It gives us a choice to either copy cells or fill the series so in this case because we had a numeric value it copied the cells, but we could change it to fill the series.
So it would fill in 1 2 3 4 5 6 Terry's big problem is this icon hides the data in the next row.
So we just copy everything down from down to row 6.
He now needs to look at row 7, but the icon is there and hiding the data in B7 and there doesn't seem to be any good way to get rid of it.
I figured all we could simply hit the escape key, and that doesn't seem to do it.
Terry said if I save the worksheet it gets rid of it.
But that has to be a real pain to go through and do it.
I mean I've tried various things selecting a different cell and then hitting escape.
Selecting a range of cells hitting escape and that the thing just stays there.
Persist. Terry says I like that option to be there because sometimes I need to copy and sometimes I need to fill a series.
So I don't want to turn it off. What I'm going to propose to Terry is that we do turn it off, but I show you how to control whether you fill the series or whether you copy.
So to turn it off we go to Tools Options and then choose the Edit tab.
On the Edit tab over on the right hand side, there's something called Show Paste Options button. We click OK and now when we drag the fill handle it will not show us that icon.
The icon doesn't persist.
Now here's the problem.
We have to learn how to control whether to fill the series or not.
And it's a different set of rules whether you have a numeric value or a date.
So let's say that we have a numeric value like 6 and we want to extend that series 6 7 8 9 10.
The trick is to hold down the Control key.
When you hold on the Control key excel will extend the series.
6 7 8 9 10.
If you wanted to copy the 6 you wouldn't do anything, you just grab the fill handle drag and it will fill in 6 6 6 6 6.
Now if you have dates though or text, it works completely opposite .
If you just grab the fill handle and drag, it will extend the series the 15th, the 16th, the 17th.
If you want to copy then you have to hold on the Control key while you click and drag and it will copy the values.
So knowing when to hold on the control key or not to hold the control key seems to solve the problem and it gets rid of that Icon that pops up.
Now I know with several thousand people watching this podcast, I want to get lots of email today saying.
Hey, Bill there's a really easy way to get rid of the icon.
Please feel free to let me know and I'll put that on a future podcast and also send it out to Terry.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Terry.
If you have a question for the podcast please either call and leave us a voicemail or drop me an email at bill@MrExcel.com and we'll get to you on a future podcast.
Now Terry had a question about the fill handle, uses the fill handle a lot but uses the autofill options box that pops up to control whether he should fill the series or copy and I have to admit that I never really even paid attention to this little icon that pops up.
It gives us a choice to either copy cells or fill the series so in this case because we had a numeric value it copied the cells, but we could change it to fill the series.
So it would fill in 1 2 3 4 5 6 Terry's big problem is this icon hides the data in the next row.
So we just copy everything down from down to row 6.
He now needs to look at row 7, but the icon is there and hiding the data in B7 and there doesn't seem to be any good way to get rid of it.
I figured all we could simply hit the escape key, and that doesn't seem to do it.
Terry said if I save the worksheet it gets rid of it.
But that has to be a real pain to go through and do it.
I mean I've tried various things selecting a different cell and then hitting escape.
Selecting a range of cells hitting escape and that the thing just stays there.
Persist. Terry says I like that option to be there because sometimes I need to copy and sometimes I need to fill a series.
So I don't want to turn it off. What I'm going to propose to Terry is that we do turn it off, but I show you how to control whether you fill the series or whether you copy.
So to turn it off we go to Tools Options and then choose the Edit tab.
On the Edit tab over on the right hand side, there's something called Show Paste Options button. We click OK and now when we drag the fill handle it will not show us that icon.
The icon doesn't persist.
Now here's the problem.
We have to learn how to control whether to fill the series or not.
And it's a different set of rules whether you have a numeric value or a date.
So let's say that we have a numeric value like 6 and we want to extend that series 6 7 8 9 10.
The trick is to hold down the Control key.
When you hold on the Control key excel will extend the series.
6 7 8 9 10.
If you wanted to copy the 6 you wouldn't do anything, you just grab the fill handle drag and it will fill in 6 6 6 6 6.
Now if you have dates though or text, it works completely opposite .
If you just grab the fill handle and drag, it will extend the series the 15th, the 16th, the 17th.
If you want to copy then you have to hold on the Control key while you click and drag and it will copy the values.
So knowing when to hold on the control key or not to hold the control key seems to solve the problem and it gets rid of that Icon that pops up.
Now I know with several thousand people watching this podcast, I want to get lots of email today saying.
Hey, Bill there's a really easy way to get rid of the icon.
Please feel free to let me know and I'll put that on a future podcast and also send it out to Terry.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
