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.

Sample Dataset to Sort Pivot Table by Values

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.

Select Cell inside Pivot Table

  • 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.

Sort Pivot Table by Values Using Sort 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.

Sorted Pivot Table by Values

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.

Use Sort & Filter Tool to Sort Pivot Table by Values

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

Sorted Pivot Table by Values

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.

Select a Pivot Table Data

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

Choose the More Sort Option to Sort Pivot Table by Values

  • 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.

Sort By Value Dialogue Box

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.

Sorted Pivot Table by Values

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.

Access the 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

Microsoft Visual Basic for Applications Window

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

Microsoft Excel Dialogue Box

  • 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.

Save As Dialogue Box

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

Access the Macros Tool

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

Macros Window

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.

Sorted Pivot Table by Values

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.


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

Access the PivotTable Options

  • 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.

PivotTable Options Window

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!


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

Get FREE Advanced Excel Exercises with Solutions!
Siam Hasan Khan
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?

    • Reply Lutfor Rahman Shimanto
      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 
      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.


      Meraz Al Nahian


Leave a reply

Advanced Excel Exercises with Solutions PDF