Pivot Table Problem
Posted by Barry on June 15, 2001 5:19 AM
I have a pivot table to summarize departmental expenses. The rows are account codes, the columns are months with a total column and the data is expenditure.
It works well but I would like to have the columns in the order Jan, Feb, Mar etc. To do this I have to substitute a month number i.e. 1 for jan,2 for Feb otherwise it sorts alphabetically.
Is there any way round this please.
Posted by Barrie Davidson on June 15, 2001 9:45 AM
Can you change your pivot table's underlying data to date format, instead of text, for the month? You could then format the date field (in your pivot table) to custom "mmm" to display the month as Oct, for example, and Excel will sort properly.
Barrie
Posted by Mark W. on June 15, 2001 12:18 PM
Barry, PivotTables recognize text representations
of months as ordered sets. I suspect that you
sorted the Row field using either the Sort toolbar
button or the Data | Sort... menu command. Perhaps
the easiest way to recover from this unwanted sort
is to double-click your PivotTable's 'Month' field
button, press the PivotTable Field dialog's
[ Advanced... ] button, choose the "Ascending"
AutoSort option, and press [ OK ]. Alternately,
you could choose the Data | Sort... menu command,
press [ Options... ], change the "First key sort
order" drop-down selection to "Jan,Feb,Mar...",
press the [ OK ] button, and [ OK ] once again.
