Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting pivot tables - text / date confusion
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting pivot tables - text / date confusion
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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting pivot tables - text / date confusion
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting pivot tables - text / date confusion
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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting pivot tables - text / date confusion
Thanks Debra that's done the trick
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting in Excel 2000 Pivot Tables | Excel Discussion (Misc queries) | |||
pivot tables by date range | Excel Discussion (Misc queries) | |||
Sorting in pivot tables | Excel Discussion (Misc queries) | |||
Using Date Grouping in Pivot Tables | Excel Discussion (Misc queries) | |||
Sorting data in Pivot Tables | Excel Discussion (Misc queries) |