From yesterday's live seminar, a new way to fill quarters and years with the fill handle.
Transcript of the video:
Learn Excel from MrExcel, podcast episode 2357. A new way to use the fill handle for quarters and years.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I had a great day yesterday. Started out at 7:24 AM with a beautiful launch that I watched from the beach, and then I got to spend the whole day with the folks who actually do the accounting for those launches.
One of the things in my live seminars, if you give me a tip that's not in the book, you win this Excel Guru mission patch.
These are the first people who truly understand the whole culture of mission patches.
One of the things that I always talk about when we're talking about the fill handle is how frustrating it is to fill quarters and years.
If you have to do a five-year projection or something like that, it just doesn't work, right?
But the method that I've shown, and I'm sure I've shown it here on the podcast, and I always mentioned in the books before, is if you take that Q1 and reverse it to be 1Q, then, magically, it's going to work.
For some reason, Excel understands that we're advancing the 1, 2, 3, 4, and then after that, they go back to 1Q, but they advanced the year, then, at that point.
Okay, all right.
So people were okay with that, but they're like, "Okay, now we really hate the 1Q.
We really want it to be Q1".
It started a conversation of, "Well, could we use find and replace to flip those around, or could you do =MID of this, 2, 1, and someone just says, "No, wait.
This is silly, right?" It's not that hard of a problem.
Just take the Q1 2024, copy it four times.
It's super easy to edit, and then, select all four like that, and now, when you grab the fill handle, it's going to repeat this repeating pattern.
We'll drag that down.
If you're going to use my method, and then, build a formula to reverse it, this is definitely the easier way to go.
Thanks to Mariusz for that beautiful idea.
Let's come back for an outtake.
I started to wonder, that 1Q, if I just put a 1 there, see, then, I could have that 1 2024, and use a custom number of format with in quotes "Q"@ sign which would make it look like there were quarters there, but that Q is important.
If you don't have the Q after 4, they go up to 5, they go up to 6, and they never know to bump the year up.
Then, just for completeness here, what if we really wanted that formula?
I interrupted that formula as I was creating it.
The best way to do it would be =Q, ampersand, the left of D4, 1, that'll get the 1, and then the MID of D4 starting at the space in character 3 for a length of 5, and you can flip them around that way.
All right.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I had a great day yesterday. Started out at 7:24 AM with a beautiful launch that I watched from the beach, and then I got to spend the whole day with the folks who actually do the accounting for those launches.
One of the things in my live seminars, if you give me a tip that's not in the book, you win this Excel Guru mission patch.
These are the first people who truly understand the whole culture of mission patches.
One of the things that I always talk about when we're talking about the fill handle is how frustrating it is to fill quarters and years.
If you have to do a five-year projection or something like that, it just doesn't work, right?
But the method that I've shown, and I'm sure I've shown it here on the podcast, and I always mentioned in the books before, is if you take that Q1 and reverse it to be 1Q, then, magically, it's going to work.
For some reason, Excel understands that we're advancing the 1, 2, 3, 4, and then after that, they go back to 1Q, but they advanced the year, then, at that point.
Okay, all right.
So people were okay with that, but they're like, "Okay, now we really hate the 1Q.
We really want it to be Q1".
It started a conversation of, "Well, could we use find and replace to flip those around, or could you do =MID of this, 2, 1, and someone just says, "No, wait.
This is silly, right?" It's not that hard of a problem.
Just take the Q1 2024, copy it four times.
It's super easy to edit, and then, select all four like that, and now, when you grab the fill handle, it's going to repeat this repeating pattern.
We'll drag that down.
If you're going to use my method, and then, build a formula to reverse it, this is definitely the easier way to go.
Thanks to Mariusz for that beautiful idea.
Let's come back for an outtake.
I started to wonder, that 1Q, if I just put a 1 there, see, then, I could have that 1 2024, and use a custom number of format with in quotes "Q"@ sign which would make it look like there were quarters there, but that Q is important.
If you don't have the Q after 4, they go up to 5, they go up to 6, and they never know to bump the year up.
Then, just for completeness here, what if we really wanted that formula?
I interrupted that formula as I was creating it.
The best way to do it would be =Q, ampersand, the left of D4, 1, that'll get the 1, and then the MID of D4 starting at the space in character 3 for a length of 5, and you can flip them around that way.
All right.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
