Pivot tables can be sorted in several ways - by rows, by columns, and by value. In today’s Exceljet tip, we show you how to sort a pivot table with a custom list.
Excel has the ability to sort using custom lists, and you can use these same lists to sort your pivot tables in a custom order.
Let’s take a look.
Before we sort out pivot table using a custom list, let’s first review how to sort by a custom list generally. Custom lists are useful when you want to order a list into a sequence that is not alphabetical.
For example, here we have a list of four regions. We can easily sort these regions alphabetically in ascending or descending order.
But what if we want to sort them from west to east. In that case, we need them to appear in this order: West, Midwest, South, and East. Let’s create a second list in that order.
Now we have our custom list, but to be able to use it for sorting, we need to define it properly as a custom list. To do that, navigate to the custom list area at File, Options, Advanced. In the General, section, click the Edit Custom lists button.
Next, select the list on the worksheet, and click the Import button. You’ll see the new list appear to the left. Click OK twice to return to the worksheet.
Now, when we used advanced sorting, we can use our custom list to sort
Back in our pivot table, we need to make sure that custom lists are enabled.
Right click anywhere in the pivot table, and select PivotTable options. Then select the Totals and Filters tab, and make sure that Use Custom lists is checked.
Now when we select a region and sort, the pivot table automatically sorts regions in the order defined in our custom list.
And if we clear and rebuild out pivot table from scratch, regions are sorted by the custom list we created automatically.