Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the time when you sort PivotTable data you can use the standard methods to sort the values in alphabetical or numeric order. There will be times though when you might want to define a custom list of values and sort a PivotTable 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, instead of alphabetical order, you can create a custom list and use that list to sort your fields' values. So for this example, I have a PivotTable with sales data for four regions, east north, south, and west, but I want to have them sorted into the order north, south, east and west, instead of alphabetical order like they are now.
To do that we can create a custom list. So click the File tab on the Ribbon to display the Backstage view, and then click Options to display the Excel Options dialog box, and then on the Advanced page scroll down, which I'll do using my mouse with scroll wheel, and then under the General heading, click the Edit Custom List button. Doing so displays the Custom Lists dialog box. Now you can create your list and in this case we will do it by typing it in.
So here in the List Entries bo, we can type North, then press Enter. Each item needs to be on its own line, and we have South, Enter, East, Enter, and West. So all the entries look to be correct and spelled correctly so I can click OK. And then click OK again to close the Excel Options dialog box. And now I can sort the field values based on the new list that I have just created.
Now to sort by row labels, in this case, the regions, into north-south-east-west order, just click any cell that contains one of those labels, in this case East is already selected, and then on the Options contextual tab of the Ribbon, click the Sort button to display the Sort dialog box, and then select the ascending A to Z option by, and then make sure that the region field is selected, and then click the More options button. In the More Sort Options dialog box, clear the Sort Automatically checkbox here at the top, and then under First key sort order click the down arrow and select list that you want to use, in this case North, South, East and West, and then click OK.
Then back in the Region dialog box click OK again, and when you do Excel applies the sort using your custom list. Custom lists help you arrange your PivotTable 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 · 64863 Viewers
80 Video lessons · 124401 Viewers
52 Video lessons · 60325 Viewers
59 Video lessons · 46149 Viewers