How to Sort Pivot Table by Grand Total (Including VBA)

When working with a large set of data, conclusions are often drawn using the grand totals, as using the subtotals is not always feasible. In a pivot table, it is easy to sort data using any field, including grand total. In this article, we will learn how pivot table sort by grand total works.

In the following dataset, we have some sales data. There are the names of the salesperson, where they made the sales, the products they sold, the number of units, and the revenue. We will find out the total amount of units and revenue according to the region by sorting the pivot table using the grand total.

➤ Select the data table and go to the Insert Click on PivotTable to open a new window with options to create a new pivot table. Click OK to make a pivot table.

➤ In the new sheet, first, we have to select all the fields. Then, we keep the Values and Region in the Columns The Region should be under Values for now; the reason will be explained later. The Rows area should contain the Salesperson and the Product. The Values area should already include the Sum of Units Sold and Sum of Revenue, as those are automatically chosen by excel.

➤ Go to a cell of Grand Total, right click on it, and select Sort > Sort Smallest to Largest

➤ Now, the table is sorted by grand total. However, the Sum of Units Sold is used for sorting here, and the Sum of Revenue is shown in a separate section, like the image below:

➤ If you like it this way, keep it like that. However, we would like to show the revenue right next to the units on a per-column basis. In order to do that, we put the Region over Values on the Columns section.

➤ Now the table looks like this:

If you want to automate the sorting, you can sort the pivot table by grand total in excel vba. Here are the steps to do that:

➤ Press Alt+F11 to open the VBA window.
➤ Go to Insert > Module to open the code editor up.
➤ Write this code:

Sub SortPivotTableByGrandTotal()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim totalFieldName As String
    Set ws = ThisWorkbook.Worksheets("PivotSheet")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")
    totalFieldName = "Sum of Units Sold"
    On Error Resume Next
    If pt.PivotFields(totalFieldName) Is Nothing Then
        MsgBox "Total field '" & totalFieldName & "' not found in the pivot table.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    pf.AutoSort xlDescending, totalFieldName
End Sub

➤ Replace “PivotSheet” with the sheet where the pivot table exists, “PivotTable1” with the name of the pivot table, “Region” with the column name, and “Sum of Units Sold” with the value for which you want to find the grand total of.
➤ If you want to sort by ascending order, replace xlDescending with xlAscending.

➤ Press F5 to run the code and go back to the spreadsheet to see results.

Frequently Asked Questions

How do you filter PivotTable totals?

When you have the pivot table open, go to the PivotTable Analyze tab. From there, go to Options, and find the Totals & Filters tab. Check the filters you want and uncheck everything you don’t. Click OK and check the pivot table for results.

Why won’t my PivotTable sort by date?

Excel usually recognizes dates by itself and allows you to sort the table by date from the Sort option. However, if the pivot table cannot sort by date, it is probably because the date is formatted using text or something else. In your data range/table, check the date format and convert the values to date format. Then recreate the pivot table to sort it by date.

How to sort data in Excel?

Select the data you want to sort. Then go to the Data tab and click Sort. From there, you can sort the data according to your needs.

How do I insert a total row?

First of all, convert your data range to a regular table by selecting your data range and pressing Ctrl+T. Then, go to the Table Design tab, and check the Total Row box from the Table Style Options section.

How to grand total in Excel?

Click on any cell on your pivot table to get the Design tab to show up. From that tab, go to Layout > Grand Totals, and select whether you want your grand totals to show up for columns and/or rows.

Wrapping Up

In this article, we have learned how to sort a pivot table by grand total both using regular excel functions and VBA code. The workbook is there for you to download, so don’t hesitate to use it for your practice. If you have a moment to spare, comment below on how helpful you found this tutorial. We will see you in another tutorial soon.