Quite often we need Customized Sorting for our lists, pivot tables and slicers.
Being an accountant and working with Financial Statements I need it every other day.
For example take a look at Income Statement of Microsoft below. The Structure of Income Statement does not follow an ascending or descending order rather it follows a logical structure which is prescribed by Accounting Standards. It starts with Revenue and ends with Net Income
You can download this file here
If I use a pivot table to create this Income Statement, the pivot table would automatically sort the Income Statement by ascending order. Moreover the “SLICER” created on the Income Statement would also be sorted in a similar manner.
What I can do is to manually drag the row items to bring the Income Statement into desired format. But this has a cost… precious time wasted… every time when I have to create a pivot table
To make matters worse, the slicer items can not be sorted manually by drag and drop like Pivot Tables.
Fortunately we have Custom Lists to the rescue. We can create our own “Custom List” and tell Excel to sort using those Custom List.
- For Excel 2010 and later, click File > Options > Advanced > General > Edit Custom Lists.
-
For Excel 2007, click the Microsoft Office Button
> Excel Options > Popular >Top options for working with Excel > Edit Custom Lists.
Once custom list is added, you can make your pivot tables and slicers sort according to that list. The steps for pivot table and slicer are depicted in picture below
LIMITATIONS OF CUSTOM LISTS
Although Custom Lists is a useful tool, it is has certain limitations.
First Custom Lists are not stored with the Workbooks but rather they are added to computer’s registry.
This means that custom lists are available for use in other workbooks in your computer. And you do not need to add them each time
Unfortunately this also means that when you share the workbook with others, they would not see these custom lists and if the pivot tables are refreshed in Other PCs all the customized sorting would disappear.
So you have to ask every user to enter custom lists in their PC. Sometimes this wont be practical especially if you are sharing file with many people.
I have a little solution. Why not create a macro that will add custom lists automatically to user’s PC whenever they open the file.
AUTOMATICALLY ADD CUSTOM LISTS TO OTHER USERS’ PCs
Following is the code. Just put inside any standard module. But you will need to save your file in xlsm (macro enabled) format, otherwise code wont be stored.
Sub auto_open()
On Error Resume Next
Application.AddCustomList ListArray:=Worksheets(“Microsoft”).Range(“M34:M46”)
End Sub
Surely you know that you have to change the worksheet name and Range above in orange according to your situation
Second it is such a long path to reach if you want to add or edit a Custom List i.e. (File > Options > Advanced > General > Edit Custom Lists)
And there is no way (or atleast I am not aware of one) to add it to QUICK ACCESS TOOLBAR.
I would love to see the option of “Custom Lists” in the Ribbon under the “Data Tab” amongst the “Sort & Filter” Group items. I mean here somewhere
