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.

sum_of_order_amount.jpg

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.

sorting.jpg

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.

sort_z_to_a.jpg

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.

sorting_on_subtotals.jpg

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.

grand_total.jpg

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

click_sort.jpg

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.

total_amount.jpg

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.

more_sort_options.jpg

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 down_arrow.jpg in Row Labels.
  • Select More Sort Options from the dropdown list. Sort (Region) dialog box appears.

region.jpg

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.

more_options.jpg

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

values_in_selected_column.jpg

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.

ascending_order.jpg

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.jpg

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.

sorting_data_manually.jpg

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 arrowdown_arrow.jpg in Row Labels or Column Labels.

select_manual.jpg

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 down_arrow.jpgin 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.

select_region (1).jpg

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.

click_on_east.jpg

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

repeat.jpg

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 down_arrow.jpgin 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 down_arrow.jpg 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.

click_ok.jpg

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.

click_the_box.jpg

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.

advanced.jpg

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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel pivot tables Topics