Excel allows you to sort a PivotTable in alphabetical order or from largest to smallest values. They are similar to the standard Excel sorting options but worth looking at separately because there are automatic sorts, custom sortings and a manual sorting option.
Quick PivotTable Sort
Sort the PivotTable by right-click on any cell in a column and select ‘Sort’. Choose to sort in ascending order by selecting the Sort Smallest to Largest option or descending order by selecting the Sort largest to Smallest option.
The ‘Sort Ascending’ and ‘Sort Descending’ commands are also available under the Data tab – ‘Sort & Filter’ group.
Another way of sorting data in a PivotTable, is click the arrow next to Row Labels and Column Labels cells to sort them.
Select Sort A to Z or Sort Z to A to either sort data in ascending or descending order. You will find the text sorted in alphabetical order, numbers sorted from lowest to highest and likewise after performing the sort actions.
There is no issue with using the PivotTable fields in the original sort order if that pleases you.
No Arrow button?
How do you sort a column if that does not have an arrow button? You can sort them individually or on Totals by doing a right click on a cell and selecting a sort option. You will be amazed to see that the sorting is applied for all the cells at the same column level.
The below screenshot I have sorted the data alphabetically by Country then Product using Sort A to Z:
You can also apply the same function by right clicking any cell on the Total Amount and sorting them for seeing the products sorted from lowest to highest and likewise.
PivotTable Custom Sort Options
Excel also gives you the freedom to set your custom sort options. Now you can manually sort specific items and also set your own sorting options.
- Click any cell you want to sort in a row or column and then click the arrow on the Row labels.
- Select the More sort options.
- Choose the type of sorting you want to do using the Sort dialog box below:
- Clicking on Manual allows you to manually rearrange the fields by dragging the items.
5. Click on the Sort options either Ascending (A to Z) or Descending (Z to A) and then choose the fields you want to sort from the drop-down menu.
