Pivot chart - prevent custom sort from resetting when data is refreshed

T

ToniGo

New Member
Joined
Oct 2, 2013
Messages
25
I have set up a data model with the main source of data being a bunch of CSV files in a particular folder.
Data is grouped by month via the pivot field which works fine.
The only issue is that I need to order the months July-June and every time the data is refreshed they divert back.

When I click on the axis and select more sort options I can see the "More Options" box but it is greyed out.

Hoping there is a simple answer! Otherwise the best idea I have to set up a macro to manually resort if the data is refreshed.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
do you have 12 months with a single data value by each ? if the pivot after refreshing is in the order in in say order jan to dec and you want dec back to jan a simple new table pulling the months in you specified order could prepare the data for the chart.....
 
Upvote 0
[TABLE="width: 951"]
<colgroup><col span="3"><col><col><col span="10"></colgroup><tbody>[TR]
[TD]month[/TD]
[TD]score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jan[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]assuming you want dec to jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]feb[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mar[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Sum of score[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]dec[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apr[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]month[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]nov[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]may[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]jan[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]oct[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jun[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]feb[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sep[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]july[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]mar[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aug[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aug[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]apr[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]july[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sep[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]may[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jun[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oct[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]jun[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]may[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]nov[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]july[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]apr[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dec[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]aug[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mar[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jan[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]sep[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]feb[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]feb[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]oct[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jan[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mar[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]nov[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apr[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]dec[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]may[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jun[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]formula giving 36 for dec is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]july[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aug[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($D$5,MATCH(I4,$D$6:$D$17,0),1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sep[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oct[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]nov[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dec[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
No, I have multiple years with a single entry for each month in each year. So I have it set up as a standalone pivot chart and would prefer not to link it to formula table but that is another option. Ideally I would just like to lock the order in so it stays as I have set it and not change the order once refreshed.
 
Upvote 0
2 options:
Add a custom list with your sort order
Add a calculated field with = month (Table1 [datefield]) and sort on that field
 
Upvote 0
mart37 said:
2 options:
Add a custom list with your sort order
Add a calculated field with = month (Table1 [datefield]) and sort on that field
Click to expand...

That sounds perfect thanks, but I can't see how to sort based on a different list?
 
Upvote 0
So hopefully this might help someone else. I just added a new pivot table in the same tab with a list of the months and was able to change the sort settings on that to not re-sort automatically. Since then my pivot charts have also not re-sorted (even though I didn't connect them). So problem solved.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Data Model Error - Effecting all versions of the same workbook
Replies
0
Views
335
Rollnation3
R
R
  • Question Question
Axis Options - Bounds - Change with Pivot Table values
Replies
2
Views
341
RodneyW
R
M
  • Question Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
3K
Alex Blakenburg
A
D
  • Solved
Change pivot source when generating new workbook
Replies
1
Views
697
GlennUK
G
T
  • Question Question
Pivot Table - Conditional Formatting (or selecting) from a Data Table
Replies
0
Views
731
Timbucktoo
T

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

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