Fast Formula Copy

The MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, plus Excel cartoons, cocktails tweets and jokes.

Learn Excel from MrExcel podcast, episode 1974 - Fast Formula Copy!

Alright, well hey, this whole book has a lot of illustrations in it, and the one that got me hooked was this illustration by Emily DeMarco. Emily was in some sort of an Excel seminar, where they were showing how to combine data using the concatenation character, and she was doodling through this, and someone in the audience said: "But isn't this the same as =CONCATENATE?" To whoever's teaching classes, yeah. But who can spell conCATenate? I saw that, I thought that was hilarious.

So the question today is how do we take this formula, I'm not going to talk about this formula, but how do we take this formula, or any formula, and copy it down to hundreds or thousands of rows of data? And before I get to my way, first we should acknowledge the Excel Olympics Method! There's people who can do these five steps like as a single keystroke, it takes less than a second, and if that's you, congratulations, kudos to you, if there's ever an Excel Olympics, I'm nominating you to be on the team. So if you're a keyboard shortcut person, left arrow moves to B2, CTRL+down arrow, right arrow, CTRL+Shift, up arrow selects to the top, and in my favorite one, the one that I didn't really ever use before, was CTRL+D. CTRL+D takes the formula in the top row of the selection, and copies it down. So if you love keyboard shortcuts, by all means, this is the way to go.

But the way that most people solve this problem is, they create that first formula up there in row 2, and they grab the fill handle and they start to drag, right, and it starts going faster and faster and faster and faster. Before you know, it you're hundreds of rows below the bottom of the data. And if you notice, they actually slow down right there. They're not long enough for you to actually react, just long enough for you to see the slowdown. So if you find yourself going down too far, and back up, I call that the fill handle dance, and you're going to love this next trick.

So, here's the faster way, do starts out the exact same way, you have the white cross there, there is the mouse cursor, when you get close, it changes to a black +, right there when you have that black +, double-click! Excel looks to the left, figures out how many rows of data we have today, and copies the formula down to the bottom! Whether it's a 112 rows in this case, or 1.1 million rows, either way, it gets to the bottom.

Alright now, for those of you, who already knew double-click the fill handle, you've known it for a long time: When does double click the fill handle not work? That's right, it's when someone doesn't have a last name! The column to the left has a blank. Instead of Cher, let's put in Prince. So normally, double click the fill handle, it looks to the left, and it's only going to copy down to row 8. Right, because of this blank in row 9. And of course, it's never on the first screen, it's always hundreds of rows down into the data. Well, that used to be the problem, but, silently without any notice in Excel 2010, they now look in all of the columns to the left, to figure out how far to copy the formula down. So it doesn't get fooled by the blank cell in the column to the left! This is by far my favorite tip in all of the Excel, When I'm doing the power Excel seminars, those live seminars, and I get to this about 45 minutes into the day, I almost always get a gasp from a few people into the room, when I double click the fill handle.

Now hey just a fun fact here, the "MrExcel XL" book, you know I grew up back in the day, back in the 70s when there were all sorts of movies, that came out with Roman numerals, so the Godfather part II was the first one in. In modern history, Back to the Future III, Star Wars IV, Rocky V, Star Trek VI, even Final Fantasy VII for 7. You know, and it's funny, Excel actually has a ROMAN function! Alright, so you can ask for the ROMAN of ROW, and that will give you the Roman numerals. What's really weird, because in Excel 2013, if you put in a Roman numeral XL, and you ask for the ARABIC, it'll convert that ROMAN numeral back to regular numeral. So when I got done with my 39th book, and I knew that the 40th book was coming up, just as a tip of the hat back to all of these movies that use Roman numerals, I knew that, it was just going to be "MrExcel XL", because it's my 40th book, although I put the tagline there, =ROMAN(40) , so people would get the joke!

Alright, just a quick Recap of all the things we talked about in this episode: And THIS is just tip #1 from the book. Well hey thanks, to everyone who wrote in suggesting this tip. These are all people who tweeted me in. One I didn't even cover here, Bill Hazlett, point out that CTRL+R is like CTRL+D, if you select one cell and drag to the right, or select to the right, CTRL+R will copy to the right. Go ahead and click that info button in the top right hand corner right now, to read about how you can buy this book.

Thanks for stopping by, we'll see you next time for another netcast from MrExcel!