How to Sort a Pivot Table (6 Easy Ways)

PivotTable summarizes and generates reports from a large data range. It helps to analyze data and find trends and relations between different data. Sorting is one of the main ways to generate summaries from a pivot table according to your desired order of data.

In this Excel tutorial, you will learn to sort a pivot table in Excel.

You can see an unsorted pivot table with the annual salaries of each department for different designations and the same pivot table after sorting alphabetically at the bottom of the image.

Pivot Table Sorting Restrictions

In Pivot Tables, you can sort the Row Labels by their labels in ascending (A-Z) or descending (Z-A) order or by one of the Value fields in Largest to Smallest or Smallest to Largest. However, there are some restrictions to this sorting process because of the Pivot Table structure.

• If no pivot fields are present to the left of the field you are sorting, then all the pivot items will be sorted together in the order you selected.
• On the other hand, if fields are present to the left of the field you are sorting, the pivot items will be sorted within each item of the next field to the left.

6 Ways to Sort Pivot Table in Excel

In this Excel tutorial, you will learn 6 ways of sorting PivotTable in Excel. To create a pivot table, we will use the dataset shown below.

To create a pivot table from this dataset:

1. Select any cell in the data range.
2. Go to the Insert tab > PivotTable > From Table/Range.
3. In PivotTable from the table or range box, choose where to put the pivot table in the New worksheet or Existing worksheet and press OK.
4. In the PivotTable Fields pane on the right side, drag the fields in the Rows, Columns, and Values fields. 

Following the steps above, you can create a pivot table. Now, here are 6 different ways to sort the created pivot table in Excel:

1. Sorting a PivotTable by Label Data

In the image below, you can see a pivot table with Department as columns, Designations as rows, and Annual Salaries as values. We want to sort the row labels in the pivot table in alphabetic order.

To sort PivotTable by the label data:

1. Select a cell in the column that is to be sorted by row labels.
2. Go to the Data tab > Sort & Filter group > Sort.
3. Choose one of the Manual, Ascending, or Descending options from the Sort box and press OK.

Here, we chose the Descending sorting option. You can also sort by values or auto sort by clicking More Options.

The pivot table will look like the image below after being sorted in descending order.

You can also avail of the sorting options by clicking on the sign beside Row Labels and sorting them alphabetically or avail of More Sort Options.

2. Sorting by Values

In Excel, you can sort pivot tables by values in an ascending or descending manner. Also, the data can be sorted in both vertical and horizontal directions.

To sort the pivot table by values, follow the steps below:

1. Select a cell with a value in the pivot table.
2. Go to the Sort tab and select Sort from the Sort & Filter group.
3. In the Sort By Value box:
   1. Choose the sorting option, either Smallest to Largest or Largest to Smallest.
   2. Choose the sorting direction Top to Bottom or Left to Right.
   3. Press OK.

As we can sort pivot tables by values in two different directions, we will now sort them vertically and horizontally. Here we will sort by the Grand Total values in these two directions.

2.1 Sorting Pivot Table by Grand Total Horizontally

Follow the steps below to sort the pivot table horizontally by Grand Total:

1. Select a cell with a Grand Total value horizontally in the pivot table.
2. Go to Sort > Sort & Filter > Sort.
3. In the Sort By Value box:
   1. Choose Smallest to Largest as sorting option.
   2. Choose Left to Right as sorting direction.
   3. Press OK.

The Grand Total values in the row will be sorted from smallest to largest.

2.2 Sorting Pivot Table Vertically

To vertically sort the Grand Total values, you need to select a cell from the Grand Total column and choose Top to Bottom as sorting directions.

After sorting values vertically, the output will look like the image below. You can notice that sorting grand total in the vertical and horizontal directions can be done concurrently.

3. Sorting Pivot Table with Custom Options

You have already learned to sort pivot tables by values and labels. However, you can also set custom sorting options to sort your pivot table according to your needs. We have a pivot table with employee names and their annual salaries shown in the image below. Now we will show you how to set the custom sort option.

To sort the pivot table with custom options go through the steps below:

1. Click on the sort and filter sign beside Row Labels.
2. Select More Sort Options.
3. In Sort box, choose one of the Manual, Ascending, or Descending options.
   We chose Descending option.
4. Press More Options to see more sorting options.
   
5. In More Sort Options box, check Autosort and press OK.
   

Here, Autosort will automatically every time the report is updated. If you uncheck this option then First key sort order option will be available and you can choose a custom order to sort. In general, you can see the weekdays and months in a year as a custom list. However, you can also create your own custom sort list and use that list from here. One thing to remember is that updating (refreshing) the data in your PivotTable results in the loss of the custom list’s sorting order.

In the Sort By feature, select either Grand Total or Values in specific columns to arrange the data based on these values. This functionality is disabled when the sorting is set to Manual.

4. Sorting A Pivot Table With Multiple Category Fields

We often must create pivot tables with multiple category fields in a column. Like in the image below, we have a pivot table that shows annual salaries for different designations department-wise.

While sorting by row labels this pivot table will sort by department wise. But if you need to sort the designations that can not be done in the same way. Follow the steps below to learn how to sort by column with multiple row labels or category fields:

1. Click on the sort and filter sign beside Row Labels.
2. Click on the drop-down of “Select Field”.
3. Choose the category field or row label to sort.
   We chose Designation category.
   
4. Select the sorting option.
   We selected the “Sort Z to A” option.
5. Press OK.
   

After sorting, the designations of each department will be sorted in a descending manner.

5. Sorting Pivot Table by Dates

For analyzing data, sometimes we need to sort data by dates, and in the pivot table, you can quickly sort the data based on dates. Here, we have a pivot table containing the employee’s names, their hiring dates, and annual salaries.

To sort employees’ annual salary based on the hiring date, follow the steps below:

1. Click on the sort and filter sign beside Row Labels.
2. Select the Sort Newest to Oldest sort option.
3. Press OK.

You can see the dates are sorted from newest to oldest order in the image below.

6. Sort Pivot Fields With A Macro

To sort your pivot table by values in ascending or descending order quickly you can apply a VBA macro. When you need to sort frequently, this VBA macro can be useful. Here we will sort the Grand Total column of the pivot table using VBA in ascending order.

Follow the steps below to apply the VBA macro to sort the pivot table:

1. Go to the Developer tab > Code group > Visual Basic.
   
2. Go to Insert and select Module in the Microsoft Visual Basic for Applications window.
   
3. Insert the following VBA code in the module.

   Sub SortUsingVBA()
       ActiveSheet.PivotTables("PivotTable4").PivotFields("Designation").AutoSort _
           xlAscending, "Sum of Annual Salary", ActiveSheet.PivotTables("PivotTable4"). _
           PivotColumnAxis.PivotLines(5), 1
   End Sub

4. Press Ctrl+S to save the file as a macro-enabled file.
5. Click the No button in the Microsoft Excel dialog box.
   
6. In the Save As dialog box, choose the Save as type option as .xlsm type and click the Save
7. Go back to the sheet and select a cell in the column to sort.
8. Select Macros from the Code group in the Developer tab.
   
9. Select SortUsingVBA macro and click on the Run button in the Macro box.
   

You can see the pivot table is sorted by values in ascending order.

What to Do If Pivot Table Sorting Not Working in Excel?

There are some common instances where you might see that the pivot table sorting is not working. Here, we listed some common reasons and solutions for the pivot table sorting not working.

1. Pivot Table Sort by Value Not Working

The most common case of pivot table sort not working is that the outer column is sorted in a chosen ascending or descending manner. Still, the other columns are not sorted in that way. The image below shows that the grand total of annual salaries for each designation column is sorted in ascending order. But the Finance column is not sorted in ascending order.

Reason:  The reason behind this is that the pivot table makes groups of different parts of data. So, when the pivot table is sorted based on grand totals, all the designations are sorted based on that value. However, the finance department’s column can not be sorted similarly as it will disrupt the existing order. For this reason, the pivot table gets filtered based on the value of the right-most column. Also while sorting it does not take any other column on the left of that in considertaion.

Solution: If you are facing this sorting problem, you have to decide which column value you want to consider for sorting your data. Then, you have to put that column at the rightmost column of your pivot table.

2. Pivot Table Months Not In Order

Generally, a pivot table can sort the data month-wise. But sometimes, the pivot table might sort the months alphabetically, like in the image below.

Reason: Pivot table sorts months with a default custom sort list of months. But if this custom sorting is disabled, then months get sorted alphabetically.

Solution:

To sort months in order, not alphabetically, by enabling the Custom Sort option, follow the steps below:

1. Right-click on any cell in the pivot table.
2. Choose PivotTable Options from the context menu.
   
3. In the PivotTable Options box, go to the Totals & Filters section.
4. Under Sorting check the Use Custom List when Sorting.
5. Press OK.
   

You will see the months automatically sorted in custom order instead of alphabetically sorting.

3. Newly Added Pivot Table Items Out of Sort Order

Often we might add data to the source file after sorting the pivot table. In those cases, the newly added items add up at the bottom of the sorted list after refreshing. For instance, we have a sorted dataset showing annual salaries for different designations of each department.

Now in the source data we added a new department named “IT” with “Analyst” designation. After refreshing the pivot table you can see that the “IT” department added at the bottom.

Solution:

To include newly added items in the sort list, go through the steps below:

1. Click on the Row Labels sort and filter sign.
2. Choose the Sort A to Z option and press OK.
   

You will find the list sorted in the proper order.

Conclusion

In this Excel tutorial, you learned 6 different ways of sorting pivot table in Excel. You learned to sort pivot tables by row labels, by values, using custom options, with multiple category fields, by dates, and using a VBA macro. In addition to that, you learned about some reasons and solutions to apply when pivot table sort is not working. I hope this tutorial helped you to learn about pivot tables and performing it’s sorting. If you have any queries please comment down below.

Frequently Asked Questions

1. Why Does Excel Not Allow Sorting?

Excel can sort most of the data sourced externally. However, Excel can not sort data sourced externally from SQL or some other sources. In this case, you have to copy and paste the data as value in your spreadsheet and then use pivot table sorting.

2. Why Pivot Table Report Filter Is Not Sorted?

If new data is added to the source data and after refreshing it is not shown as a sorted list in the pivot table report filter. In this case, drag that category from the filter field into the Value field so that pivot table can treat it as a value field. Then apply sorting and drag the field again to filter the field. Now, you can see the newly added items in sorted order in the report filter.

3. How to Sort Pivot Table by Count?

To sort the pivot table by count, click on the arrow sign of a value field, select Value Field Settings, in the appeared box, choose Count from the Summarize value field by section, and press OK. Now when you sort that value field column, it will be sorted based on the count value.

<< Go Back to Pivot Table in Excel | Learn Excel