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

We have a dataset of different products and their respective sales for the months of January, February, and March. We have created a pivot table from this dataset and want to sort this pivot table by values.

Method 1 – Using the Pivot Table Sort Option to Sort Data by Values

We want the January sales to be sorted in ascending order.

Steps:

• Select any cell from the Sum of January Sales column and right-click on that cell.

• Choose the Sort option from the context menu.
• Click on the Sort Smallest to Largest option.

• You will sort the pivot table by January sales values in ascending order.

Method 2 – Applying the Sort & Filter Option for Sorting Values

Steps:

• Select any cell of the pivot table.
• Go to the Home tab and the Editing group.
• Click on the Sort & Filter tool and select the Sort Smallest to Largest option.

• The pivot table will be sorted in ascending order by the January sales values.

Method 3 – Using More Sort Options for Sorting a Pivot Table by Values

Steps:

• Click on a cell inside the pivot table and right-click on it.

• Choose the Sort option from the context menu.
• Choose the More Sort Options… option.

• The Sort By Value dialogue box will appear.
• In the Sort options group, choose the Smallest to Largest option.
• In the Sort direction group, choose the Left to Right option.
• Click on the OK button.

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.

Method 4 – Applying Excel VBA Code to Sort a Pivot Table by Values

Steps:

• Go to the Developer tab and select Visual Basic.

• The Microsoft Visual Basic for Applications window will appear.
• Select Sheet3 from the VBAPROJECT group and insert the following VBA code in the 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``````

• Press Ctrl + S on your keyboard.
• A Microsoft Excel dialogue box will appear. Click on the No button.

• The Save As dialogue box will appear.
• Choose the Save as type: option as .xlsm and click on the Save button.

• Close the VBA code window.
• Go to the Developer tab and select Macros.

• The Macros window will appear.
• Choose the Sheet3.SortPivotTableByValues macro and click on the Run button.

• The pivot table will be sorted in ascending order by the Sum of January Sales column.

Why Sorting the Pivot Table by Value Is Not Working

The most frequent reason is due to a custom list.

Solution:

• Right-click on any cell inside the pivot table.
• Choose the PivotTable Options… from the context menu.

• The PivotTable Options window will appear.
• Go to the Totals & Filters tab, uncheck the option Use Custom Lists when sorting from the Sorting group, and 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.

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

Get FREE Advanced Excel Exercises with Solutions!
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1. That takes us up to the next level. Great potnsig.

2. Very good, thanks.

3. Is it possible to sort a pivot table with a code run from the command prompt?

Lutfor Rahman Shimanto Feb 25, 2024 at 10:44 AM

Hi Kim, thanks for reaching out. Here’s a solution to your query to run the VBA code from the command prompt.

Here, I have used the VBA code shown in Method 4 of this article. The name of this macro is “Sheet3.SortPivotTableByValues” according to this article. Use it in your workbook and modify it according to your references.

Now, create a Notepad/Text document file and copy the following code in it. Change the file location and macro name if needed. Keep in mind that this macro was written in a sheet. So the sheet name should be added before the name of the Sub procedure.

``````Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample()

Dim xlApp

Dim xlBook

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open("C:\Users\DELL\Desktop\SortPivot.xlsm", 0, True)

xlBook.Application.Visible = True

xlApp.Run "Sheet26.SortPivotTableByValues"

Set xlBook = Nothing

Set xlApp = Nothing

End Sub``````

Now, save the file as a .vbs extension file. In this case, I named it RunMacro.vbs.

After that, open the Command Prompt or cmd.exe. Press Windows + R buttons and type cmd in the Run dialog box and click OK.

The Command Prompt will appear. Copy and paste the following line and press Enter: `cd "C:\Users\DELL\Desktop”`

After that, copy and paste the following code in the next line and press Enter.

cscript RunMacro.vbs

Finally, your .xlsm file will open with the Pivot Table sorted. Hope this helps.

Regards

Meraz Al Nahian

ExcelDemy

Advanced Excel Exercises with Solutions PDF