Hey! Welcome back to the Mr. Excel Net cast, 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, the Microsoft can do quarters and after the Q4 this start over again in 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 second or fourth and then go back to first.
And right at that point, someone in the audience named Kara stops me and said “Wait a second! It doesn’t do any way that you want.” It doesn’t do the way that I want? And Kara puts in Q1-09 with the quarter and year. And I grab the fill handle and drag and checked this out, I just cuff the years up 9, 10, 11, 12. With this, I tried the apostrophe. Alright, 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 I think the apostrophe. Yes, it’s same thing. And I say, “No Kara. Okay, 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 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 the world’s worst format. Who would ever use this? This is terrible! You need a space or a dash or something between us and you know. And so we’re having this conversation on the audience and Kara says, “Oh, I’m going to have to go in a custom number format to put the dash back in.” Which would be fine if this was a four 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 stretch and across your report. And you just need the thing to fill, right? You just need to grab one and drag it over and do it. And it won’t do the right thing.
Okay, now when I did come up with, I said, you could go through this 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 this in a custom list which will get you out to 2028. And will try to calculate if you’re still employed in using Excel 2007 and 2028. Microsoft will have fixed this by then maybe.
So we choose the list going to the file menu, excel options and then edit custom list, and import that list, click Okay. Now of course it will work with just the single one, but really frustrating there. 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 at the seminar I was handing out copies of Excel for Marketing Managers like they were candy anytime, anyone had a good idea. So I’m going 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 so send your copy the Excel for Marketing managers Book.
Hey! I want to thank you for stopping by. I’ll see you next time for another net casts from Mr. Excel.
Transcription by:
Scribe4you Transcription Services