Why does our employee May keep sorting to the top of the pivot table?
By default, pivot tables will follow the sort pattern of any custom lists.
If you employ people named June or May or Wednesday, they will sort to the top of the list.
File, Options, Advanced. Scroll all the way down. Edit Custom Lists.
The names in the first four series can not be removed.
I used to joke that one solution is to fire all the people named April or June.
Right-click the pivot table and choose Options.
Go to the Totals & Filters tab.
Uncheck Use Custom Lists when sorting.
Download the file to work along: https://www.mrexcel.com/download-center/2018/05/pivot-table-no-custom-sort.xlsx
Read the article: Pivot Table No Custom Sort
By default, pivot tables will follow the sort pattern of any custom lists.
If you employ people named June or May or Wednesday, they will sort to the top of the list.
File, Options, Advanced. Scroll all the way down. Edit Custom Lists.
The names in the first four series can not be removed.
I used to joke that one solution is to fire all the people named April or June.
Right-click the pivot table and choose Options.
Go to the Totals & Filters tab.
Uncheck Use Custom Lists when sorting.
Download the file to work along: https://www.mrexcel.com/download-center/2018/05/pivot-table-no-custom-sort.xlsx
Read the article: Pivot Table No Custom Sort
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2211: Pivot Table, No Custom Sort.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question: Why are my Pivot Tables sorting in a weird sequence?
Alright, so we have a list of employees here, and they're all on a first-name basis-- Adam, April, Claire, Della-- we insert a Pivot Table: Insert, PivotTable, OK-- put Names down the left-hand side, and Sales, for some reason, January and April are coming to the top, then followed by Gary, Otto, Paul, May, June, and then everyone else, alphabetically.
What's going on here?
Well, I know exactly what's going on here, and in lots of times I use this on purpose.
Let me put Region in, and you'll notice that Region shows up: East, Central, West.
Why does it show up, East, Central, West?
Because I set up a Custom List: File, Options, Advanced, scroll all the way to the bottom, Edit Custom Lists, and see?
I have a list-- East, Central, West.
And this list allows me to use the Fill Handle to fill this list, it allows me to Sort the data into this list-- you have to go into Data, Sort, and then choose the third drop-down Custom List and, automatically, Pivot Tables will sort into this list.
That's beautiful, right?
I want that to happen.
But, in the case where we have Employee instead of Region-- and we happen to have some employees named January, or April, or May, or June, or Wednesday-- they will soar to the top of the list.
Now, why Gary, Otto, and Paul?
Because I create fake data all the time, and so I have a custom list that starts out with Andy and has 26 names in there, and it happens that Gary, Otto, and Paul happen to be in my list.
If we would have had an employee named Wednesday or Tuesday, they would have sorted to the top of the list as well.
Although, the one thing I really cannot explain here-- and I try and understand how Excel is working all the time-- but, in this one particular case, I can't quite figure out the pattern because January and April are in this list here-- the fourth list from the top-- and if I'd just typed in January and grabbed the Fill Handle and dragged-- or, let's say I typed in Jan and grabbed the Fill Handle and dragged-- it's going to use the one nearest to the bottom, right?
So, this one with the extra word "Total" is going to get used instead of this one near the top of the list.
Alright, so January and April are in this list, and then-- here, where's Andy, right here-- so, here's Gary, and Otto, and Paul in that list.
But May and June-- well, May is in this list, and June is in this list.
Right.
So, why it's mixing items from this list, this list, and this list-- I don't have an explanation for that.
I would have expected it would have chosen one list and used everything from that list, you know, or something, right?
Why January and April-- and then June all the way down here?
That makes no sense to me at all.
But, anyway, that's not the point of the episode.
You tuned in to figure out, "How do we get Pivot Tables to happen without a custom sort?" So, here's what you have to do: To change just this Pivot Table, right-click, go into PivotTable Options, go to the second tab called Totals and Filters, and un-check this box "Use Custom Lists when sorting." Now, that doesn't fix it.
And, even removing Employee and putting Employee back doesn't fix it.
You have to go here to this drop-down and say Sort A-to-Z, and that will fix it.
But, what if you never want this to happen, right?
You do have someone named Jan, and they are always sorting to the top of the list.
Well, you can turn it off permanently.
Go to File, Options, Data-- now, this is brand new in Office 365, came along in 2017-- Edit Default Layout.
Go to PivotTable Options, go to Totals and Filters, and un-check "Use Custom Lists when sorting." If you don't have this choice, well, it's time to upgrade to Office 365.
There's really no reason to have Excel 2016, or Excel 2013, or Excel 2010.
You want to get the latest and greatest version-- new features every month, and they're really giving us a lot of great new features every month.
It's actually cheaper to go with Office 365 than to pay $400 once every three years.
So, you know, there's no good reason for that at all.
Pivot Tables, Custom Lists, all covered in my book MrExcel LIVe, The 54 Greatest Excel Tips Of All Time.
Click that "I" on the top right-hand corner to learn more about the book.
Alright, wrap-up today: Why does May or Jan keep sorting to the top of the Pivot Table?
That's because Pivot Tables follow the Sort Pattern of any Custom Lists, and those month names or weekday names are in the Custom Lists.
So, if you employ people named June or May or Wednesday, they're going to sort to the top of the Pivot Table.
Where can you see your Custom Lists?
Go to File; Options; Advanced; scroll all the way down; Edit Custom Lists.
But, hey, those first four series, the ones with the month names and the weekday names?
Those can't be removed.
You're not allowed to edit those; Those are built in.
Alright, so your one solution is just to fire all the people named April or June orr you can right-click the Pivot Table and choose Options; go to Totals and Filters; uncheck "Use Custom Lists when sorting." Or, if you have Office 365, just turn it off for every future Pivot Table-- File; Options; Data; Pivot Table Defaults; go into Pivot Table Options and turn it off once.
It'll be off for all future lists.
Well, hey, to try this -- to download the workbook from today's video -- visit the URL in the YouTube description.
I want to thank you for stopping by; I'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question: Why are my Pivot Tables sorting in a weird sequence?
Alright, so we have a list of employees here, and they're all on a first-name basis-- Adam, April, Claire, Della-- we insert a Pivot Table: Insert, PivotTable, OK-- put Names down the left-hand side, and Sales, for some reason, January and April are coming to the top, then followed by Gary, Otto, Paul, May, June, and then everyone else, alphabetically.
What's going on here?
Well, I know exactly what's going on here, and in lots of times I use this on purpose.
Let me put Region in, and you'll notice that Region shows up: East, Central, West.
Why does it show up, East, Central, West?
Because I set up a Custom List: File, Options, Advanced, scroll all the way to the bottom, Edit Custom Lists, and see?
I have a list-- East, Central, West.
And this list allows me to use the Fill Handle to fill this list, it allows me to Sort the data into this list-- you have to go into Data, Sort, and then choose the third drop-down Custom List and, automatically, Pivot Tables will sort into this list.
That's beautiful, right?
I want that to happen.
But, in the case where we have Employee instead of Region-- and we happen to have some employees named January, or April, or May, or June, or Wednesday-- they will soar to the top of the list.
Now, why Gary, Otto, and Paul?
Because I create fake data all the time, and so I have a custom list that starts out with Andy and has 26 names in there, and it happens that Gary, Otto, and Paul happen to be in my list.
If we would have had an employee named Wednesday or Tuesday, they would have sorted to the top of the list as well.
Although, the one thing I really cannot explain here-- and I try and understand how Excel is working all the time-- but, in this one particular case, I can't quite figure out the pattern because January and April are in this list here-- the fourth list from the top-- and if I'd just typed in January and grabbed the Fill Handle and dragged-- or, let's say I typed in Jan and grabbed the Fill Handle and dragged-- it's going to use the one nearest to the bottom, right?
So, this one with the extra word "Total" is going to get used instead of this one near the top of the list.
Alright, so January and April are in this list, and then-- here, where's Andy, right here-- so, here's Gary, and Otto, and Paul in that list.
But May and June-- well, May is in this list, and June is in this list.
Right.
So, why it's mixing items from this list, this list, and this list-- I don't have an explanation for that.
I would have expected it would have chosen one list and used everything from that list, you know, or something, right?
Why January and April-- and then June all the way down here?
That makes no sense to me at all.
But, anyway, that's not the point of the episode.
You tuned in to figure out, "How do we get Pivot Tables to happen without a custom sort?" So, here's what you have to do: To change just this Pivot Table, right-click, go into PivotTable Options, go to the second tab called Totals and Filters, and un-check this box "Use Custom Lists when sorting." Now, that doesn't fix it.
And, even removing Employee and putting Employee back doesn't fix it.
You have to go here to this drop-down and say Sort A-to-Z, and that will fix it.
But, what if you never want this to happen, right?
You do have someone named Jan, and they are always sorting to the top of the list.
Well, you can turn it off permanently.
Go to File, Options, Data-- now, this is brand new in Office 365, came along in 2017-- Edit Default Layout.
Go to PivotTable Options, go to Totals and Filters, and un-check "Use Custom Lists when sorting." If you don't have this choice, well, it's time to upgrade to Office 365.
There's really no reason to have Excel 2016, or Excel 2013, or Excel 2010.
You want to get the latest and greatest version-- new features every month, and they're really giving us a lot of great new features every month.
It's actually cheaper to go with Office 365 than to pay $400 once every three years.
So, you know, there's no good reason for that at all.
Pivot Tables, Custom Lists, all covered in my book MrExcel LIVe, The 54 Greatest Excel Tips Of All Time.
Click that "I" on the top right-hand corner to learn more about the book.
Alright, wrap-up today: Why does May or Jan keep sorting to the top of the Pivot Table?
That's because Pivot Tables follow the Sort Pattern of any Custom Lists, and those month names or weekday names are in the Custom Lists.
So, if you employ people named June or May or Wednesday, they're going to sort to the top of the Pivot Table.
Where can you see your Custom Lists?
Go to File; Options; Advanced; scroll all the way down; Edit Custom Lists.
But, hey, those first four series, the ones with the month names and the weekday names?
Those can't be removed.
You're not allowed to edit those; Those are built in.
Alright, so your one solution is just to fire all the people named April or June orr you can right-click the Pivot Table and choose Options; go to Totals and Filters; uncheck "Use Custom Lists when sorting." Or, if you have Office 365, just turn it off for every future Pivot Table-- File; Options; Data; Pivot Table Defaults; go into Pivot Table Options and turn it off once.
It'll be off for all future lists.
Well, hey, to try this -- to download the workbook from today's video -- visit the URL in the YouTube description.
I want to thank you for stopping by; I'll see you next time for another netcast from MrExcel.
