Sorting in a Pivot Table

By default, items in each pivot field are sorted in ascending sequence based on the item name. Figure 5.15 shows that the Market field is sorted alphabetically by market name.

Figure 5.15. By default, a pivot table is sorted alphabetically. In this default table, markets are presented in AZ order.

Excel gives you the freedom to sort your data fields to suit your needs. You can use one of three methods to apply sorting to your pivot table:

• Using the Advanced Options dialog box

• Using the manual method

• Using the sorting buttons on the standard toolbar

Sorting Using the Advanced Options Dialog Box

Sort options are controlled from the PivotTable Field Advanced dialog box. To get to this dialog box, you must first double-click the Market field name in the pivot table to display the PivotTable Field dialog box. Look for the Advanced button along the right side of this dialog box.

Clicking the Advanced button will lead to the powerful PivotTable Field Advanced Options dialog box. This dialog box, shown in Figure 5.16, controls both the sorting options for a field and the AutoShow options for a field. You'll have a chance to try out the AutoShow options later in this chapter.

Figure 5.16. The Advanced Options dialog box contains the AutoSort and AutoShow features, which are really too powerful to be buried in an obscure place.

Figure 5.16 shows that the default sort for the Market field is to be sorted in ascending order using the Market field. To change the report to list the largest markets first, change the AutoSort option to Descending.

As shown in Figure 5.17, choose the drop-down for Using Field. Typically, your choices are to sort by the original field or by any of the data fields currently in the pivot table. In this example, your choices are to sort by descending units or to sort by descending revenue. Choose Revenue from the drop-down.

Figure 5.17. You can sort a column field by any of the data fields or by the column field itself.

After completing the Advanced Options dialog box, you must click OK twiceonce to close the Advanced Options dialog box, and once to close the PivotTable Field dialog box.

As shown in Figure 5.18, the result will be that the pivot report is sorted by descending revenue. Tennessee was the top market for copier sales with $6.3 million.

Figure 5.18. The report is sorted with the largest market at the top.

Although it may seem a bit labor intensive to navigate to the Advanced Options dialog box, this is by far the best method of all the sorting methods covered in this book. Sorting applied in this method persists no matter how you reshape the pivot report. This will prevent you from having to apply your sorting preferences repeatedly.

Note the Effect of Layout Changes on AutoSort

If you change the page field to show printer sales, the report will automatically re-sort. Figure 5.19 shows the report after Printer Sales has been selected from the Line of Business drop-down. The report automatically sorted to show that Indiana is the top market for printer sales with $4.9 million.

Figure 5.19. Change to a new page field value, and the markets re-sort to show which market is the largest in printer sales.

If you drop a new field on the report, the pivot table will remember the AutoSort option for the Market field and do its best to present the data in that order. This may not be in the spirit of your report focusing on the best markets. Figure 5.20 shows the report after the Region field was added as the outer row field. The Region field is sorted alphabetically by region name, but within each region, the markets are arranged in descending order by revenue.

Figure 5.20. After a Region field was added with the default sort, the report shows regions alphabetically, but within each region, the markets are reported by descending revenue.

You can choose to sort the Region field by descending revenue. In this case, the South region appears first because it produced $28 million of printer sales. Within the South region, markets are sorted in descending order by revenue, as shown in Figure 5.21. Although such a sort would be relatively simple in a regular Excel table, it requires two visits to the Advanced Options dialog boxonce for the Region field and once for the Market field.

Figure 5.21. Set the AutoSort option for the Region field to sort descending based on Revenue.

Sorting Using the Manual Method

Figure 5.22 shows the default sequence of regions in a pivot table report. Alphabetically, the regions are shown in the sequence North, South, West. If this company is based in California, company traditions might dictate that the West region should be shown first, followed by North and South.

Figure 5.22. Company traditions might dictate that the Region field should be in West, North, South sequence instead.

On the face of it, there is no easy way to sort the Region field into this sequence. An ascending sort would cause the West region to be last. A descending sort would cause the West region to be first, but then the South and North regions would not be in the proper sequence to match the company's standard reporting.

You might try to convince your company to change a decade-long tradition of reporting in the West, North, South sequence, or even change the region names in order to accommodate sorting in your pivot table. Both of these concepts would be tough to sell and are not viable options. Luckily, Microsoft offers a very simple solution to this problem.

Move the cellpointer to cell B5 of the pivot table, or the cell that currently contains the heading for North. In cell B5, type the word "West" and press Enter. Remarkably, the pivot table will automatically resequence. The $216 million in sales for the West will automatically move from column D to column B. The remaining regions will move over to take the next columns. The result, shown in Figure 5.23, presents the report in the desired West, North, South sequence.

Figure 5.23. Simply typing "West" in cell B5 will resequence the report.

Example of Adding a New Region

After using this technique, any new regions you add to the data source will be added at the end of the list. Figure 5.24 shows the pivot table after a region named Central is added. Because Excel does not know where to add Central, it automatically goes to the end of the list.

Figure 5.24. After you specify a manual sort, new regions are added at the end of the list.

The other solution to the West, North, South sequence problem is to set up a custom sort list. Custom sort lists are maintained on the Custom Lists tab of the Options dialog box. Choose Options from the Tools menu. Type "West, North, South" in the List Entries box and click the Add button.

As shown in Figure 5.25, the Custom Lists list box will show your new list of West, North, South.

Figure 5.25. A custom list of West, North, South is now available.

After setting up a custom list, you can specify that the Region field should be sorted in ascending sequence. Excel's IntelliSense technology will realize that the items along the Region field match a custom list and will sort the report to match your custom list.

Sorting Using the Sorting Buttons on the Standard Toolbar

With a little practice, you can sort some pivot tables using the sorting buttons on the standard toolbar.

CAUTION

Doing any sort using the toolbar buttons will change the AutoSort option to Manual. The logic behind the sort will not be remembered as the layout of the report changes.

Sorting a pivot table using the toolbar buttons is a little different from sorting a regular dataset.

Select any cell from A5 through A26 in Figure 5.26 to sort the pivot table by market. Because this is a row field, you can select the Market heading or any of the individual market items before clicking the sort button.

Figure 5.26. Sort the Market field by selecting any cell in A5:A26 and clicking the AZ sort button.

To sort by revenue as shown in Figure 5.26, you must select one of the values from B6 through B26. If you select the heading in A4 or B4, Excel will report that it does not understand by which field to sort.

Figure 5.27 shows a pivot table with two data fields along the column area. Choosing a cell in A5:A26 and then clicking a sort icon will sort the report by market. Choosing a cell in B6:B26 will sort the report by revenue. Choosing a cell in C6:C26 will sort the report by units. Interestingly, choosing a cell in either B5 or C5 and then sorting will sort the Revenue and Units fields into ascending or descending order based on their heading names. Choosing a cell in either B27 or C27 will sort the Revenue and Unit columns based on their total values.

Figure 5.27. Sorting using the toolbar buttons becomes more complex as the report structure becomes more complex.

It is critical to remember that any sort performed using the toolbar buttons is a one-time sort. The result will be the AutoSort option changing to Manual. The left side of Figure 5.28 shows a pivot table showing copier sales by market. The report was sorted in descending revenue by choosing cell B6 and clicking the ZA toolbar icon. Although this appears equivalent to the more laborious process of setting up an AutoSort, the sort only applies to the current view of the table.

Figure 5.28. Change to Printer Sales, and the report is not properly sorted. This is because sorts using the AZ and ZA toolbar icons result in a manual setting for the AutoSort feature.

If you change the page field to show Printer Sales, the pivot table is not automatically re-sorted. As shown on the right side of Figure 5.28, the markets are reported in the same sequence as when the report was showing Copier Sales. Although there are instances when this is desirable, most of the time, you would want the report to automatically sort based on the new field.