Excel Fill Handle 1, 2, 3
July 06, 2017 - by Bill Jelen
The fill handle is great for Months, Weekdays, Quarters, and Dates, but why won't it fill 1, 2, 3? This episode shows many workarounds. Also includes:
Why does the Excel Fill Handle pretend it does not know how to count 1, 2, 3?
The Fill Handle is great for filling months, weekdays, quarters and dates. Why doesn’t it know that 2 comes after 1?
In case you’ve never used the Fill Handle, try this: Type a month name in a cell. Select that cell. In the lower-right corner of the cell is a square dot. Click the dot and drag right or drag down. The tooltip increments to show the last month in the range.
When you let go of the mouse button, the months will fill. An icon appears giving you additional options.
The Fill Handle works great with months, days...
The fill handle works with quarters in many formats:
To do both quarters and years, you have to start with a number, then Q, then any punctuation (period, space, apostrophe, dash) before the year.
But when you type in 1 and grab the Fill Handle, Excel gives you 1, 1, 1, 1, 1, .... Many people tell me to enter the 1 and the 2, select them both, then drag the Fill Handle. But there is a faster way.
The secret trick is to hold down Ctrl! If you hold down Ctrl while dragging, Excel will fill 1, 2, 3.
Huntsville Alabama’s Andrew Spain of Spain Enterprise taught me a cool variation on this trick. If you start dragging without Ctrl, you can press Ctrl during the drag. A+icon appears at the bottom of the drag rectangle to indicate that you are going to fill instead of copy.
How were we supposed to figure out that Ctrl makes the Fill Handle count instead of copy? I have no idea. I picked up the tip from Row 6 at an IMA seminar in Kent, Ohio. It turns out that Ctrl seems to make the Fill Handle behave in the opposite way. If you Ctrl + drag a date, Excel will copy instead of Fill.
I've heard another trick: type 1 in A1. Select A1 and the blank B1. Drag. Excel fills instead of copies.
Right-Click the Fill Handle for More Options
If you right-click and drag the Fill Handle, a menu appears with more options, like Weekdays, Months, and Years. This menu is great for dates.
What if your payroll happens on the 15th and on the last day of the month? Put in both dates. Select them both. Right-click and drag the Fill Handle. When you finish dragging, choose Fill Months.
- The fill handle is great for Months, Weekdays, Quarters, and Dates, but why won't it fill 1, 2, 3? This episode shows many workarounds. Also includes:
- You can fill a series by using the Fill Handle
- You can drag down or right
- Filling months works automatically
- Filling weekdays works automatically
- Type in ALL CAPS to have the fill be all caps
- Abbreviations work for months and weekdays
- For years and quarters: 1Q-2016
- Any word followed by a number works
- Hold down Ctrl key to have 1 fill to 1 2 3
- You can press Ctrl after you start dragging
- Hold down Ctrl to copy dates instead of filling
- Right-click and drag to fill weekdays or months or years
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!
Title Photo: blickpixel / pixabay