Excel Pivot Tables Sorting Data - Excel pivot tables

How data is sorted in Pivot tables?

Data in a PivotTable can be sorted to find the items which you want to analyse. Data can also be sorted from lowest to highest values or highest to lowest values or in any other custom order that you choose.

Consider below PivotTable wherein you have the summarized sales data region-wise, salesperson-wise and month-wise.

Sorting on Fields

Data in the above PivotTable can be sorted on Fields which are in Rows or Columns – Region, Salesperson and Month.

To sort the PivotTable with the field Salesperson, proceed as follows

• Click the arrow in the Row Labels.
• Select Salesperson in the Select Field box from the dropdown list.

The following sorting options are displayed

• Sort A to Z.
• Sort Z to A.
• More Sort Options.

Further, Salesperson field will be sorted in ascending order, by default. Click Sort Z to A. Salesperson field will be sorted in descending order.

Similarly fields can be sorted in column – Month, by clicking on the arrow in the column labels.

Sorting on Subtotals

If you want to sort the PivotTable based on total order amount – highest to lowest in every Region. That is, you want to sort the PivotTable on subtotals.

You can observe that there is no arrow for subtotals but you can still sort the PivotTable on subtotals as follows

• Right-click on the subtotal of any of the Salespersons in the Grand Total column.
• Select Sort from the dropdown list.
• Another dropdown list will appear with sorting options – Sort Smallest to Largest, Sort Largest to Smallest and More Sort Options. Select Sort Largest to Smallest.

Subtotals in the Grand Total column will be sorted from highest to lowest values, in every region.

Similarly, if you want to sort the PivotTable on subtotals region wise, perform below steps

• Right click on the subtotal of any of the regions in the Grand Total column.
• Click Sort in the dropdown list.
• Click Sort Largest to Smallest in the second dropdown list. PivotTable will get sorted on subtotals region-wise.

As you can see, South has the highest order amount while North has the lowest.

PivotTable can also be sorted based on the total amount month wise as follows

• Right click on any of the Subtotals in the Grand Total row.
• Select Sort from the dropdown list.
• Select Sort Largest to Smallest from the second dropdown list.

PivotTable will be sorted on total amount month wise.

You can see that February has highest order amount while March has the lowest.

More Sort Options

If you want to sort the PivotTable on total amount region wise in the month of January.

• Click on the arrow in Row Labels.
• Select More Sort Options from the dropdown list. Sort (Region) dialog box appears.

As you can see, under Summary, current Sort order is given as Sort Region in ascending order. Ascending (A to Z) by is selected under Sort Options. In the box below that, Region will be displayed.

• Click the box containing Region.
• Click Sum of Order Amount.

Click the More Options button. More Sort Options (Region) dialog box appears.

As you can see, under Sort By, Grand Total is selected. Under Summary, current sort order is given as Sort Region by Sum of Order Amount in ascending order.

• Click Values in selected column: under Sort By.
• In the box below that, type B5.

As you can see, under Summary, current sort order is given as follows

• Sort Region by Sum of Order Amount in ascending order using values in this column: January. Click OK.
• The Sort (Region) dialog box appears. Select Descending (Z to A) by: under Sort Options.

Under Summary, current sort order is given as follows

Sort Region by Sum of Order Amount in descending order, using values in this column: January. Click OK. PivotTable will be sorted on region, using values in January.

As you can see, in the month of January, West has the highest order amount while North has the lowest.

Sorting Data Manually

In the PivotTable, data will be sorted automatically based on chosen the sorting option. This is called as AutoSort.

Place the cursor on the arrow in Row Labels or Column Labels.

AutoSort appears, showing the current sort order for each of the fields in the PivotTable. Now, if you want to sort the field Region in the order – East, West, North and South. You can do this manually, as follows

• Click the arrow in Row Labels.
• Select Region in the Select Field box from the dropdown list.
• Click More Sort Options. The Sort (Region) dialog box appears.
• Select Manual (you can drag items to rearrange them).
• Click OK.

Under Summary, current sort order is given as Drag items of the Region field for displaying them in any order.

Click on the East and drag it to the top. While dragging East, a horizontal green bar appears across the entire row moves.

Repeat dragging with other items of the Region field till you get the required arrangement.

You can observe below points

• Items of the nested field – Salesperson also move along with the corresponding Region field item. Further, values in other columns also moved accordingly.
• If the cursor is placed on the arrow in Row Labels or Column Labels, AutoSort will appear showing the current sort order of the fields Salesperson and Month only. As you have sorted Region field manually, it will not show up in AutoSort.

Note − Manual dragging of items of the field cannot be used which is in ∑ VALUES area of the PivotTable Fields list. Therefore, you cannot drag the Sum of Order Amount values in this PivotTable.

Setting Sort Options

In previous section, we explained how to set the sorting option for a field to manual. There are some more sort options which can be set as follows

• Click the arrow in Row Labels.
• Select Region in the Select Field box.
• Click More Sort Options. The Sort (Region) dialog box appears.
• Click the More Options button.

More Sort Options (Region) dialog box appears. You can set more sort options in this dialog box.

Under AutoSort, user can check or uncheck the box and sort automatically each time report is updated. This will allow or stop automatic sorting whenever the PivotTable data is updated.

• Uncheck the box – Sort automatically every time the report is updated.

Now, First key sort order option becomes available. This option can be used to select the custom order you want to use.

• Click the box under First key sort order.

As you can see, day-of-the-week and month-of-the year custom lists are provided in the dropdown list. You can use any of these, or you can use your own custom list such as High, Medium, Low or the sizes list S, M, L, XL which are not in alphabetical order.

You can create your own custom lists from the FILE tab on the Ribbon. FILE → Options. In the Excel Options dialog box, click on advanced and browse to General. You will find the Edit Custom Lists button next to Create lists for use in sort and fill sequences.

Remember that a custom list sort order will not be retained when you update (refresh) data in your PivotTable.

Under Sort By, click on Grand Total or Values in the selected columns to sort by these values. This option will not be available when you set sorting to Manual.

Points to consider while sorting PivotTables

While sorting data in a PivotTable, remember below points

• Data which has leading spaces will affect the sort results. Remove any leading spaces before sorting.
• Case-sensitive text entries cannot be sorted.
• Data cannot be sorted by a specific format such as cell or font color.
• Data cannot be sorted by conditional formatting indicators, such as icon sets.