Pivot Chart not sorting by date

Question

Hi,&nbsp;I cannot get the horizontal axis in the chart picture to display in date order.&nbsp;&nbsp;I have tried around 10-15 differnt fixes suggested on similar threads, if someone could let me know where I'm going wrong that would be amazing! I've attached the file if that helps.&nbsp;CheersJon

hansvogelaar · Answer

JonGreen&nbsp;
The problem is that the field (column) you used in the Rows area uses the TEXT function, so it is a text value that is sorted in alphabetic order.
Use the Date field instead, and group it by Years and Months.
See the attached version
(I also cleared the check box to reverse the order of the x-axis)

jongreen · Answer

HansVogelaar&nbsp;brilliant! Thank you so much for helping.&nbsp;In the version you sent back though, all data from Sept-Nov is missing? Any ideas?

hansvogelaar · Answer

JonGreen&nbsp;
The Source Data of the pivot table was set to 'Edited data'!$A$1:$R$5797. It should have been 'Edited data'!$A$1:$R$6421. If you change this, the missing months will appear.

jongreen · Answer

HansVogelaar&nbsp;thank you, but now the first change you made has reset and I can't duplicate what you did.&nbsp;How have you created the 'Years' field to add to Rows and display date order rather than alphabetical?&nbsp;

hansvogelaar · Answer

JonGreen&nbsp;
I used the Date column instead of the Month/Year column in the Rows area of the pivot table.
I then right-clicked any of the dates in the pivot table and selected Group... from the context menu.
I selected only Months and Years in the dialog:
&nbsp;

&nbsp;
Confirm by clicking OK. this adds the Years field in the Rows area.
Since Date is a real date column instead of a text column, it is automatically sorted correctly.
See the attached version.

Forum Discussion

Pivot Chart not sorting by date

6 Replies

Resources