Always love this time of year - when Microsoft is hosting the MVP Summit - because it means that Bill "MrExcel" Jelen gets to go out to Seattle, Washington and hook up with none other than Mike "ExcelisFun" Girvin to do Dueling Excel together in the same room!
Today, in Episode #1524, Mike and Bill look at a topic that Bill previously introduced: 'Using The Macro Recorder to Fill Down' a column. See how both Mike and Bill address this topic - and then stay tuned for the 'Outtake' that follows the regular episode.
Dueling Excel Podcast #99...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Today, in Episode #1524, Mike and Bill look at a topic that Bill previously introduced: 'Using The Macro Recorder to Fill Down' a column. See how both Mike and Bill address this topic - and then stay tuned for the 'Outtake' that follows the regular episode.
Dueling Excel Podcast #99...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back! It's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I'm here live, in the office of the master Mike Excel Is Fun Gervin, here at Highline Community College.
This is our episode 99.
Record Fill Down All right so, you know, my favorite trick.
You have some little formula over here, and you need to get that formula copied down.
What do you do? You double click the fill handle to shoot it down.
Right! Great feature.
Unfortunately, the Macro recorder cannot, cannot record that.
So, I record a Macro.
Will just do Ctrl+A Click OK and Well, the Caps lock was on.
Ctrl+Shift+A that's alright.
So =2*that Ctrl+Enter to stay in that cell.
Double click the fill handle.
Right, everything should be good.
We press stop to stop recording, and then we play it back with Ctrl+Shift+A And we get an error. Right.
How frustrating is that, it's trying to fill some specific area.
So, we click stop and we say, well, that's that's horrible.
How could Macro recorder not do that for us.
And so, it was probably last week, I came up with this convoluted method, where I could actually use the Macro recorder to do this.
And it drove me a little bit crazy, because I had to have relative references off.
Start to record the Macro.
We do Ctrl+Shift+B there.
Click OK.
And I put my heading in, while relative was off.
There's my heading.
And press Enter and then my little formula =2* this.
Right, and then from there, I would go back to cell A1 with relative off, then turn relative on.
Now, Ctrl+Down arrow, Ctrl+Right arrow.
Right, one more time, that gets me to the bottom of the column.
Ctrl+Shift+Up arrow, and that's where I pulled out a feature that I didn't even know existed, called Ctrl+D to fill down and I was really happy that..
beautiful...that one worked, right.
And Mike is like, "O hey, wait why did you do all that?" Because, there's an easier way.
I do it all the time. I've been doing it that way for 1800 years.
He said so, that's where the dueling part comes in here.
Mike, let's see what you have.
Thanks MrExcel. Ah wow, so I love that fill down.
I mean, when I saw you do that a week ago, and I was just like, that's amazing.
I have a slightly different way back get that Ctrl+Down and that fill down is pretty cool.
Now so, by the way, I didn't even know, Macros is on the View tab.
I always go to the developer.
Now the way I'm going to do this is, I'm going to do relative the whole way, and that means later when I invoke this Macro, I better start in the cell, that's going to have the header.
All right, so I say header.
I better turn on the Macro, well that helps yeah, that's right, okay.
So, I'm going to come up here in Macro 3 and I'm going to do Ctrl+Shift+Q Click OK.
All right, so cell selected.
So, I want to type header.
It put whatever this word is in the active cell.
I'm going to go down one, and I'll do my formula, one cell to the left times whatever.
Whatever formula that is.
Ctrl+Enter and now I'm going to copy.
And now I'm noticing, that this is relative. So, when I left arrow, it's going back one column relative to that.
Ctrl+Down arrow will jump to the bottom of the current region.
And that will be recorded the same whether it's absolute or relative, over one that's a relative move.
Now, that step I just did when I Ctrl+Down arrow, that'll go down to whatever the bottom of the table is.
Now, I'm going to Ctrl+Shift+Up arrow.
Ctrl+V+Escape and Ctrl+Home.
All right, and then stop recording.
Now, if I come over here, if I were to do it over here, it wouldn't work.
So, I'm going to be sure. That's the thing about recording it all relative like that.
Ctrl+Shift+Q and we go to a different size data set.
Ctrl+Shift+Q It works just fine.
Come over here. If I Ctrl+Shift+Q this will be bad.
You don't ever want to do that.
So, that's the one drawback to doing the offset the whole way, like that.
All right, number 99 99 boy! Hope we do another one that would be cool. We can actually hit a 100.
A 100?
A 100.
Do you think we'll get to a 100?
I don't know, with the track record we're having tonight, it might be another year before we get to a 100.
All right, well hey, I want to thank everyone for stopping by.
We'll see you next time, for another netcast from MrExcel and Excel Is Fun.
Ctrl+V that Ctrl+Shift+Up arrows parallel to that Ctrl+Down arrow, and then Escape and maybe Ctrl+Home and then, I didn't have it turned on.
This is why, a podcast that never gets recorded.
You know, if this would have happened in episode one, we never would have done a hundred of these.
I'm Bill Jelen from MrExcel.
I'm here live, in the office of the master Mike Excel Is Fun Gervin, here at Highline Community College.
This is our episode 99.
Record Fill Down All right so, you know, my favorite trick.
You have some little formula over here, and you need to get that formula copied down.
What do you do? You double click the fill handle to shoot it down.
Right! Great feature.
Unfortunately, the Macro recorder cannot, cannot record that.
So, I record a Macro.
Will just do Ctrl+A Click OK and Well, the Caps lock was on.
Ctrl+Shift+A that's alright.
So =2*that Ctrl+Enter to stay in that cell.
Double click the fill handle.
Right, everything should be good.
We press stop to stop recording, and then we play it back with Ctrl+Shift+A And we get an error. Right.
How frustrating is that, it's trying to fill some specific area.
So, we click stop and we say, well, that's that's horrible.
How could Macro recorder not do that for us.
And so, it was probably last week, I came up with this convoluted method, where I could actually use the Macro recorder to do this.
And it drove me a little bit crazy, because I had to have relative references off.
Start to record the Macro.
We do Ctrl+Shift+B there.
Click OK.
And I put my heading in, while relative was off.
There's my heading.
And press Enter and then my little formula =2* this.
Right, and then from there, I would go back to cell A1 with relative off, then turn relative on.
Now, Ctrl+Down arrow, Ctrl+Right arrow.
Right, one more time, that gets me to the bottom of the column.
Ctrl+Shift+Up arrow, and that's where I pulled out a feature that I didn't even know existed, called Ctrl+D to fill down and I was really happy that..
beautiful...that one worked, right.
And Mike is like, "O hey, wait why did you do all that?" Because, there's an easier way.
I do it all the time. I've been doing it that way for 1800 years.
He said so, that's where the dueling part comes in here.
Mike, let's see what you have.
Thanks MrExcel. Ah wow, so I love that fill down.
I mean, when I saw you do that a week ago, and I was just like, that's amazing.
I have a slightly different way back get that Ctrl+Down and that fill down is pretty cool.
Now so, by the way, I didn't even know, Macros is on the View tab.
I always go to the developer.
Now the way I'm going to do this is, I'm going to do relative the whole way, and that means later when I invoke this Macro, I better start in the cell, that's going to have the header.
All right, so I say header.
I better turn on the Macro, well that helps yeah, that's right, okay.
So, I'm going to come up here in Macro 3 and I'm going to do Ctrl+Shift+Q Click OK.
All right, so cell selected.
So, I want to type header.
It put whatever this word is in the active cell.
I'm going to go down one, and I'll do my formula, one cell to the left times whatever.
Whatever formula that is.
Ctrl+Enter and now I'm going to copy.
And now I'm noticing, that this is relative. So, when I left arrow, it's going back one column relative to that.
Ctrl+Down arrow will jump to the bottom of the current region.
And that will be recorded the same whether it's absolute or relative, over one that's a relative move.
Now, that step I just did when I Ctrl+Down arrow, that'll go down to whatever the bottom of the table is.
Now, I'm going to Ctrl+Shift+Up arrow.
Ctrl+V+Escape and Ctrl+Home.
All right, and then stop recording.
Now, if I come over here, if I were to do it over here, it wouldn't work.
So, I'm going to be sure. That's the thing about recording it all relative like that.
Ctrl+Shift+Q and we go to a different size data set.
Ctrl+Shift+Q It works just fine.
Come over here. If I Ctrl+Shift+Q this will be bad.
You don't ever want to do that.
So, that's the one drawback to doing the offset the whole way, like that.
All right, number 99 99 boy! Hope we do another one that would be cool. We can actually hit a 100.
A 100?
A 100.
Do you think we'll get to a 100?
I don't know, with the track record we're having tonight, it might be another year before we get to a 100.
All right, well hey, I want to thank everyone for stopping by.
We'll see you next time, for another netcast from MrExcel and Excel Is Fun.
Ctrl+V that Ctrl+Shift+Up arrows parallel to that Ctrl+Down arrow, and then Escape and maybe Ctrl+Home and then, I didn't have it turned on.
This is why, a podcast that never gets recorded.
You know, if this would have happened in episode one, we never would have done a hundred of these.
