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.
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
The following sorting options are displayed
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.
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
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
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
PivotTable will be sorted on total amount month wise.
You can see that February has highest order amount while March has the lowest.
If you want to sort the PivotTable on total amount region wise in the month of January.
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 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.
As you can see, under Summary, current sort order is given as follows
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.
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
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
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.
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
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.
Now, First key sort order option becomes available. This option can be used to select the custom order you want to use.
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.
While sorting data in a PivotTable, remember below points
|
|
Excel pivot tables Related Tutorials |
|
---|---|
VBA For Excel Tutorial | Microsoft Excel Tutorial |
Microsoft Word Tutorial | Microsoft Powerpoint Tutorial |
Microsoft Entity Framework Tutorial | Microsoft Azure Tutorial |
Microsoft Project Tutorial | Advanced Excel Charts Tutorial |
Advanced Excel Functions Tutorial | Excel Dashboards Tutorial |
Excel Data Analysis Tutorial | Excel Power View Tutorial |
Excel pivot tables Related Interview Questions |
|
---|---|
VBA For Excel Interview Questions | Microsoft Excel Interview Questions |
Microsoft Word Interview Questions | Microsoft Powerpoint Interview Questions |
Microsoft Entity Framework Interview Questions | Microsoft Azure Interview Questions |
Microsoft Project Interview Questions | Excel Data Analysis Interview Questions |
Excel pivot tables Interview Questions | Excel Power View Interview Questions |
Microsoft Interview Questions |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.