Sort Row label contents in Pivot Table

[Canine]

Hi All,

My row label format is "month, year". When i created the pivot table in excel 2010 the 1st time, months displayed correctly, e.g. jan, 2010 and then feb, 2010 etc. But next time it displayed alphabetically.

I tried a lot of things, but couldn't find the option sort newest to oldest.

I am not sure what to do.

Can anyone help me please.

 

[Jerry Sullivan]

Hi and Welcome,

If you have any items in that field that Excel interprets as Text instead of a Date, Excel takes away the option to sort or filter by Date.

You might check to see if any of your data for that field as not a true date.
You can also check to make sure your source data for the PivotTable doesn't extend beyond your actual data since null text ("") can be interpreted as Text.

Good luck - I'd be interested to hear what you discover.

 

[Canine]

Thanks for replying. I think what you mentioned is one reason why i am not able to sort it.

I actually faced this problem the third time. That's when i started luking how to sort. First 2 times the row labels appeared in the right order.July 2009, August 2009 etc. So i assumed excel has intelligence.

Then a couple of days back this problem started, when i recreated the pivot table. After trying many things, all by itself, the problem got solved. i seriously dont know what happened. Now the problem re-appeared.

Did any of you face such problems? I mean without any effort from my side, it appeared in the right sorted way. Then problem came. Then problem solved. Again problem.

Apologize for the verbosity of my mail. But this is actually very mysterious.

The number category is "general" for each cell in the row label. I am actually trying to plot something on month as x-axis to follow trend. and the months are all crooked sometimes.

Any ideas?

 

[Jerry Sullivan]

The number category is "general" for each cell in the row label.

Have you tried changing the number format a Date Format and then specifying the fomatting IE "MMMM YYYY"? That way, Excel will store the value as (date) number instead of text and you should get your ability to sort Earliest-Latest back.

You might also test creating a new PivotTable after changing the formats just to isolate if the problems with the sort order are due to previous steps you have taken.

 

[Canine]

i can't change the source data format... and i am not able to also, for testing purposes.

 

[Jerry Sullivan]

i can't change the source data format... and i am not able to also, for testing purposes.

Are you saying that you aren't allowed as part of your workflow/ permissions...or that you don't know how to do that?

 

[Canine]

yes and no. I know how to do it. But no matter what i do, cell value just wont change.

i copied few values to another sheet and did this format change. But these values just remain same.

 

[Jerry Sullivan]

yes and no. I know how to do it. But no matter what i do, cell value just wont change.

i copied few values to another sheet and did this format change. But these values just remain same.

I'm not sure that I'm following whether you are referring to the cells in your source data or your Pivot Table.

I'd suggest you make a copy of your Source Data worksheet and then replace some or all (depending on how long your list is) of the Text values with Dates.

Change:
September 2009 to 09/01/2009
October 2009 to 10/01/2009
...etc.

Then select those cells > right click > format cells > number tab > Date > Choose one of the formats shown like Mar-01

If that works, you can work your way back to changing your source data to match the previous format.

 

[Canine]

Hey thanks a lot. That's exactly what i am going to do. I will use another column with correct date format and use it in hidden form and match it with my source data, which i can't touch.

Thanks a lot again.