Excel: Wired sorting results in Pivot Table and solution

Summary

You may see that the sorting on text column going strange like below.

The Left side is a normal data range. The right side is a pivot table that with source from A1:A7

Possible Cause

The problem has been caused by sorting with "custom list" under PivotTable.

The "Custom List" is used to instruct the Excel the sorting order of a list of items.

An example custom list: "JAN", "FEB", "MAR"

should be sorted as "JAN", "FEB", "MAR", but not alphabetically  "FEB", "JAN", "MAR"

There are two types of "custom List", one is Excel built-in (including Months,Day of weeks...), another type is user defined.

The "Custom List" could be reviewed or adjusted with following location:

Excel 2010:  File->Option->Advanced->Edit Custom Lists (General Section)

Excel 2007: Office button->Excel Options->Popular-> Edit Custom Lists (button)

Excel 2000/XP/2003: Tools->Option->Custom Lists (Tab)

Symptoms

Assume that we feeding a list of 3 alphabet into a pivottable to sort: "AAA","AAB"..."AAZ","ABA"...."ZZZ" 

The "sorted" result from Pivottable would become this:

You could see the list is started with "Months" JAN but not AAA

Resolution and Workaround

Excel 2007/2010

A. To switch off the custom list sorting on pivot table

1. Right Click the PivotTable-> PivotTable Options
2. Totals & Filters-> remove the check box from "Use Custom Lists when sorting"

B. To switch off the individual column custom list ordering

1. Right Click the Field required -> Sort->More Sort Option
2. In the "Sort Options", Make sure "Ascending" or "Decending" selected.
3. Click on the "More Options" Button
4. Remove the checkbox: "Sort automatically every time the report is updated"
5. Select "No Calculation" in first key sort order
6. Press OK twice

Excel 2003

C. To switch off the individual column custom list ordering

1. Right click the Field required, go to Data Menu->Sort
2. Click on Options button, select "normal" in custom sort order
3. Press OK twice