Hello, welcome back for another #Excel tip. Today let’s take a look at how to use Custom Sorting in a Pivot Table. This is one tip I use all of the time, so I really wanted to share it with my subscribers.
If you need to sort your Pivot Table by a sequence other than say Numeric or in Date order then this will be very useful. I need to sort my Pivot Tables on a regular basis by the products in order of importance to the business. You can see a sample of raw data and the Pivot Table below. I have also included the preferred sorting order of the data.
You can already see that the order of sorting preference cannot fit into any regular sorting order. I cant use any dates or any alpha numeric sorting options. We need to create one ourselves.
So, in order to sort our Pivot Table data in the correct order, we need to use a Custom List. This requires an extra step just once to set up the list. This ensures we do not have to manually sort the Pivot Table like below, clicking and dragging the product names. This is very time consuming if you have to repeat this over a number of Pivot Tables.
Defining Your Custom List.
First, we will need to Define The Custom List your sorting will be based on. Just follow the steps below, it is straightforward.
- Edit Custom Lists
Once the Edit Custom Lists dialog box is opened there are two ways to enter your Custom List. You can
1. Manually type in the items in the order you want to sort
2. Use the dialog box or range selector to select the cells that contain your items.
Both are effective, but I do tend to use the range selector if I have more than just two or three items.
Now it is time to either build you Pivot Table if you have not already built it.
If the Pivot Table is not built before the custom list is created then the product name will automatically be in the correct sequence. If your Pivot table is already built then you need to sort the Pivot Table with an A to Z sort. This will give you the correct sequence.
Job Done. No manually moving things around the Pivot Table manually again!.
Recommended Pivot Table Course.
If you want to find out more about Pivot Tables and looking for a course. This is my personal recommendation from my training resources. Just Awesome.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.