Justin on Twitter asked what determines the order of the drop down for a selection list in a pivot table. In Episode 1784, Bill shows a pivot table that ends up in a seemingly random sequence - three large cities at the top, followed by alpha after that. This episode is full of accidental discoveries - things that can help Justin solve the problem, but also plenty of useful tricks that can let you control the order of items in a pivot table.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1784: What Controls The Order Of Items In A Pivot Table?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
This question from Justin on Twitter, what controls the order of items in a pivot table, and I think I know the exact problem.
So, we have some store locations, dates, and sales.
We want to create a pivot table from this data.
So, INSERT, PIVOT TABLE, and I'm going to say EXISTING, LOCATION.
I'm just going to put it right here next to our original data.
Click OK, and we'll put DATES going across, SALES in the VALUES area, and then LOCATION down the side in the ROW LABELS, and for some reason, the items are not appearing in alphabetical order.
Orlando, Jacksonville, Daytona.
It's just…it's seemingly random.
What the heck is going on?
Now, the last few items are in alphabetical order – Fort Myers, Merritt Island, St.
Pete, and Tallahassee – but the others are not in any particular order, and this even happens if we would go…put it up in the REPORT FILTER, it appears in this mysterious order, and if we insert a slicer for LOCATION, it's appearing in this order, alright, and that's unusual.
Usually, it should be alphabetical.
We might be able to go in and say, hey, we want to make this appear in the order of revenue, largest to smallest or something like that, but just fresh out of the box, for some reason, we're getting this strange sequence, and I'm going to show you how to fix this, but it also is very, very useful if you want your pivot tables to always sort into some particular sequence.
So, we're going to go to FILE and then EXCEL OPTIONS, and then ADVANCED, and scroll all the way to the bottom.
It's about 83% of the way down, right there, EDIT CUSTOM LISTS, and what's happened is I was in Orlando doing a seminar, my Power Excel seminar, and I asked people to give me a list of names, and I showed them how to create a custom list, and we created a list that has Orlando, Jacksonville, Miami, Tampa, Gainesville, Daytona, Cocoa Beach, and Apopka, and although that list is completely unrelated to this pivot table, because the pivot table shares a subset of items with a custom list, it's appearing in the order of the custom list for those items.
So, Orlando, Jacksonville, and then Daytona and Cocoa Beach happened to be in this completely unrelated custom list, and so it ordered them in that order, and then everything else just went alphabetically at the end.
Isn't that wild?
Okay, so, let's see.
If I really had this problem, if this was really my problem and this was just a random stray custom list that I created from a seminar, and I don't need this list anymore, then I would delete the list.
That would be my first choice.
If I needed to keep this list for some other reason, like, I've…you know, this was a different list of locations and I frequently had to fill the list, Orlando, grab the fill handle and drag to get the list of those cities, and I still needed that, well, then I would be very tempted to do this.
I would take the original data, CONROL+C, come out here to the side, CONTROL+V. On the DATA tab, REMOVE DUPLICATES, click OK.
That gives us just the unique list of items in this list.
I would bold the headings.
That way I can sort with a single click.
We now have the data alphabetically, and I would do these steps.
I would select this list, FILE, OPTIONS, ADVANCED, again down to EDIT CUSTOM LISTS, and import that list as a new list, alright, and then I would come back up, remove the original list, DELETE, click OK, but we still needed that list, right?
Well, then, I'm going to say I want to also import the original list here.
Click IMPORT, and what that's done now is I have 2 lists.
I have the Cocoa Beach order first…list appearing first, and then Orlando, Jacksonville, Miami, and I think the way that they do this is they use the first custom list that they find.
Alright.
So, let's get rid of this slicer, let's get rid of the pivot table, get rid of our stray lists here, and come back to the original data.
Alright.
Let's INSERT a PIVOT TABLE, EXCISTING WORKSHEET, click OK, put LOCATION on the left-hand side, and say…it's still coming up in some relatively random sequence.
Let's go to MORE SORT OPTIONS.
We’ll say ASCENDING BY LOCATION, also click MORE OPTIONS, uncheck SORT AUTOMATICALLY, and then we can go in and specifically choose the custom list.
I can see how this would be a really, really maddening situation if it just starts randomly happening to you.
You set up the custom list months ago, years ago, forgot it was there, and because it has some intersection of the items in your pivot table today, it is causing all of these problems.
So, Justin, I feel your pain.
Everyone else, remember this.
This is if it ever happens to you randomly or if you actually want to control the order of the items in the list.
Hey, by the way, this works for regular Excel pivot tables, not OLAP pivot tables, not Power Pivot pivot tables.
Power Pivot and OLAP don't respect the custom lists.
Also, hey, Justin.
You know, there's another great way to go.
Just take this original data, make it into a table with CONTROL+T. If you have Power Pivot, run it through a Power Pivot, and then it will sort automatically alphabetically.
There we go.
We can take advantage of that.
Usually annoying feature in Power Pivot but it'll work to your advantage here.
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 1784: What Controls The Order Of Items In A Pivot Table?
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
This question from Justin on Twitter, what controls the order of items in a pivot table, and I think I know the exact problem.
So, we have some store locations, dates, and sales.
We want to create a pivot table from this data.
So, INSERT, PIVOT TABLE, and I'm going to say EXISTING, LOCATION.
I'm just going to put it right here next to our original data.
Click OK, and we'll put DATES going across, SALES in the VALUES area, and then LOCATION down the side in the ROW LABELS, and for some reason, the items are not appearing in alphabetical order.
Orlando, Jacksonville, Daytona.
It's just…it's seemingly random.
What the heck is going on?
Now, the last few items are in alphabetical order – Fort Myers, Merritt Island, St.
Pete, and Tallahassee – but the others are not in any particular order, and this even happens if we would go…put it up in the REPORT FILTER, it appears in this mysterious order, and if we insert a slicer for LOCATION, it's appearing in this order, alright, and that's unusual.
Usually, it should be alphabetical.
We might be able to go in and say, hey, we want to make this appear in the order of revenue, largest to smallest or something like that, but just fresh out of the box, for some reason, we're getting this strange sequence, and I'm going to show you how to fix this, but it also is very, very useful if you want your pivot tables to always sort into some particular sequence.
So, we're going to go to FILE and then EXCEL OPTIONS, and then ADVANCED, and scroll all the way to the bottom.
It's about 83% of the way down, right there, EDIT CUSTOM LISTS, and what's happened is I was in Orlando doing a seminar, my Power Excel seminar, and I asked people to give me a list of names, and I showed them how to create a custom list, and we created a list that has Orlando, Jacksonville, Miami, Tampa, Gainesville, Daytona, Cocoa Beach, and Apopka, and although that list is completely unrelated to this pivot table, because the pivot table shares a subset of items with a custom list, it's appearing in the order of the custom list for those items.
So, Orlando, Jacksonville, and then Daytona and Cocoa Beach happened to be in this completely unrelated custom list, and so it ordered them in that order, and then everything else just went alphabetically at the end.
Isn't that wild?
Okay, so, let's see.
If I really had this problem, if this was really my problem and this was just a random stray custom list that I created from a seminar, and I don't need this list anymore, then I would delete the list.
That would be my first choice.
If I needed to keep this list for some other reason, like, I've…you know, this was a different list of locations and I frequently had to fill the list, Orlando, grab the fill handle and drag to get the list of those cities, and I still needed that, well, then I would be very tempted to do this.
I would take the original data, CONROL+C, come out here to the side, CONTROL+V. On the DATA tab, REMOVE DUPLICATES, click OK.
That gives us just the unique list of items in this list.
I would bold the headings.
That way I can sort with a single click.
We now have the data alphabetically, and I would do these steps.
I would select this list, FILE, OPTIONS, ADVANCED, again down to EDIT CUSTOM LISTS, and import that list as a new list, alright, and then I would come back up, remove the original list, DELETE, click OK, but we still needed that list, right?
Well, then, I'm going to say I want to also import the original list here.
Click IMPORT, and what that's done now is I have 2 lists.
I have the Cocoa Beach order first…list appearing first, and then Orlando, Jacksonville, Miami, and I think the way that they do this is they use the first custom list that they find.
Alright.
So, let's get rid of this slicer, let's get rid of the pivot table, get rid of our stray lists here, and come back to the original data.
Alright.
Let's INSERT a PIVOT TABLE, EXCISTING WORKSHEET, click OK, put LOCATION on the left-hand side, and say…it's still coming up in some relatively random sequence.
Let's go to MORE SORT OPTIONS.
We’ll say ASCENDING BY LOCATION, also click MORE OPTIONS, uncheck SORT AUTOMATICALLY, and then we can go in and specifically choose the custom list.
I can see how this would be a really, really maddening situation if it just starts randomly happening to you.
You set up the custom list months ago, years ago, forgot it was there, and because it has some intersection of the items in your pivot table today, it is causing all of these problems.
So, Justin, I feel your pain.
Everyone else, remember this.
This is if it ever happens to you randomly or if you actually want to control the order of the items in the list.
Hey, by the way, this works for regular Excel pivot tables, not OLAP pivot tables, not Power Pivot pivot tables.
Power Pivot and OLAP don't respect the custom lists.
Also, hey, Justin.
You know, there's another great way to go.
Just take this original data, make it into a table with CONTROL+T. If you have Power Pivot, run it through a Power Pivot, and then it will sort automatically alphabetically.
There we go.
We can take advantage of that.
Usually annoying feature in Power Pivot but it'll work to your advantage here.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
