The Excel fill handle is really cool with quarters. But wait....it can not do quarters and years. But wait...it can do them but in a hideous format. Episode 1132 asks - "Does anyone have a better way?"
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I have a great one today, this is a very, very annoying mystery, hoping you can help.
I was in Columbus, Indiana, I'm doing my Excel seminar, and I'm at the point where I'm talking about the fill handle.
And how amazing it is that Microsoft can do quarters, and after Q4 they start over again at Q1, and how you can spell quarter just about any way you would like.
You can do Q1, you can do Qtr1, You can even do 1st Quarter, you know, which I think is amazing, that they can do 2nd, 3rd, 4th, and then go back to 1st.
And right at that point, someone in the audience named Cara(?) stops me and says “Wait a second, it doesn't do any way that you want, it doesn't do the way that I want.” And Cara puts in Q1-09 with the quarter and the year, and I grab the fill handle and drag, and check this out.
It just count the years up, 9-10-11-12.
She says “I've tried the apostrophe, it doesn't work, and I even tried going the other way of putting the quarters at the end, and what does it goes up to Q5, Q6, Q7, Q8.” Same thing with the apostrophe, yeah same thing.
I said “You know Cara, OK, they just didn't think about this, they didn't think about extending both the quarter and the year.” You know, but then, just kind of out of desperation, I put this one in, Q109 without the dash and they get it right.
They did think about it, they put it in, but they put it in in the world's worst format.
Who would ever use this?
This is terrible!
You need a space or a dash or something between this, you know.
And so, we're having this conversation now in the audience, and Cara's like “Oh, I'm going to have to go into Custom Number Format to put the dash back in.” Which would be fine if this was a 4-digit number, but it's not, it's text, nothing we can do here in Custom Number format is going to insert that dash back in.
Now, we did come up with, if you type all four and grab the fill handle and drag, then it will do the right thing, but who wants to do that, Right?
I mean, you have a report, you have a few quarters stretching across the report, and you just need the thing to fill.
Alright, you just need to grab one and drag it over and do it, and it won't do the right thing.
OK, now what I did come up with, I said that you could go through the somewhat painful process of producing every single possible, you know, year and quarter.
Go back a few years, you know, as far back as you might be reporting, and then forward.
Actually, you can record up to 96 of these in a Custom List, which will get you out to 2028.
And which, frankly, if you're still employed and using Excel 2007 in 2028, you know, Microsoft will have fixed this by then, maybe.
So we choose the list, go into the File menu, Excel Options, and then Edit Custom lists and import that list, click Ok, now, of course, it will work with just the single one.
But really frustrating that we would have to do that when Excel clearly knows how to extend it, based on what's back here in column K, they just used a horrible format.
If that one didn't work, I would just say “You know, well, but they thought about it, they thought about it, but used the world's worst format!” Alright so, yesterday in the seminar, I was handing out copies of “Excel for Marketing Managers” like they were candy any time anyone had a good idea, so I want to make the same offer to you.
If you have a solution to this, have a way to get the quarters with either a space or a dash or something in between them, some format other than this hideous format.
Shoot me a note, bill@mrexcel.com, put you on the podcast, I'll, send you a copy of the Excel for Marketing Managers” book.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I have a great one today, this is a very, very annoying mystery, hoping you can help.
I was in Columbus, Indiana, I'm doing my Excel seminar, and I'm at the point where I'm talking about the fill handle.
And how amazing it is that Microsoft can do quarters, and after Q4 they start over again at Q1, and how you can spell quarter just about any way you would like.
You can do Q1, you can do Qtr1, You can even do 1st Quarter, you know, which I think is amazing, that they can do 2nd, 3rd, 4th, and then go back to 1st.
And right at that point, someone in the audience named Cara(?) stops me and says “Wait a second, it doesn't do any way that you want, it doesn't do the way that I want.” And Cara puts in Q1-09 with the quarter and the year, and I grab the fill handle and drag, and check this out.
It just count the years up, 9-10-11-12.
She says “I've tried the apostrophe, it doesn't work, and I even tried going the other way of putting the quarters at the end, and what does it goes up to Q5, Q6, Q7, Q8.” Same thing with the apostrophe, yeah same thing.
I said “You know Cara, OK, they just didn't think about this, they didn't think about extending both the quarter and the year.” You know, but then, just kind of out of desperation, I put this one in, Q109 without the dash and they get it right.
They did think about it, they put it in, but they put it in in the world's worst format.
Who would ever use this?
This is terrible!
You need a space or a dash or something between this, you know.
And so, we're having this conversation now in the audience, and Cara's like “Oh, I'm going to have to go into Custom Number Format to put the dash back in.” Which would be fine if this was a 4-digit number, but it's not, it's text, nothing we can do here in Custom Number format is going to insert that dash back in.
Now, we did come up with, if you type all four and grab the fill handle and drag, then it will do the right thing, but who wants to do that, Right?
I mean, you have a report, you have a few quarters stretching across the report, and you just need the thing to fill.
Alright, you just need to grab one and drag it over and do it, and it won't do the right thing.
OK, now what I did come up with, I said that you could go through the somewhat painful process of producing every single possible, you know, year and quarter.
Go back a few years, you know, as far back as you might be reporting, and then forward.
Actually, you can record up to 96 of these in a Custom List, which will get you out to 2028.
And which, frankly, if you're still employed and using Excel 2007 in 2028, you know, Microsoft will have fixed this by then, maybe.
So we choose the list, go into the File menu, Excel Options, and then Edit Custom lists and import that list, click Ok, now, of course, it will work with just the single one.
But really frustrating that we would have to do that when Excel clearly knows how to extend it, based on what's back here in column K, they just used a horrible format.
If that one didn't work, I would just say “You know, well, but they thought about it, they thought about it, but used the world's worst format!” Alright so, yesterday in the seminar, I was handing out copies of “Excel for Marketing Managers” like they were candy any time anyone had a good idea, so I want to make the same offer to you.
If you have a solution to this, have a way to get the quarters with either a space or a dash or something in between them, some format other than this hideous format.
Shoot me a note, bill@mrexcel.com, put you on the podcast, I'll, send you a copy of the Excel for Marketing Managers” book.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
