So before we learn how to custom sort a Pivot table in Excel. Let's establish the basic meaning of custom sorting in Excel.
In Excel by default, the sorting is done numerically/alphabetically. For example, if you have a column with the name of months and you sort it, by default it will be sorted alphabetically (Apr,Feb,Jan…) instead of the order of the months. The same goes for pivot tables.
If you want to manual sort pivot tables with your own custom order, you need to tell that order to Excel. We had enough of the theory. Let's roll with an example.
Most businesses start their financial year in April and end in March. What we need to do is to custom sort our pivot table so that the report shows March as the first month, April as Second and so on.
So first create a pivot table by month. I have a pivot table that shows the sales done in different months.
Currently the pivot table is sorted in ascending order of months (because I have stored a list of months).
To custom sort my pivot table, I need to define the list. So in a range I write the order of the months I need.
Now, to add this list now in excel, follow these steps:
Click on File. Go to option. Click on Advanced Options. Find the general category. Click on Edit Custom List.
The custom list dialog will open. You will see some predefined lists here. As you can see we already have Jan, Feb, Mar… listed as a custom list. This is why my report is sorted by month.
Now to add to our own custom list here, click on the input box below and select the range that contains your list. Then click on import.
Note that we have an import button added. This means the list will be static in the system. Making changes later in the range won't affect the list.
Click OK and get out of the settings.
Now try to sort the list by month.
You can see that the report is now sorted according to our given list instead of the default sorting method.
If that doesn't work, make sure that you have enabled sorting through the custom list for the Pivot table. To confirm this, do this:
Right click on the pivot table and click on Pivot Table Options. Click on the total and filters tab in the open dialog box. Find the Totals and Filters tab. Check the Use Custom List when sorting options.
And it is done.
Now you are ready to roll out your pivot report with custom sorted data.
I hope this was explanatory enough and served your purpose of being here. If you still have any doubt or questions regarding this topic, feel free to ask. You can ask questions related to any Excel/VBA topic, in the comments section below.
Related Articles:
Subtotal grouped by dates in pivot table in Excel : find the subtotal of any field grouped by date values in pivot table using the GETPIVOTDATA function in Excel.
Pivot Table : Crunch your data numbers in a go using the PIVOT table tool in Excel.
Dynamic Pivot Table : Crunch your newly added data with old data numbers using the dynamic PIVOT table tool in Excel.
Show/hide field header in the pivot table : Edit ( show / hide) field header of the PIVOT table tool in Excel.
How to Refresh Pivot Charts : Refresh your PIVOT charts in Excel to get the updated results without any problem.
Popular Articles:
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the VLOOKUP Function in Excel: This is one of the most used and popular functions of excel that is used to look up value from different ranges and sheets.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
