After grouping a pivot table by week, you want to show the weeks with the most recent at the top. This does not work because the grouped weeks are text and September (9) sorts before December (12). Today, a workaround.
Transcript of the video:
The MrExcel podcast is sponsored by “Easy-XL”!
Learn Excel from MrExcel podcast episode 1939 – Pivot Table Group by Week, Latest to Earliest!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Boy, I hope that someone in the YouTube comments comes up with an easier way to do what we have to do today, because this is just ugly!
So a question came in to my YouTube channel, take a Pivot table by date, group this up, well let’s first copy that date there, and format it to see what day of the week it is, it is Tuesday, alright.
So we want weeks starting on Monday, so I have to start on January 31st.
We want to group this up to weekly data, so Group Field, choose just Days, uncheck Months, change this to 7 days, and then it’ll be 12/31/2012 because that’s the first Monday, alright, click OK.
Alright, good enough, it is appearing in earliest-to-latest sequence and everything is fine, the problem comes in when we decide that we want to sort that with the most recent at the top.
As soon as I click Z-A, it goes really bad because it’s text.
And so text-wise September 9 comes way before down here, this December because December starts with a 12, alright, it’s just so literal, so I’m going to undo that, Ctrl+Z.
Here’s my solution, which is really, really ugly, I admit!
I’m going to take those dates, I’m going to copy the dates, come out here to the right-hand side and paste, Ctrl+V, I’ll call this Original Text, and over here I’ll call it Sequence.
So here, =ROW(), double-click to copy that down, which gets us sequential numbers, Ctrl+C, convert to values, and now sort descending to get these into the right sequence.
Alright, once I have that, I’m going to select all of that text, File, Options, Advanced, scroll almost all of the way down, choose Edit Custom Lists, this is where they store Sunday, Monday, Tuesday, and so on.
And I selected that range, D4:D107, I’ll Import, we now have a Custom List that shows Excel what sequence we want that data in!
Alright, now you would think, usually custom lists will automatically sort the Pivot tables, but because there must be some bug where they’re not sorting after they group this data.
So, you have to come here, More Sort Options, you want to say- we’re actually going to say Ascending because the list is already in the right sequence.
But then you have to go like More Options, you HAVE to uncheck that box, Sort automatically, and then the First key sort order is going to be the custom list that we created.
Now the nightmare here is if you’re getting new data every single week, if this is a once-a-year kind of thing, not bad.
If you’re getting data every week, then you have to constantly keep up updating that custom list, you can imagine how frustrating that would be.
But there, we now have the data with the most recent week at the top, and going backwards.
Boy, I wish there was some easier way, and there probably is some easier way, let me know in the YouTube comments.
Alright well 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 1939 – Pivot Table Group by Week, Latest to Earliest!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Boy, I hope that someone in the YouTube comments comes up with an easier way to do what we have to do today, because this is just ugly!
So a question came in to my YouTube channel, take a Pivot table by date, group this up, well let’s first copy that date there, and format it to see what day of the week it is, it is Tuesday, alright.
So we want weeks starting on Monday, so I have to start on January 31st.
We want to group this up to weekly data, so Group Field, choose just Days, uncheck Months, change this to 7 days, and then it’ll be 12/31/2012 because that’s the first Monday, alright, click OK.
Alright, good enough, it is appearing in earliest-to-latest sequence and everything is fine, the problem comes in when we decide that we want to sort that with the most recent at the top.
As soon as I click Z-A, it goes really bad because it’s text.
And so text-wise September 9 comes way before down here, this December because December starts with a 12, alright, it’s just so literal, so I’m going to undo that, Ctrl+Z.
Here’s my solution, which is really, really ugly, I admit!
I’m going to take those dates, I’m going to copy the dates, come out here to the right-hand side and paste, Ctrl+V, I’ll call this Original Text, and over here I’ll call it Sequence.
So here, =ROW(), double-click to copy that down, which gets us sequential numbers, Ctrl+C, convert to values, and now sort descending to get these into the right sequence.
Alright, once I have that, I’m going to select all of that text, File, Options, Advanced, scroll almost all of the way down, choose Edit Custom Lists, this is where they store Sunday, Monday, Tuesday, and so on.
And I selected that range, D4:D107, I’ll Import, we now have a Custom List that shows Excel what sequence we want that data in!
Alright, now you would think, usually custom lists will automatically sort the Pivot tables, but because there must be some bug where they’re not sorting after they group this data.
So, you have to come here, More Sort Options, you want to say- we’re actually going to say Ascending because the list is already in the right sequence.
But then you have to go like More Options, you HAVE to uncheck that box, Sort automatically, and then the First key sort order is going to be the custom list that we created.
Now the nightmare here is if you’re getting new data every single week, if this is a once-a-year kind of thing, not bad.
If you’re getting data every week, then you have to constantly keep up updating that custom list, you can imagine how frustrating that would be.
But there, we now have the data with the most recent week at the top, and going backwards.
Boy, I wish there was some easier way, and there probably is some easier way, let me know in the YouTube comments.
Alright well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
