Let’s take a look. Before we sort our 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 sort 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 in order 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 the Use Custom Lists option is checked. Now when we select a region and sort, the pivot table automatically sorts regions in the order defined by our custom list. And if we clear and rebuild our pivot table from scratch, regions are sorted automatically by the custom list we created.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
