Custom sorting in Pivot Table using Custom Lists

This feature has been around for two decades.
It is high time you know it!

Decide the sort order once and use it lifelong.

Estimated reading time 10 min

Sorting Text

This works only with text. Numbers and Dates have their own sorting orders. They don’t need custom sorting.

Text is sorted in alphabetical order – ascending or descending. But that is not enough. We want more customization. Why?

Consider this example. This is a multi-national organization with global presence. This is the report from all continents.

In a pivot table, data can also be sorted in the order of the calculated data item – Sum of Amount. But the requirement is different. The company started operations in Europe. Then it spread to Asia followed by Americas and Africa. This order has to be shown in every report.

Manual reordering is possible

This is neither ascending, descending or by the calculated value. This is a CUSTOM order. How do we do this today? Yes. Pivot Table does allow you to drag individual items to create any order you desire.

The problem is – you have to perform this kind of drag drop for every new pivot table you create.

There is a need to specify the custom order ONCE and be able to use it for sorting across all Excel files. That is called a custom list.

How to create the custom order

First step is to create a custom order manually. This can be done by creating a pivot table and then reordering with drag drop. If the list is very long, it makes sense to copy paste the data to Word (not as a table, just text) and use the Shift Alt Up / Down shortcut to reorder the list quickly. Once reordered, copy it back into Excel.

All of us know that if you drag Jan, Feb and March appear automatically – that is a custom series. Microsoft has already created it. Similarly, we can create our own custom order.

Select the reordered list.

Choose File – Options – Advanced (scroll all the way down) – Edit Custom Lists

The dialog shows existing lists – which we are already familiar with.

Click import to define our new list based upon the selection. Click Ok.

That’s it. Now this list will be available for various things, including custom sorting across all Excel files. It is a global setting.

Using the custom list

Before sort, it is also good for extending the list.

Type Asia and drag. Usually the word Asia will repeat. But now it is a custom list. Therefore the other items appear. The list wraps around and repeats itself if you continue to drag.

 

Any item in the list can be typed and dragged. The list will continue, wrap around and repeat.

This is good in most cases. But what if you wanted to repeat the word Asia?

No problem. By default, dragging will fill the custom list (series). If you don’t want it, click on the icon which appears just below the drag area and choose Copy Cells.

Using custom list while sorting raw data

Notice that the custom list is shown in forward and reverse order for your convenience.

Using Custom List in Pivot Tables

Create the custom lists first. Create the Pivot Table. Custom list will be used automatically.

Pivot Tables understand the presence of custom lists and use these automatically.

This happens due to a setting in Pivot Table options – which is ON by default:
Totals and Filters, Use Custom Lists when sorting.

It is possible that there are new entries which are not included in the custom list. In this case the custom list entries will be sorted first, followed by the missing entries in alphabetical order. This can be very confusing if you don’t know the reason behind it.

If the custom list is outdated, correct it and then delete the pivot table and recreate it.

Overlapping items in Custom Lists

If the same word appears in two or more custom lists, the latest list takes precedence while dragging. Unfortunately, there is no way of reordering the order of these lists themselves!

Deleting custom lists

That is easy. Go to the dialog, select it and delete.

Try it out yourself

Open your data. Go to Pivot. Get a list in alphabetical order. Reorder it. Select it. Import it. Try it. Recreate the pivot table to confirm that the custom list is automatically used.

Teach this to everyone around you who can benefit from custom lists.

Sharing lists is difficult

The lists are stored per person (user profile) in the registry. Therefore it is not possible to share standard custom lists for every user. However, it is possible to use VBA method Application.AddCustomList to create new lists. This macro can then be sent to all users and they can run it to create standardized custom lists.

***