Sorting pivot tables - text / date confusion

[malcomio]

I have a pivot table of students taking courses, which I need to sort by
surname. One of the students' surname is May, and Excel always puts this at
the top of the list, before Adams & Anderson etc. It's the same if I change
the source data so that the person's name is April or December.

I've tried formatting it as text, but this doesn't seem to have any effect.

Any ideas gratefully received.

 

[Debra Dalgleish]

May goes to the top of the list because Excel assumes it's the month
name, and it appears in one of the built-in custom lists.

When you create a pivot table, and Excel detects an entry from a custom
list, it uses that list as the sort order. So, in your table, May sorts
to the top, and the other items appear below, because they're not in the
Months custom list.

If you manually sort the list, May should move to the correct position
alphabetically. To manually sort the list, select a cell in that field,
and click the A-Z button the toolbar/

In Excel 2007, you have the option to turn off this setting.

 

[malcomio]

Thanks Debra, but manually sorting the pivot table doesn't do it either.

This is in Excel 2007, but using compatibility mode because I'm sending the
report to someone who uses Excel 2003.

Where is the option to turn off the custom lists? I can't find it anywhere.
Also would that mean that I couldn't autofill when I need to write a list of
months?

 

[Debra Dalgleish]

No, it's a setting that only affects the specific pivot table. All the
fields will be affected. To change it:

Right-click a cell in the pivot table, and click PivotTable Options
Click the Totals & Filters tab
Under Sorting, remove the check mark from Use Custom Lists when sorting,
then click OK.

 

[malcomio]

Thanks Debra that's done the trick