Sort Row label contents in Pivot Table

Canine

New Member
Joined
Apr 28, 2011
Messages
7
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,592
Messages
6,179,786
Members
452,942
Latest member
VijayNewtoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top