Hello, welcome back for another #Excel tip. Today, let’s look at how to use Custom Sorting in a pivot table. Custom Excel pivot table sorting is one tip I use regularly. So I wanted to share it with my subscribers. If you use pivot tables in Excel, you know they are a great way to summarize data. But did you know that you can customize the sorting of your pivot table? Custom sorting is a great way to organize your data in a way that makes sense to you. In this blog post, we will show you how to do this. Stay tuned!
If you need to sort your pivot table by a sequence other than Numeric or in Date order, this will be very useful. For example, I need to sort my pivot tables regularly 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 sorting preference cannot fit into any regular sorting order. I can’t use any dates or any alphanumeric sorting options. We need to create one ourselves.
So, to sort our Pivot Table data in the correct order, we need to use a Custom List. This method requires an extra step just once to set up the list. But, the extra effort ensures we do not have to sort the Pivot Table like below manually. Instead of a lot of clicking and dragging the product names, the extra fantastic step does the hard work repeatedly.
There are a number of steps to achieve the custom sort or of your pivot table. just follow the steps below. You will get the hang of it.
Step 1. Build The Pivot Table.
If you have not already built the pivot table, then go ahead and do this. If you need further help with creating pivot tables, I suggest the blog post below.
A point to note. If the Pivot Table is not built before the custom list is created, the product name will automatically be in the correct sequence. If your Pivot table is already built, you need to sort the Pivot Table with an A to Z sort. This will give you the correct sequence from the custom list. Why?. Excel saves the custom list within the Excel application, not the workbook. So, this means that the custom sort order will be available every time you open Excel. It, therefore can be used in other workbooks.
Step 2. Define Your Custom List.
Next, we will need to Define The Custom List the pivot table sorting will be based on. Just follow the steps below, it is straightforward.
- File
- Options
- Advanced
- General
- 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.
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 each month. Receive my free Ebook, 50 Excel Tips.
