How to add percentage of grand total/subtotal column in an Excel Pivot Table?

To display the percentage that each item contributes to the overall totals or to a subgroup within your data, you can enhance your Excel pivot table by adding a percentage calculation column. This approach is particularly useful when you want to perform data comparison or present summary statistics that go beyond the raw numbers. The following expanded steps outline how to set up this feature, along with practical tips and considerations at each stage.

1. Start by selecting your data range that you wish to analyze in the pivot table. Then go to the Excel ribbon and click Insert > PivotTable. This creates a pivot table foundation for your analysis. Choosing the right source range at the outset ensures your calculations will be accurate; double-check that your selection covers all relevant data without blank rows or columns.

2. In the Create PivotTable dialog box that appears, specify whether you want the pivot table to be placed in a new worksheet or an existing one. Selecting a new worksheet often makes your table easier to view and keeps your original data intact. After setting your preference, click the OK button to proceed.

3. In the PivotTable Fields pane, drag the Shop field and the Items field to the Rows area. Next, drag the Sales field into the Values area twice. Doing this allows you to display both the original sales values and the percentage calculation side by side in the resulting table. If you only wish to show the percentage column, you can later remove or hide the original value field.

4. In the Values area below, click on the drop-down arrow next to the second Sales field (this will typically show as “Sum of Sales2” by default). Select Value Field Settings from the context menu. This step opens a dialog box where you can define how the field’s data is summarized and displayed in the table.

5. Within the Value Field Settings dialog box, go to the Show Values As tab. From the Show value as drop-down menu, pick % of Grand Total to calculate each value as a proportion of the grand total. Optionally, type a clear, descriptive name for your new column in the Custom Name field, such as "Percent of Total Sales," to make interpretation easier. Confirm your changes by clicking OK.

Note: If you'd like to show the percentage that each value represents of its parent subtotal (rather than the overall grand total), select % of Parent Row Total from the Show values as drop-down menu instead. This option is especially valuable when your dataset has grouped rows—such as categories under a shop—so you can analyze contribution to category-level totals.

Upon returning to the pivot table, you'll now see an added column displaying the “Percent of Grand Total” alongside the original values. This allows immediate comparison, making it much easier to interpret which items or categories are making the largest contribution to total results.

Note: When you select % of Parent Row Total in step 5, the percentage reflects each item's contribution to its respective subtotal (e.g., each product’s share within a shop), offering a more granular perspective on your data.

Adding percentage columns this way is useful for dashboards, quick performance analysis, and summarizing detailed data for presentations or management reports. However, if you need further customizations, such as conditional formatting or more advanced calculations, consider using calculated fields or supplementary Excel formulas for additional flexibility.

If you are interested in alternative approaches or need to achieve customized percentage calculations outside standard PivotTable options, try supplementing your report with Excel formulas or even automating your workflow with simple VBA. These methods offer more control, especially when built-in “Show Values As” settings don’t meet special requirements.

In some scenarios, you may want to display the percent of grand total directly next to your pivot table, or you require more formatting options than the built-in "Show Values As" feature offers. In this case, you can use Excel formulas outside the pivot table to perform the calculation.

1. Locate the numeric values column in your pivot table (for example, suppose your sales values are in cell range D5:D10). Next, identify the cell containing the grand total (such as D11). Alternatively, you can use the GETPIVOTDATA function to reference the total more reliably.

2. In the adjacent column (for instance, cell E5), enter the following formula to calculate the percent of grand total for each item:

=D5/$D$11

Or use this more robust version with GETPIVOTDATA (assuming the total value field is "Sales" and the pivot table starts in cell D4):

=D5/GETPIVOTDATA("Sales", $D$4)

These formulas divide each value by the grand total, ensuring a relative percentage is calculated for each row. Adjust the field name and cell references to match your actual pivot table layout.

3. Copy the formula down alongside the entire values range. For best results, format the new column as Percentage by selecting the range, right-clicking, choosing Format Cells, and picking Percentage.

Practical tip: This method offers flexibility for further customization (such as additional conditions or color-coding with conditional formatting). However, as your pivot table updates, double-check that formula references remain accurate—especially if items or rows change dynamically. Using GETPIVOTDATA helps prevent broken references in such cases.

For users who need to automate the process of adding a percent of grand total measure—especially when creating multiple pivot tables for reporting—VBA provides a customizable approach. This practical solution is ideal for recurring tasks or templates. Please follow these steps:

1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. In the VBA window, click Insert > Module, then copy and paste the following code into the module:

Sub AddPercentOfGrandTotal()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfNew As PivotField
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    
    If ActiveSheet.PivotTables.Count = 0 Then
        MsgBox "No PivotTable found on this sheet.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    Set pt = ActiveSheet.PivotTables(1)
    
    If pt.DataFields.Count = 0 Then
        MsgBox "No data field found in the PivotTable.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    Set pf = pt.DataFields(1)
    
    ' Check if the field already exists
    Dim fldName As String
    fldName = "Percent of Grand Total"
    On Error Resume Next
    Set pfNew = pt.PivotFields(fldName)
    On Error GoTo 0
    If Not pfNew Is Nothing Then
        MsgBox "Field '" & fldName & "' already exists.", vbInformation, xTitleId
        Exit Sub
    End If

    ' Add new field and apply percentage calculation
    Set pfNew = pt.AddDataField(pt.PivotFields(pf.SourceName), fldName, xlSum)
    With pfNew
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
    End With
End Sub

2. After inserting the code, click the “Run” button or press F5 to execute. The macro will automatically add a new field showing the percent of grand total in your existing pivot table on the current sheet.

Notes and Troubleshooting: This code assumes your PivotTable already contains at least one data field. If you want to target a specific PivotTable by name, you can replace ActiveSheet.PivotTables(1) with something like ActiveSheet.PivotTables("PivotTable1"). Always save your workbook before running new macros and ensure macros are enabled (check Trust Center settings if the code does not run).

How to sort by sum in Pivot Table in Excel?

How to automatically refresh a Pivot Table in Excel?