Pivot Table No Custom Sort
May 30, 2018 - by Bill Jelen
Sometimes, if you actually know the name of a feature, it is easy to find how to control that feature. But with today's topic, most people who get stung by this "bug" have no idea why it is happening. They will report a symptom along the lines of: "For some reason, two of our employees keep appearing at the top of our pivot tables". I will often predict that the employee is named June or May or maybe even Friday.
Here is the story: Every copy of Excel starts with four built-in custom lists. Those lists contain the twelve month names, seven weekday names, twelve month abbreviations and seven weekday abbreviations. Custom lists are often used in conjunction with the Fill Handle to quickly add headings across a worksheet.
But custom lists have other uses. You can sort by a custom list. In the Sort dialog, the Sort By dropdown offers Ascending, Descending, and Custom List. Also - you can use a custom list to control the sequence of items in a pivot table.
Because custom lists are useful, many people will create new custom lists. A list can be as short as two or as long as 254 items. You can add custom lists for your product lines or cost centers, or plant locations.
For example, I have a custom list with three region names: East, Central, West. The list has the items in that order. When I create a pivot table, the pivot table will automatically show the regions in East, Central, West sequence, even though the normal sequence would be Central, East, West.
What happens when your company introduced a new South region and you don't update the custom list? Excel will use East, Central, West from the custom list and then show the items not in the custom list:
But let's pretend you haven't created any custom lists. Your version of Excel only has the four built-in lists. There are thirty-eight words that happen to be in those custom lists. If you have a data set with first names, and some of those first names happen to be in the list of 38 words, those names will sort to the top of the pivot table. I don't know many people named Tue or Thu. But I know lots of people named Jan. The problem will happen if your data includes any of these names: Apr, April, Aug, August, Dec, December, Feb, February, Fri, Friday, Jan, January, Jul, July, Jun, June, Mar, March, May, May, Mon, Monday, Nov, November, Oct, October, Sat, Saturday, Sep, September, Sun, Sunday, Thu, Thursday, Tue, Tuesday, Wed, and Wednesday.
Note
During the recording of the video below, I accidentally discovered that the pivot table sorting could come from various lists. Jan from the Month abbreviations and April from the Month names will both sort before another name such as Andy. However, there is no clear rule for which custom lists will sort first. I even checked with Sam Rad on the Excel team and there is nothing defined to handle the mixing of items from multiple lists. Sam suggests if you want to control the sorting of a pivot table, keep all the items in the same custom list.
So - how do you get Jan to stop sorting to the top of a pivot table? After all, you can not delete those built-in custom lists.
- Right-click on the Pivot Table and choose Options.
- In Pivot Table Options, go to the Totals & Filters tab.
-
Unselect the box for Use Custom Lists When Sorting.
Note: This will not fix the already existing pivot table. You need to do step 4 in order to re-sort a pivot table that already exists. - Open the Region dropdown in the Pivot Table. Choose Sort Ascending
If you have Office 365 or Excel 2019, you can change the defaults for all future pivot tables to turn this feature off. Go to File, Options, Data, Edit Default Layout. In the next dialog, click Pivot Table Options and turn off the feature shown above.
Watch Video
Video Transcript
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.
Download Excel File
To download the excel file: pivot-table-no-custom-sort.xlsx
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Live an Excel-lent life"
Title Photo: Alex Block on Unsplash
