Kasper de Jonge Video

It’s true! honestly :-)Thanks for the link.

There are a few solutions to the problem of sorting, although none are perfect:

1) Manually drag the members in the order you want. This can be quite tedious if you have a lot of distinct members. Also, if you remove the field from the PivotTable and then subsequently add it, you will have to start over.

2) Use an Excel Custom List. The custom list can be applied to sorts and fills. Excel is equipped with custom lists for days of the week and months of the year. You can use a month custom list in cases where you have a common key field in the tables(which allows you to use month instead of month-year in the PivotTable).

3) Create a new custom list. In the current scenario, we need month-year for table relationships. I created a list from January 2001 to December 2020 (by then we should have a better solution :))

As with (1), you lose the custom sort order under the condition mentioned (and perhaps under other conditions that I haven’t yet investigated). However, it takes less time to reapply the custom sort than it is to drag members manually.

How you apply a custom list for sorting in the PivotTable is *not* obvious. The process is as follows:

1) Select More Sort Options from the field’s sort and filter drop-down menu(CalendarMonth in this case).

2) In the Sort dialog box, click the More Sort Options button.

3) In the More Sort Options dialog box, clear the AutoSort check box and select the custom list from the First Key Sort Order drop-down list. Click OK to return to the Sort dialog box.

4) In the Sort dialog box, choose the Ascending option and select the appropriate field (e.g. CalendarMonth) from the drop-down list. Click OK. Whew!