In Excel 2010 we are able to apply our own custom sort to Pivot Tables.
This is particularly useful when you want to report on financial periods such as Jan, Feb, Mar.
If your financial year begins on Apr, you can create a custom list for this.
Here’s how to do it :
Select the FILE tab from the ribbon and choose Options (just below the Help menu). Choose the ADVANCED option. Scroll to the very end of the dialogue box and look for the Edit Custom Lists button.
Add your list by following the instruction in the Custom Lists dialogue box
Once you have added your custom list close the window.
Here’s how to apply the custom sort to a pivot table. It’s a little quirky so please follow the steps carefully.
You can clearly see the table below is sorted in the wrong order.
Choose the Sort icon from the Data tab on the ribbon.
Select More Options.
In the First key sort order field select your custom list. Press OK to confirm. Press OK again to apply the custom sort order.
Here’s the important step. With a live pivot table the sort order is sometimes lost whenever a slicer is selected or the pivot table is refreshed. To avoid this click the Sort icon again and select More Options again. But this time tick the box to Sort automatically every time the report is updated.
This doesn’t seem intuitive but I assure you the custom sort list is still applied as long as you follow the order of events I have given you. Very odd but it works.