Excel Fill Handle 1, 2, 3

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

Learn Excel from MrExcel podcast, episode 1977 - Fill Handle for 1-2-3!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Everyone knows how to use the fill handle, but there's good tricks in this video so hang on. Fill handle type in January, grabbed a little squirt out of the bottom of the cell, you can drag across, or you can drag down and it will fill the series, how cool is that? It works automatically, out of the box, with months, right, works with months, abbreviations, ahhh.

I have to tell you how the weirdest nightmare last night. I was thinking about what podcasts I'm going to do today, I typed in Jan, I grab the fill handle, drag, I got Jan, Marsha, Cindy, Bobby, Greg, and Peter! Okay that still works, works with days of the week, works with abbreviations of days of the week. They understand that at Microsoft Excel, they love accountants dealing quarters all the time, Q1, Q4, after Q4 it goes back to Q1. You can spell quarter in any conceivable way. Qtr 1, even 1st Quarter.

The one that drove me crazy for years though, and this is the one that I love, if you have to fill quarters AND years! Quarters AND years, and what I always used to do, I always used try Q1 2018 or 2016, and that's just chaos, because I get Q1 of each year, right? Undo, undo. But what you have to do is 1Q, and then after the Q put anything you want, a space, a - , ' , just about anything works in there! 1Q 2018, click and drag, and it will fill, 2 3 4, but then bump up the 18 to 19! All right, so the whole trick there, is to have the number at the beginning. Frankly, any word, and a number. So Room 101 will automatically fill.

OK so, Excel can do all of these January, the days of the week, months of the year, quarters, dates - but it can't do this one. Put in the number one, what would any rational person think we're going to get next? 2 3 4, what do you actually get? 1 1 1 1 1! Thanks Microsoft for being so consistent, you can fill all these other series, but you can't figure out that after one comes 2. Know what, people tell me is: "Well hey just give it a pattern, put in a 1 and a 2!" Yeah, that's true, but I didn't have to put in Jan and Feb, I could just put in January.

Beautiful trick here, this is from row 2, in Kent, OH, at the (?) conference! Again, 8 years ago, doing a live power Excel seminar there, someone said: "Oh no, you can do this! Hold down the CTRL key!" What? Hold down the CTRL key, sure enough, when you hold down the CTRL key, all of a sudden Excel knows how to count! How was anyone ever supposed to figure that out? In fact, how did this lady in Kent, OH figure it out, it had to be like, late on a Friday afternoon, where she's falling asleep, and accidentally falls asleep with her elbow on the CTRL key. I have no idea.

Hey, my friend Andrew Spain from Huntsville, AL, great Excel trainer and Excel consultant there said: "If you forget to hold down the CTRL key, right, so you start to drag and you see you're getting 1 1 1s, then, after the fact while you're doing the drag, hold down the CTRL key. See that little + right there? I'm going to have to zoom in so we can see it. The + next to the +, says that we are going to fill the series, instead of adding. If you just completely forget, if you just drag the 1s down there, and then you let go, right here, in any version, except for the latest version of Excel 2016, you're going to have Autofill options. Click that icon and choose Fill Series instead of Copy Cells! However, it's broken in this latest version of Excel 2016. Or just because I feel like I need to be complete and thorough, if you choose the number, and a blank cell to the right of the number, and drag down, that will automatically fill the 2 3 4.

All right, a couple more tricks here, dates' just normal, just grab the fill handle, drag, you get the next day, the next day, the next day. What if you need to copy 1/31, you don't want it to extend? Hold down CTRL, it seems to say: "Do the opposite of what you would normally do". Alright, if you work Monday through Friday, let's change this to a long date here, if you want to fill just the work week, Monday through Friday, right click and drag. Right click and drag will go down 250 rows, and choose Fill Weekdays! Fill weekdays will fill just the Monday through Friday dates. Double click there to make it wide enough for the longest value. That works great if you're working Monday through Friday. If you work anything, else Monday through Saturday, Tuesday through Saturday, every day except for off Wednesdays, well hey, Microsoft just doesn't care about you!

Now what if we have to fill months, so we want the last of every month? Right click and drag, and we will say Fill Months, and they're smart enough to go, they know that there's not 31 days in February, but in 2020 there were 29 days in February. Specific day of the year so, right click and drag, and say Fill Years, it'll give us that day of each year!

Hey, here's a great trick from my friend Bob Umlas, author of the "Excel Outside the Box" series, I think I stole this from his book. If you have to do payroll on 15th and last, select them both, right click and drag, and then say Fill Months, and it will always fill the 15th and the last of the month. Great, great trick there from Bob Umlas.

Alright, here's a recap of the concepts in this video: Some of my favorites for quarters and years, use 1Q, and then anything and the year, it has to be 1Q, not Q1, hold down the CTRL key, to have one filled 1, 2, 3, hold down the CTRL key to copy dates instead of filling. And then right click and drag the fill handle, to Fill Weekdays, Months, and Years.

Alright, all of that is in this book, "MrExcel XL", with more than 40 other tips, go ahead click that "i" in the top-right hand corner there, you can order the book in either print or e-book.

Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!