Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the time, when you sort Pivot Table data, you can use the standard methods to sort the values in alphabetical or numerical order. There will be times though when you want to define a custom list of values and sort a Pivot Table using those values. For example, if your company has stores in four regions: North, South, East and West, and you want the regions to show in that order within a Pivot Table instead of an alphabetical order, you can create a custom list and use that list to sort your field's values. As an example, here is the Pivot Table that contains sales broken up by region: North, East, West and South, except that the regions are sorted into alphabetical order starting with East and going on through West.
If you want to create a custom list of values and sort using those values, first you go to Excel>Preferences and in the Excel Preferences dialog box, click Custom lists. You can use the Custom lists page of the Excel Preferences dialog box to create your new custom list. To do that you start typing in the List Entries box and I will type North. Hit Return. South. Return. East and West, which is my desired order. If I already had a vertical group of cells somewhere in the worksheet that contained the values in the order that I wanted for my list, I could import the list from those cells.
To do that, I would use this RefEdit control. I would click it. Go out into there worksheet. Select the cells that contain the values. The address of those cells would appear in this box. And then I could click Import and those values would appear here in the List Entries box. But I already have the values I want, so I can just click Add. And my new list appears here in the Custom lists pane. I am done, so I can click OK. To sort the Pivot Table using the custom list I just created, I can click any cell in the regions field and then on the Data menu, click Sort, and then click Options.
In the Sort Options dialog box, I can select my First key sort order. Right now the First key sort order is set to Normal, which is alphabetical because I have text values in my region column. If I click this arrow, I can select one of my custom lists. The list I just created is North, South, East and West. So when I click that, I'll be able to use it. I would also like to point out the other existing custom lists that you can use. You have abbreviations for weekdays, the weekdays spelled out, abbreviations for months and the months spelled out.
If you sort the months using either of these two lists then you'll get the order in which the months occur within the year as opposed to an alphabetical sort. But in this case, I want to use my new list, North, South, East and West. Click that. It appears in the Sort Order box. Click OK and click OK again, to sort using that list. And Excel has now sorted my Pivot Table data in the Region column in by North, South, East, West. Custom lists help you arrange your Pivot Table data to emphasize the elements you feel are most important, making sorting a more powerful and useful tool than ever before.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74776 Viewers
80 Video lessons · 129857 Viewers
52 Video lessons · 64061 Viewers
59 Video lessons · 49851 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.