Excel Pivot Table Report – Sort Data in Row & Column Labels & in Values Area, use Custom Lists
As applicable to Excel 2007
Excel Pivot Tables Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
9. Sort Data in a Pivot Table Report – Sort Row & Column Labels, Sort Data in Values Area, Use Custom Lists
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Sort Data in a Pivot Table Report
You can control the order in which data appears in a Pivot Table report. You can do this by sorting the data in different ways – display data in the same order it appears in the source data; sort items in ascending or descending order based on their names; sort date & time values according to newest to oldest or oldest to newest; sort items in ascending or descending order based on values; manually setting the order in which items appear. This helps in customizing the visual effect of the summarized report for better analysis ex. if you want to view sales figures in the order of highest to lowest, or view data in reverse in the order of newest to oldest by date. On adding a field in a Pivot Table report, items in Row Labels & Column Labels appear in ascending (A to Z) order by default.
Sort Data in Row and Column Labels
Quick Sort from the Ribbon:
You can sort quickly by selecting a cell (in the rows or columns area) in the Pivot Table report and then under the ‘PivotTable Tools’ tab on the ribbon -> click ‘Options’ tab -> in the ‘Sort’ group click on the Ascending or Descending icons (refer Image 1). This sorts text in A to Z (Ascending) or Z to A (Descending) order; sorts date & time values according to Oldest to Newest (Ascending) or Newest to Oldest (Descending); and sorts numbers in the order of Smallest to Largest (Ascending) or Largest to Smallest (Descending). Sorting is done on the selection in the row or column area of the Pivot Table.
You can also do a Quick Sort by right-clicking a cell (in the rows or columns area) in the Pivot Table report, and from the list of commands which opens, point to ‘Sort’ and click on ‘Sort A to Z’ or ‘Sort Z to A’. Another way of doing this is to click on the drop-down arrow of a row or column label which opens a list with ‘Sort A to Z’ or ‘Sort Z to A’ at the top.
The Sort dialog box:
Launch the Sort dialog box:
Launch the Sort dialog box to sort data based on several criteria at once. The dialog box can be launched in many ways: under the ‘PivotTable Tools’ tab on the ribbon -> click ‘Options’ tab -> in the ‘Sort’ group click on ‘Sort’; click on the drop-down arrow of a row or column label and from the list of commands click on ‘More Sort Options’; right-click a cell in the rows or columns area in the Pivot Table report and from the list of commands which opens, point to ‘Sort’ and click on ‘More Sort Options’ in the list. Caption on the top band of the dialog box appears as ‘Sort (<Field Name>)’, where the Field Name refers to the row or column field you have selected to Sort. Clicking on ‘More Options…’ at the bottom left of this dialog box further opens the dialog box of ‘More Sort Options’.
Sorting Options:
In the ‘Sort’ Dialog Box (which is in reference to the row or column field you have selected to sort and that field name appears in the caption of the dialog box in its top band) you can select any one of the 3 sorting options: (i) Manual; (ii) Ascending; and (iii) Descending. The Ascending and Descending sort options will sort Text data from ‘A to Z’ or ‘Z to A’, sort Numbers from ‘Smallest to Largest’ or ‘Largest to Smallest’ and sort Dates and Times from ‘Oldest to Newest’ or ‘Newest to Oldest’. Refer Image 2 which shows sorting for Text values, Date values and Numbers.
(i) Manual: by choosing this option – (a) you can drag items to rearrange them the way you want – to drag, select an item and move your mouse cursor on its borders till a four-pointed arrow is formed and then hold and drag (refer Image 3 to see how the arrow appears); or (b) select an item in the Pivot Table report and type in exactly the other item you wish to replace the selected item with while the rest of the order remains the same. Refer Image 4a which shows the original order of the ‘City’ field – Vancouver, Toronto, New York & Los Angeles. Image 4b shows that Los Angeles is being typed at the top, overwriting Vancouver. Image 4c shows the new order – Los Angeles, Vancouver, Toronto & New York;
(ii) Ascending (A to Z) by: Choose this option and then select a field from the drop-down list which you want to ‘sort by ‘ in Ascending order. The drop-down list includes the relevant row or column area field and all fields in the values area; and
(iii) Descending (Z to A) by: Choose this option and then select a field from the drop-down list which you want to ‘sort by’ in Descending order. The drop-down list includes the relevant row or column area field and all fields in the values area.
Sort Automatically everytime the report is updated:
‘AutoSort’: In the Sort Dialog Box, clicking on “More Options…” at the bottom left opens the dialog box of ‘More Sort Options’. Selecting “AutoSort”, will sort automatically every time the report is updated.
Sort in a User-Defined Order with a Custom List:
‘First key sort order’: Not selecting the AutoSort checkbox will automatically activate “First key sort order” option which enables sorting in a user-defined sort order with a custom list as selected from the drop-down options. Default custom lists available in Excel are the 7 weekdays (Sun to Sat/Sunday to Saturday) and 12 calendar months (Jan to Dec/January to December), or else you can create a custom list on your own, as per steps given below. Refreshing the Pivot report will not retain the custom list sort order. See below for a detailed explanation on “How to Use Custom Lists when sorting”.
Sort by “Grand Total” or ‘Values in selected row/column’:
If sorting of row or column fields is done by using values, ie. when in the Sort Dialog Box you select a value field say “Sum of Sales” in Ascending or Descending options, then you have options to further Sort this value field by “Grand Total” or “Values in selected row” (or “Values in selected column”) in the “More Sort Options” dialog box. If you Sort by “Grand Total”, then the order of the items within the field will be based on the Ascending or Descending order of the Grand Totals of each item in that field. If you sort by “Values in selected row/column”, then the order of the items within the field will be based on the Ascending or Descending order of the values in that particular row or column whose value is selected.
Examples:
Refer Image 5 which shows sorting done on the ‘Country’ field, by ‘Grand Total’ of ‘Sales-$’ in ‘Ascending Order’ – Canada Total is less than the USA Total and UK Total is less than France Total.
Refer Image 6 where ‘Year’ is the column label having items 2009, 2010 and 2011 under which are values of ‘Sum of Actual Sales ($)’ and sorting is done on ‘Car Models’ in ‘Ascending Order’ by ‘Grand Total’ of ‘Sum of Actual Sales ($)’. Note that column E of ‘2011-2009 (%)’ is a calculated field with the formula ‘=2011/2009’ and this does not show in the pull-down option list of Ascending in the ‘Sort’ dialog box.
If you want to sort ‘Car Models’ on the calculated field ‘2011-2009 (%)’, refer Image 7 where you enter ‘$E$3’ column in the box “Values in selected column” in ‘More Sort Options’ dialog box and this will sort ‘Car Models’ in ‘Ascending Order’ using values in column E/calculated field. You can sort similarly using values in any other column (viz. B, C or D).
Sort Data in Values Area
Quick Sort from the Ribbon:
You can sort quickly by selecting a cell in the values area in the Pivot Table report and then under the ‘PivotTable Tools’ tab on the ribbon -> click ‘Options’ tab -> in the ‘Sort’ group click on the icons: (i) Sort Smallest to Largest – sorts the selection so that the lowest values are at the top of the column; (ii) Sort Largest to Smallest – sorts the selection so that the highest values are at the top of the column.
You can also do a Quick Sort by right-clicking a cell in the values area in the Pivot Table report, pointing on ‘Sort’ will give you the options of ‘Sort Smallest to Largest’ and ‘Sort Largest to Smallest’.
The ‘Sort By Value’ dialog box:
Launch the ‘Sort By Value’ dialog box:
Launch the Sort By Value dialog box to sort data based on several criteria at once. The dialog box can be launched in many ways: under the ‘PivotTable Tools’ tab on the ribbon -> click ‘Options’ tab -> in the ‘Sort’ group click on ‘Sort’; right-click a cell in the values area in the Pivot Table report and from the list of commands which opens, point to ‘Sort’ and click on ‘More Sort Options’ in the list.
Sorting Options:
You have 2 Sort options – Smallest to Largest (ie. Ascending order) and Largest to Smallest (Descending order). For each of these options, you can select one of the 2 Sort directions – Top to Bottom (vertical sorting) or Left to Right (horizontal sorting). The Pivot Table report is sorted based on the sort order of the particular row/column (whose value is selected), which is determined per the selected Sort options and Sort directions.
Refer Image 8a which shows the effect of sorting ‘Smallest to Largest’ and ‘Top to Bottom’. Values in the column of Year 2010 appear in ascending order – 1350, 1550, 1750 & 4650. Refer Image 8b which shows the effect of sorting ‘Smallest to Largest’ and Left to Right’. Values in the row of Car Model of SubCompact appear in ascending order – 1400, 1550, 1650 & 4600.
Using Custom Lists for Sorting
Create your own custom list:
Click the Microsoft Office Button image on the top left of the ribbon, click Excel Options at the bottom which opens the dialog box of ‘Excel Options’. Click Popular (category on left side) and then click ‘Edit Custom Lists’ in the top section of ‘Top options for working with Excel’. This opens the ‘Custom Lists’ dialog box where you can add/delete your custom list or import from an excel range. A custom list should be based on a value (text, number, date or time) and not on a format (like cell color), with a max limit of 255 characters. Refer Image 9 where a custom list of 12 months in the order December to January has been created in the ‘Custom Lists’ dialog box.
How to Use Custom Lists when sorting:
Custom Lists are specifically useful in sorting, in cases where you want sorting to be done in a customized order. Pivot Table gives options of sorting in Ascending and Descending order which sorts items alphabetically (A to Z or Z to A). But in some cases like when you want a Pivot Report monthwise or as per weekdays, you will want a sorting order of January to December or Monday to Sunday. Sorting based on Ascending or Descending will not return this order. Obviously you can sort manually, but this could be painful and not preferable. If you have a customized list containing the 12 months in the order of January to December, then using the Ascending sort option will return the sort order for the months as January to December (and not alphabetically). If you want a sort order of December to January, use the Descending sort option or else insert a custom list in the order of December to January and use the Ascending sort order. Excel has default custom lists available for the 12 calendar months and the 7 weekdays, which are generally required by users, and you can further add your own custom lists. Obviously you can sort the months alphabetically too, starting with April, August, …. by disabling the custom lists option as explained below.
Use Custom Lists when sorting – enabling or disabling this option:
Under the ‘PivotTable Tools’ tab on the ribbon, click ‘Options’ tab, then click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which will open the dialog box of ‘PivotTable Options’. Under the ‘Tools & Filters’ tab, in the ‘Sorting’ section you can check ‘Use Custom Lists when sorting’ to enable this option or clear the check box to disable the option.
1. Enable ‘Use Custom Lists when sorting’:
Using the Excel built in default custom list which contains the 12 months in the order January to December:
In this case, using the Ascending (A to Z) sort order in a field containing months, will arrange the months in the order of January to December, and not in alphabetical order. Using the Descending (Z to A) sort order in a field containing months, will arrange the months in the order of December to January, and not in reverse alphabetical order. To apply the custom list (January to December): (i) AutoSort should be selected in the ‘More Sort Options’ dialog box and then use Ascending or Descending options; or (ii) otherwise the custom list of January to December should be selected in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box and then use Ascending or Descending options.
Create a new Custom list containing the 12 months in the order December to January:
In this case, using the Ascending (A to Z) sort order in a field containing months, will arrange the months in the order of December to January, and not in alphabetical order. Using the Descending (Z to A) sort order in a field containing months, will arrange the months in the order of January to December, and not in reverse alphabetical order. To apply the custom list (December to January): (i) AutoSort should be selected in the ‘More Sort Options’ dialog box and then use Ascending or Descending options; or (ii) otherwise the custom list of December to January should be selected in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box and then use Ascending or Descending options.
Note that in this case two custom lists are now present for months – the first is the Excel built in custom list of months in the order of January to December and the second is the newly created custom list of months in the order of December to January. In this case when AutoSort is selected in the ‘More Sort Options’ dialog box, and Ascending or Descending sorting is done for the month field, the latest custom list (appearing last) gets applied automatically. But the first (or an earlier) custom list can also be made applicable by selecting it in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box.
Example: Refer Image 10a which shows AutoSort selected in the ‘More Sort Options’ dialog box; Image 10b shows using Ascending order for the Month field in the ‘Sort’ dialog box; Image 10c shows the Month field sorted in the order of December to January (custom list of December to January having been applied in Ascending order).
Selecting ‘Normal’ in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box:
If ‘Normal’ is selected in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box (ie. AutoSort is not selected) instead of a specific custom list, then using the Ascending or Descending sort order will sort items alphabetically ie. A to Z or Z to A. Choosing a custom list determines the Ascending & Descending order, overriding the alphabetical order of Ascending & Descending.
Example: Refer Image 11a which shows Normal selected in the box of ‘First key sort order’ in the ‘More Sort Options’ dialog box (ie. AutoSort is not selected); Image 11b shows using Ascending order for the Month field in the ‘Sort’ dialog box; Image 11c shows the Month field sorted in Ascending order (A to Z) of April, August, December, February, ….
2. Disable ‘Use Custom Lists when sorting’:
If a custom list is selected in the ‘First key sort order’ (ie. AutoSort is not selected) in the ‘More Sort Options’ dialog box, then Ascending & Descending sorting will be based on the custom list order which is selected. This happens irrespective of whether the ‘Use Custom Lists when sorting’ option is disabled or enabled. However, if AutoSort is selected in the ‘More Sort Options’ dialog box (and a custom list is not selected), when ‘Use Custom Lists when sorting’ is disabled the Ascending or Descending sort order will be alphabetical, whereas when ‘Use Custom Lists when sorting’ is enabled then the Ascending or Descending sort order is based on the custom list.