Sort a pivot table by values
The below Pivot Table shows the sales data of a hotel. The column labels show the day of the week. The row labels show the package.
You can sort the values of the above pivot table, vertically as well as horizontally ⬆️➡️
Sort pivot table vertically
Assume that you want to get the highest sales contributing package to the top of the list.
Then, you have to sort the Pivot Table vertically in descending order.
Follow the below steps to sort Pivot Table data.
- Click the small drop-down arrow in row labels.
- Select more sort options from the drop-down list.
- To sort descending order select “Descending (Z to A) by:”.
- Click the down arrow button below that and select the sum of sales column.
- Click the “OK” button to sort data.
Then, packages will be sorted based on the value field.
Sort pivot table horizontally
Imagine that you want to sort pivot table days based on sales value. You can arrange it from the smallest value to the highest value.
So, you must sort the Pivot Table horizontally in ascending order.
You can follow the below steps for that.
- Select any value in the Pivot table’s grand total row except the cell in the grand total column. So, you can select any cell in the range of B6 to H6.
- Click the custom sort icon of the sort and filter group in the data tab.
- Then the sort-by-value dialog box appears. To sort in ascending order, you have to select smallest to largest from the sort options dialog box. Select the largest to the smallest sorting option, if you want to sort data in ascending order.
- Select left to right as the sort direction as you want to sort horizontally.
Your Pivot Table is sorted horizontally in ascending order.