Have a Manager who thinks that they have the 'ultimate' method to Copy a Formula Down a Column? Let's find out! Today, Tyler asks Bill how to Copy a Formula Down a Column - in every Formula Copy variation that Bill can produce.
Transcript of the video:
Don't Fear the Spreadsheet podcast episode number 13: Every Way to Copy a Formula down a Column.
Hey, welcome back to Don’t Fear the Spreadsheet podcast.
I’m Bill Jelen, from MrExcel.
With today’s question: Tyler Nash.
Tyler Nash - from the worst to the best.
Please tell me every way to copy formula down a column.
Okay, Tyler!
Now, you know, we were writing the book and went through it; and we answered all of Tyler's questions.
And I was having so much fun with the whole thing-- I say, you know, we need to do a chapter that deals with the control freak managers.
These would be the people that ask you to do something, and then they stand over your shoulder watching what you do.
If you don't use their exact method they-- they go crazy: “no, no, no, there's a faster way to do it!” So, in that chapter -- it’s called “Every way to…” -- I took a whole bunch of very common tasks -- this is chapter 8 -- for how to change columns; how to copy cells; how to paste cells; how to copy a formula down a column; convert formulas to values.
And I showed you every single way to do it, from the slowest to the fastest.
So, that way, if your manager says, “No, there's a faster way”; well, you know, you can go read exactly what it is.
But sometimes your manager might be doing at the slow way, and you can show them the book and say, “No, hey, look, MrExcel says there's a faster way”.
All right, so, from the slowest to the fastest: well, the absolute worst way to do this is, you know, to copy and then, right down here at the bottom they say, well, “Select the destination and press ENTER or choose Paste”.
Well, pressing ENTER is horrible because it takes the item off the clipboard.
So we would then have to copy again; and now, you know, at least use Ctrl-v or paste, because then you could continue pasting from the clipboard.
Of course now, there's a faster, faster-- there's many faster ways to go.
All right, the way that most people use -- and this is still to me one of the slower ways -- is to take the cell with the formula, go to the square dot -- the fill handle -- and start to drag.
The problem with dragging is it starts out really slow, and then it starts going faster and faster and faster and faster; and they pause for one-tenth of a second at the bottom; but inevitably you end up way too far, all right.
So, then you try-- start to drag back up; down; back up; and finally get there, right.
It's just that whole speeding up and flying past the end that really, really makes it slow.
Now, if your manager is a crazy keyboard shortcut guy, he might have perfected this.
And it's funny, in the book I say, if you need to, you can perfect this and in about a week just practice it for an hour a day.
Ctrl-c to copy, left, Ctrl-down arrow, right, Ctrl-Shift-up arrow, Ctrl-c.
I've had people in my seminars who just love this method.
They use it all the time, and I say, “That's a lot of steps” and they said, “No, you really get to the point where you can just do it without even thinking about it at all”.
That method works really well if Column E is filled in; it doesn't work so well if Column E has a lot of blanks.
So I modify it like this: Ctrl-c, left, Ctrl-down arrow, right, Ctrl-Shift-up arrow, Ctrl-v.
I'm not going to see a lot about those.
They’re in the book.
There's people who swear by it; I've never gone to the trouble of doing that.
Here's the-- what I consider to be the fastest way.
And in Excel 2010 this works no matter if Column E is completely filled in or not; as long as there's some diagonal path to get back to the bottom of the data set you're good.
We're going to take that method too, the drag method, but we're not going to drag.
We're going to go to that exact same spot; double-click the fill handle; BAM, there it is.
It fills it all the way down to the bottom of the data set today.
So that’s the fastest way to copy a formula down a column.
Thanks for stopping by, we'll see you next episode.
Check out “Don't Fear the Spreadsheet”.
This book makes “Excel for Dummies” look like it was written for rocket scientists.
Hey, welcome back to Don’t Fear the Spreadsheet podcast.
I’m Bill Jelen, from MrExcel.
With today’s question: Tyler Nash.
Tyler Nash - from the worst to the best.
Please tell me every way to copy formula down a column.
Okay, Tyler!
Now, you know, we were writing the book and went through it; and we answered all of Tyler's questions.
And I was having so much fun with the whole thing-- I say, you know, we need to do a chapter that deals with the control freak managers.
These would be the people that ask you to do something, and then they stand over your shoulder watching what you do.
If you don't use their exact method they-- they go crazy: “no, no, no, there's a faster way to do it!” So, in that chapter -- it’s called “Every way to…” -- I took a whole bunch of very common tasks -- this is chapter 8 -- for how to change columns; how to copy cells; how to paste cells; how to copy a formula down a column; convert formulas to values.
And I showed you every single way to do it, from the slowest to the fastest.
So, that way, if your manager says, “No, there's a faster way”; well, you know, you can go read exactly what it is.
But sometimes your manager might be doing at the slow way, and you can show them the book and say, “No, hey, look, MrExcel says there's a faster way”.
All right, so, from the slowest to the fastest: well, the absolute worst way to do this is, you know, to copy and then, right down here at the bottom they say, well, “Select the destination and press ENTER or choose Paste”.
Well, pressing ENTER is horrible because it takes the item off the clipboard.
So we would then have to copy again; and now, you know, at least use Ctrl-v or paste, because then you could continue pasting from the clipboard.
Of course now, there's a faster, faster-- there's many faster ways to go.
All right, the way that most people use -- and this is still to me one of the slower ways -- is to take the cell with the formula, go to the square dot -- the fill handle -- and start to drag.
The problem with dragging is it starts out really slow, and then it starts going faster and faster and faster and faster; and they pause for one-tenth of a second at the bottom; but inevitably you end up way too far, all right.
So, then you try-- start to drag back up; down; back up; and finally get there, right.
It's just that whole speeding up and flying past the end that really, really makes it slow.
Now, if your manager is a crazy keyboard shortcut guy, he might have perfected this.
And it's funny, in the book I say, if you need to, you can perfect this and in about a week just practice it for an hour a day.
Ctrl-c to copy, left, Ctrl-down arrow, right, Ctrl-Shift-up arrow, Ctrl-c.
I've had people in my seminars who just love this method.
They use it all the time, and I say, “That's a lot of steps” and they said, “No, you really get to the point where you can just do it without even thinking about it at all”.
That method works really well if Column E is filled in; it doesn't work so well if Column E has a lot of blanks.
So I modify it like this: Ctrl-c, left, Ctrl-down arrow, right, Ctrl-Shift-up arrow, Ctrl-v.
I'm not going to see a lot about those.
They’re in the book.
There's people who swear by it; I've never gone to the trouble of doing that.
Here's the-- what I consider to be the fastest way.
And in Excel 2010 this works no matter if Column E is completely filled in or not; as long as there's some diagonal path to get back to the bottom of the data set you're good.
We're going to take that method too, the drag method, but we're not going to drag.
We're going to go to that exact same spot; double-click the fill handle; BAM, there it is.
It fills it all the way down to the bottom of the data set today.
So that’s the fastest way to copy a formula down a column.
Thanks for stopping by, we'll see you next episode.
Check out “Don't Fear the Spreadsheet”.
This book makes “Excel for Dummies” look like it was written for rocket scientists.
