Display Pivot table summary in order of increasing time frame

[Hari]

Hi,

In my base data I have fields for Year, Month, Date, Field1 and a few
measures associated with them. New data for succeeding dates/months are
appended below the earlier dates/months.

One of my former colleagues had made a Pivot table out of the above
data and each time when it was refreshed it used to have the
months/year displayed in increasing order of time without involvement
of any macro etc. (I couldnt figure out as to how Pivot could manage
that as with some of my other data, the time periods always used to be
displayed in alphabetical naming order rather than increasing months).

Presently with data entered for November (and partial data for
December) in the above base data table and refreshing pivot, November
and December make to the top of the summary for that year. Iam
attaching the sample diplay below. Also there is a Pivot chart linked
to this table and thats also getting muddied up.

I want to understand as to how I can achieve Pivot to display data in
natural order of time (without using an macros or mutliple
manipulations) Or at the vey least Pivot should be able to Present
summaries in the order in which values for the row fields appear in the
base table.

Regards,
HP
India

Fiscal Year Month Field 1
2005 July 39
August 319
September 383
October 521
2005 Total 1,262
2006 November 11,920
December 3,980
January 5,635
February 6,186
March 7,686
April 7,682
May 6,857
June 6,230
July 6,505
August 3,483
September 4,930
October 8,289
2006 Total 79,383
Grand Total 80,645

 

[Roger Govier]

Hi Hari

Tools>Options>Custom Lists> delete the custom list which exists starting
Nov,Dec, ..... Oct
Create a New one with Jan,Feb,Mar .... Dec
Refresh your Pivot table.

 

[Debra Dalgleish]

Right-click on the Month field button
Choose Field Settings
Click the Advanced button
For AutoSort options, select Ascending
Click OK, click OK

 

[Hari]

Debra,

Thanks a TON. It works very nicely. Does the word "Ascending" mean in
the order of data being entered for that field in base data or does it
mean Ascending in terms of natural association order for that field. If
second case I dont understand as to how Pivot would
undertsand/interpret the natural orders for String values (based on
custom list in Options?)

I have one additional doubt. In the pivot drop-down for month field, I
was having some old items which is no longer in my base data. From your
web-site http://www.contextures.com/xlPivot04.html I first tried the
manual method but it didnt work. On the other hand the
"Programmatically Clear Old Items -- Excel 2002 and later" worked well.
Any idea why manual didnt work.

Regards,
HP
India

 

[Debra Dalgleish]

You're welcome! At the bottom left of the PivotTable Field Advanced
Options dialog box there's a dropdown list for 'Using field'. The
default is the field that you're sorting, but you could choose a
different field from that list.

Perhaps there's another pivot table in your workbook that uses the same
pivot cache. For the manual method to work, you'd have to remove the
field from both pivot tables, then refresh.

Debra,

Thanks a TON. It works very nicely. Does the word "Ascending" mean in
the order of data being entered for that field in base data or does it
mean Ascending in terms of natural association order for that field. If
second case I dont understand as to how Pivot would
undertsand/interpret the natural orders for String values (based on
custom list in Options?)

I have one additional doubt. In the pivot drop-down for month field, I
was having some old items which is no longer in my base data. From your
web-site http://www.contextures.com/xlPivot04.html I first tried the
manual method but it didnt work. On the other hand the
"Programmatically Clear Old Items -- Excel 2002 and later" worked well.
Any idea why manual didnt work.

Regards,
HP
India