Though Bill has looked at this topic in previous episodes, today he shares a tip from a Mary. It is an interesting thing that Excel will address certain Alpha-numeric arrangements with the Fill Handle in chronological order, while other alpha-numeric arrangements it will not. Follow along with Episode #1722 as Bill highlights several configurations of Quarter Designation and shows us how we can work with Excel to get our desired results.
"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!
35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
"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!
35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1722 - Filling Quarters & Years!
Hey, welcome back, it's time for another MrExcel netcast, I'm Bill Jelen.
We've done this one before, but Mary today sent in a better way to go.
The fill handle, of course the fill handle, when you're using the fill handle for quarters, it's smart enough that after Quarter 4 it starts over again at Quarter 1.
And this is so flexible, there are so many different ways that you can do this, like spelling out quarter using QT, or just using Q1, all of those at work.
My favorite one, this is cool, even First Quarter, they get it.
But when you try a 1st, 2nd-3rd-4th.
But when you try and do quarters and years, like Q1 2013, you see that doesn't work, they're just repeating Q1 over and over and over again, and extending the year.
But what I can never remember is the correct way to do this.
Mary sent in a few more examples.
I've done this on the podcast once before, but Mary's ideas are: 1Q-13 will properly extend, we go 2-3-4, and then it goes back to 1Q, it goes to the next year.
She noticed that the period also works, so 4Q, then it goes to 1Q-2Q-3Q.
And then I tried it with a 4-digit year, that works.
And then also leaving out the -, but using the 1Q.
It's kind of funny that they make us use 1Q because, you know what, Q1 works just fine for quarters, 1Q does not work at all.
If you do 1Q, they don't know what to do, at all.
So it's kind of funny that the 1Q nomenclature is the one that works with the years.
Really confusing, that's probably why I can never remember this.
Well, we have a little bit more time today, so while we're here, a couple more tricks.
Here we have the 15th and the last of the month.
Maybe like, you know, if you're in payroll, and you've had in the 15th in the last of the month.
I'm going to right click and drag, and say that I want to fill months, fill months, and it correctly gets the 15th every single time, but then gets us the last of the month.
So the 31st, for June 30th, July 31st, August 31st, even out here in February goes to the 28th.
So cool trick there.
Another thing with the fill handle, any word followed by a number will automatically extend the number.
So, just like room 101 you can extend.
And a couple more, if we want to do Roman numerals, this is not the fill handle doing this, this is a formula, we're using =ROMAN of the ROW 1:1, we'll extend those Roman numerals.
Or if we want to do letters, we use the same trick, =CHAR of the ROW of 65:65.
The character 65 is the capital letter A, so that will let us fill the letters.
And there you go, some cool fill handle tricks, but thanks to Mary for sending in these variations of how to fill quarters and years.
You're going to have to write that one on a sticky note if it's something you have to do, because it really, to me, is not intuitive at all.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1722 - Filling Quarters & Years!
Hey, welcome back, it's time for another MrExcel netcast, I'm Bill Jelen.
We've done this one before, but Mary today sent in a better way to go.
The fill handle, of course the fill handle, when you're using the fill handle for quarters, it's smart enough that after Quarter 4 it starts over again at Quarter 1.
And this is so flexible, there are so many different ways that you can do this, like spelling out quarter using QT, or just using Q1, all of those at work.
My favorite one, this is cool, even First Quarter, they get it.
But when you try a 1st, 2nd-3rd-4th.
But when you try and do quarters and years, like Q1 2013, you see that doesn't work, they're just repeating Q1 over and over and over again, and extending the year.
But what I can never remember is the correct way to do this.
Mary sent in a few more examples.
I've done this on the podcast once before, but Mary's ideas are: 1Q-13 will properly extend, we go 2-3-4, and then it goes back to 1Q, it goes to the next year.
She noticed that the period also works, so 4Q, then it goes to 1Q-2Q-3Q.
And then I tried it with a 4-digit year, that works.
And then also leaving out the -, but using the 1Q.
It's kind of funny that they make us use 1Q because, you know what, Q1 works just fine for quarters, 1Q does not work at all.
If you do 1Q, they don't know what to do, at all.
So it's kind of funny that the 1Q nomenclature is the one that works with the years.
Really confusing, that's probably why I can never remember this.
Well, we have a little bit more time today, so while we're here, a couple more tricks.
Here we have the 15th and the last of the month.
Maybe like, you know, if you're in payroll, and you've had in the 15th in the last of the month.
I'm going to right click and drag, and say that I want to fill months, fill months, and it correctly gets the 15th every single time, but then gets us the last of the month.
So the 31st, for June 30th, July 31st, August 31st, even out here in February goes to the 28th.
So cool trick there.
Another thing with the fill handle, any word followed by a number will automatically extend the number.
So, just like room 101 you can extend.
And a couple more, if we want to do Roman numerals, this is not the fill handle doing this, this is a formula, we're using =ROMAN of the ROW 1:1, we'll extend those Roman numerals.
Or if we want to do letters, we use the same trick, =CHAR of the ROW of 65:65.
The character 65 is the capital letter A, so that will let us fill the letters.
And there you go, some cool fill handle tricks, but thanks to Mary for sending in these variations of how to fill quarters and years.
You're going to have to write that one on a sticky note if it's something you have to do, because it really, to me, is not intuitive at all.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
