Sort Pivot Table by Values in Excel (4 Smart Ways)

Sometimes after making a Pivot table in Excel, the data can be seen placed in the wrong order. To sort it out, Excel has many different sorting options for the Pivot tables. In this article, we will see how we can sort a pivot table by values.

Say, we have a dataset of different products and their respective sales for the months of January, February, and March.

Now, we have created a pivot table from this dataset. Now, we want to sort this pivot table by values. You can follow any of the 4 suitable ways to do this.

In this article, we have used the Office 365 version of Microsoft Excel. But, no worries. If you face any problems regarding versions, please comment below.

1. Using Excel Pivot Table Sort Option to Sort Data by Values

You can sort data from a pivot table by using the pivot table’s sort option. Let`s say you want the sales amount of January sales to be sorted in ascending order. Go through the steps below to accomplish this.

📌 Steps:

• First and foremost, select any cell from the Sum of January Sales column and right-click on that cell.

• Subsequently, choose the Sort option from the context menu.
• In the Sort option, you will have two options, one is Sort Smallest to Largest and the other one is Sort Largest to Smallest.
• Following, click on the Sort Smallest to Largest option.

As a result, you will be able to sort your pivot table by January sales values in ascending order. And, the result should look like this.

2. Applying Sort & Filter Option for Sorting Values

Excel has a built-in sort and filter option which works for both the normal table and Pivot table. Now, for sorting the table by January sales values in ascending order, follow the steps below.

📌 Steps:

• Initially, select any cell of your Pivot table.
•  Afterward, go to the Home tab >> Editing group >> Sort & Filter tool >> Sort Smallest to Largest option.

Consequently, your pivot table will be sorted in ascending order by the January sales values. And, the outcome would look like this.

3. Using More Sort Options for Sorting Pivot Table by Values

Usually, the sorting feature takes place in a column. There are more options to sort where you can easily do the sorting for rows. For doing this, follow the steps below.

📌 Steps:

• At the very beginning, click on a cell inside the pivot table and right-click on your mouse.

• Subsequently, choose the Sort option from the context menu.
• Following, choose the More Sort Options… option.

• As a result, the Sort By Value dialogue box will appear.
• Now, in the Sort options group, choose the Smallest to Largest option.
• Following, in the Sort direction group, choose the Left to Right option.
• Last but not least, click on the OK button.

Consequently, you will see a sudden change in your table. The sorting takes place in the row. We selected the row Electric Kettle and there the lowest value was 700 which is the February Sales value for the Electric Kettle. After sorting, the number 700 will come first as it is the lowest number for the row. We will see the February Sales column now comes first due to the sorting of smallest to largest in the Electric Kettle row.

4. Applying Excel VBA Code to Sort Pivot Table by Values

You can also apply a VBA code to sort your pivot table by values in ascending or descending order. Follow the steps below to do this.

📌 Steps:

• At the very beginning, go to the Developer tab >> Visual Basic tool.

• At this time, the Microsoft Visual Basic for Applications window will appear.
• Subsequently, select Sheet3 from the VBAPROJECT group and write the following VBA code in the appeared code window.

Sub SortPivotTableByValues()
Dim pivtbl As PivotTable
Dim pivfld As PivotField
Dim sortclm As String
sortclm = "Sum of January Sales"
On Error Resume Next
Set pivtbl = ActiveCell.PivotTable
If pivtbl Is Nothing Then Exit Sub
For Each pivfld In pivtbl.RowFields
  pivfld.AutoSort xlAscending, sortclm
Next pivfld
End Sub

• Afterward, press Ctrl+S on your keyboard.
• Consequently, a Microsoft Excel dialogue box will appear. Click on the No button here.

• As a result, the Save As dialogue box will appear.
• Here, choose the Save as type: option as .xlsm type and click on the Save button.

• Afterward, close the VBA code window and go to the Developer tab >> Macros tool.

• At this time, the Macros window will appear.
• Subsequently, choose Sheet3.SortPivotTableByValues macro and click on the Run button.

As a result, the pivot table is sorted in ascending order as the Sum of January Sales column. And, the output should look like this.

Why Sort Pivot Table by Value Is Not Working

Sometimes, the sorting might not work properly in pivot tables. It can happen for many reasons. To fix this problem, you can apply several solutions according to the reason for your problem.

The most frequent reason is because of Excel’s custom list presence. To fix this error, you can follow the steps below.

Solution:

• First, right-click on any cell inside the pivot table.
• Following, choose the PivotTable Options… from the context menu.

• As a result, the PivotTable Options window will appear.
• Now, go to the Totals & Filters tab >> uncheck the option Use Custom Lists when sorting from the Sorting group >> click on the OK button.

Things to Remember

• In a pivot table, you can sort the numbers in smallest to largest or largest to smallest order.
• You can also sort alphabetical data from a to Z or from Z to A.
• If you sort a table by an individual column, the whole table will be in the sorted order of that specific column.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

I hope this article will help you while working with Pivot tables. Here I explained different sorting procedures of Pivot Tables by values. Feel free to comment regarding this article in the comment section.

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