Priscilla from Plano Texas offers up yet another way to have the Fill Handle automatically extend 1 to 1, 2, 3. Episode 1111 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
(Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically you start out with massive amount of data.
You say: how we're going to analyze this?
Well, let's fire up a PivotTable and so you can solve this problem.) Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Picked up a cool trick when I was down in Plano, Texas last week doing a series of seminars.
Now this trick is related to the problem, you know, okay, the fill handle.
Put in January and grab the fill handle, it fills in all the months, but if you put in 1 and grab the fill handle, it just fills in 11111.
Alright, so I always have workarounds for this and there's a lot of different workarounds I've seen.
Some people, say, putting the 1 and the 2, select them both, grab the fill handle and drag and that will extend.
That works, you can also right-click.
Right-click the fill handle and drag, and then from this box choose Fill Series, that works.
You can left-click and drag, and after you're done open the Auto Fill Options and choose Fill Series.
But I always thought the fastest way to go, was to hold down the Ctrl key.
Hold down the Ctrl key and drag, and that will automatically fill a series.
That's the one that I like.
Another way to go: instead of putting the number 1 up there, let's put the formula =ROW(A1) and of course as we copy that down, it changes to row of A2, A3, A4, A5.
Or simply put a formula there =1+H1, alright.
So we have a common problem, that has struck many of us I'm sure, the 11111.
And many different ways to solve it.
If you think about it, it's interesting that if we put in “Room 1”, a little bit of text, it automatically extends to 2, 3, 4, 5, 6.
So I was down in Plano and someone on the right side of the room in row 2, her name was Priscilla, raises her hand with a way that I've never heard of before.
She said: oh, you need the 1 to extend, just put an apostrophe before it.
Putting ' before, well, that basically makes it text, right?
So now that it's text, you can just simply extend and just like the Room 1, 2, 3, 4,5, 6, it does 1, 2, 3, 4, 5, 6.
And then what do you do?
I'll use this little symbol here and say Convert to Number and bam, you're done.
So yet, another solution to this problem that's been dogging us for a long time, love it.
Hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Basically you start out with massive amount of data.
You say: how we're going to analyze this?
Well, let's fire up a PivotTable and so you can solve this problem.) Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Picked up a cool trick when I was down in Plano, Texas last week doing a series of seminars.
Now this trick is related to the problem, you know, okay, the fill handle.
Put in January and grab the fill handle, it fills in all the months, but if you put in 1 and grab the fill handle, it just fills in 11111.
Alright, so I always have workarounds for this and there's a lot of different workarounds I've seen.
Some people, say, putting the 1 and the 2, select them both, grab the fill handle and drag and that will extend.
That works, you can also right-click.
Right-click the fill handle and drag, and then from this box choose Fill Series, that works.
You can left-click and drag, and after you're done open the Auto Fill Options and choose Fill Series.
But I always thought the fastest way to go, was to hold down the Ctrl key.
Hold down the Ctrl key and drag, and that will automatically fill a series.
That's the one that I like.
Another way to go: instead of putting the number 1 up there, let's put the formula =ROW(A1) and of course as we copy that down, it changes to row of A2, A3, A4, A5.
Or simply put a formula there =1+H1, alright.
So we have a common problem, that has struck many of us I'm sure, the 11111.
And many different ways to solve it.
If you think about it, it's interesting that if we put in “Room 1”, a little bit of text, it automatically extends to 2, 3, 4, 5, 6.
So I was down in Plano and someone on the right side of the room in row 2, her name was Priscilla, raises her hand with a way that I've never heard of before.
She said: oh, you need the 1 to extend, just put an apostrophe before it.
Putting ' before, well, that basically makes it text, right?
So now that it's text, you can just simply extend and just like the Room 1, 2, 3, 4,5, 6, it does 1, 2, 3, 4, 5, 6.
And then what do you do?
I'll use this little symbol here and say Convert to Number and bam, you're done.
So yet, another solution to this problem that's been dogging us for a long time, love it.
Hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
