A date slicer in Excel is showing Jan, Feb, Mar... But your fiscal year starts in July. How can you get the slicer to show Jul, Aug, Sep? Bjorn from New Zealand provides a faster way to sort the slicers without using the Data Model or Power Pivot.
How to change the sequence of items in a pivot table slicer
Old method involved using the "Sort by" feature in Power Pivot
New method: Add a custom list with the correct sequence
The slicer will respect the custom list
The last custom list in the dialog will "win"
How to change the sequence of items in a pivot table slicer
Old method involved using the "Sort by" feature in Power Pivot
New method: Add a custom list with the correct sequence
The slicer will respect the custom list
The last custom list in the dialog will "win"
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2069: Sorting Slicers Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
I was doing a webinar for Australia and New Zealand yesterday and a question came across that I cringed at. They said, “Hey look, our fiscal year runs July through June.
How can you reorganize these slicers?” And I remembered that there's a way to do this in Power Pivot.
You have to take the data to the data model, create another table with the correct sequence like the month names and the numbers you’ll select, you would have something like this: 1 2.
I’m just going to hold on to Ctrl key there and drag that down.
And you would add this little table, link this table to the original table and then in Power Pivot you would sort by this column: Sort this field by this column, alright.
And then put this field on the slicer.
And, one of the comments came in from Bjorn who lives in New Zealand.
Bjorn says, “No, you don't have to do that.
Just use a Custom list.” I'm like, Custom list?
I know that works in a pivot table but I'm sure it used to not work in a slicer.
But check it out - It works in a slicer.
So, here's what you do.
You type the list in the order that you want them to appear like that and select that list, go into: File, Excel options- Excel options and then go to Advanced.
In the Advanced Dialog, this is a long dialog you want to scroll 83% all the way down, right there a button called Edit Custom Lists.
You see, this is where they store Sunday, Monday, Tuesday and Jan, Feb, Mar.
But you’re not allowed to change that when it’s grayed out.
Those four are protected but we're going to Import our new list, like that.
And here's the beautiful thing, okay, so July is in this list and July is in this list, the list at the bottom wins, alright?
So this list is now going to become the prevailing list for the sequence.
Click OK, click OK, alright.
And then, what we're going to do is we'll Insert a new Slicer, so: Analyze, Insert Slicer based on date, click OK.
And it now appears in the right sequence.
So cool, that’s great now.
Of course in my Slicers, I like to rearrange them here a little bit, make them a little bit shorter so they all fit in one nice little area.
I'm even going to hide those two- two little stupid ones down there for the greater than and less than, alright?
So there we go.
A cool, cool trick from Bjorn in New Zealand.
And I'm setting an Excel guru mission patch: Bjorn for that great idea.
I was hand these out of my live seminars, I figured, what the heck.
I can do it in a webinar too.
Oh boy, hey, they mail easier than the old Excel- Excel guru enamel pencils got broken every time I mail them.
This one I think will survive.
Alright so, how to change the sequence of items in a pivot table slicer?
The old method involved using the “Sort by” feature in Power Pivot.
New method: Add a custom list with a correct sequence and then when you recreate that slicer, it will respect the custom list.
If you have July in two different lists, the one that is last in the dialog box will win.
Thanks, Bjorn, for that- for sending that tip in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I was doing a webinar for Australia and New Zealand yesterday and a question came across that I cringed at. They said, “Hey look, our fiscal year runs July through June.
How can you reorganize these slicers?” And I remembered that there's a way to do this in Power Pivot.
You have to take the data to the data model, create another table with the correct sequence like the month names and the numbers you’ll select, you would have something like this: 1 2.
I’m just going to hold on to Ctrl key there and drag that down.
And you would add this little table, link this table to the original table and then in Power Pivot you would sort by this column: Sort this field by this column, alright.
And then put this field on the slicer.
And, one of the comments came in from Bjorn who lives in New Zealand.
Bjorn says, “No, you don't have to do that.
Just use a Custom list.” I'm like, Custom list?
I know that works in a pivot table but I'm sure it used to not work in a slicer.
But check it out - It works in a slicer.
So, here's what you do.
You type the list in the order that you want them to appear like that and select that list, go into: File, Excel options- Excel options and then go to Advanced.
In the Advanced Dialog, this is a long dialog you want to scroll 83% all the way down, right there a button called Edit Custom Lists.
You see, this is where they store Sunday, Monday, Tuesday and Jan, Feb, Mar.
But you’re not allowed to change that when it’s grayed out.
Those four are protected but we're going to Import our new list, like that.
And here's the beautiful thing, okay, so July is in this list and July is in this list, the list at the bottom wins, alright?
So this list is now going to become the prevailing list for the sequence.
Click OK, click OK, alright.
And then, what we're going to do is we'll Insert a new Slicer, so: Analyze, Insert Slicer based on date, click OK.
And it now appears in the right sequence.
So cool, that’s great now.
Of course in my Slicers, I like to rearrange them here a little bit, make them a little bit shorter so they all fit in one nice little area.
I'm even going to hide those two- two little stupid ones down there for the greater than and less than, alright?
So there we go.
A cool, cool trick from Bjorn in New Zealand.
And I'm setting an Excel guru mission patch: Bjorn for that great idea.
I was hand these out of my live seminars, I figured, what the heck.
I can do it in a webinar too.
Oh boy, hey, they mail easier than the old Excel- Excel guru enamel pencils got broken every time I mail them.
This one I think will survive.
Alright so, how to change the sequence of items in a pivot table slicer?
The old method involved using the “Sort by” feature in Power Pivot.
New method: Add a custom list with a correct sequence and then when you recreate that slicer, it will respect the custom list.
If you have July in two different lists, the one that is last in the dialog box will win.
Thanks, Bjorn, for that- for sending that tip in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
