How to change multiple pivot table fields to Sum Function

Changing multiple pivot table fieldsThe Pivot table is an incredibly powerful tool for summarising data. Excel will either default to summing or counting the field data but you can choose from 11 different functions that include min, max and StdDev as well as the more common Sum, count and Average.

Why does Microsoft Excel Sum Data instead of Counting data?

If the data you are pivoting contains only numeric data then Excel will sum the data. If there are any blank cells or text cells (other than the header), then Excel will count the data.

I quite often end up with blank cells in my pivot table data. For convenience, I will regularly base my pivot table on a data range that is much larger than the populated data range. I do this so that additional rows of data can be added without having to update the range that the pivot table is looking at. (It is much better to create dynamic pivot table ranges though.)

In the first example, my pivot table is pulling from the populated range and all my fields default to the sum function.

Changing multiple pivot table fields

In the second example, I have based my pivot table on the columns A:G. This includes many thousands of blank rows and as a result all the fields default to the Count function.

Changing multiple pivot table fields

How to change the Summary Calculation in a pivot table

To force Excel to use the Sum function instead of Count, right-click a pivot table cell in the column you wish to change. Choose Summarize Values By and then tick Sum.

Changing multiple pivot table fields

You can use the same method to select any of the other summary functions.

Can you change the summary calculation of multiple pivot table fields at once?

Unfortunately there isn’t an easy way to change multiple fields to the same function and right-clicking on a load of data fields gets boring very quickly. If you find yourself having to do this on a regular basis it is worth setting up a macro in your personal workbook so that you can call upon it to very easily change the summary function of every data field in your pivot table.

Here is a short macro that converts all the fields in a selected pivot table to the Sum function. Make sure you have selected the pivot table to change before playing the macro.

Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
 
Dim pf As PivotField
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

As requested by a commenter, here’s the macro again with the option for the user to select the type of summary function.
It isn’t the neatest option as I struggled to pass the user input directly such as .Function = SubTotalType

Public Sub PivotFieldsToSumUserInput()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
Dim SubTotalType As String
Dim pf As PivotField
 
'Add a user input box with default value xlSum
SubTotalType = InputBox("What type of summary do you want? Options are: xlSum, xlAverage, xlCount, xlMax, xlMin", "Summary Type", "xlSum")
 
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
'.Function = xlSum
 
'Cycle through predefined summary types
If SubTotalType = "xlMin" Then
.Function = xlMin
    ElseIf SubTotalType = "xlCount" Then
    .Function = xlCount
    ElseIf SubTotalType = "xlAverage" Then
    .Function = xlAverage
    ElseIf SubTotalType = "xlMax" Then
    .Function = xlMax
    Else
    .Function = xlSum
End If
 
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

  • Arjo

    Great tutorial! i helped me change my pivot fields very fast.
    However, 1 question: although the pivotfields are changed to xlsum. The name / caption (whatever it is called) is still “count …”
    I thought: I change this with the same code, simply adding pf.name = “…”

    But now all my fields are: “sum count of wk.. ”

    So: How to get rid of the “count caption” in the fieldnames that are displayed in the pivot?

    The excel macro recorder showed me this when I changed one name, but I does not solve my issue. I just want the pivot to show the fields as they are shown in the list with pivot fields which pops up when you select the pivot.

    How to do this?

    • Maria

      Download this add-in. Saves you the trouble of writing a macro. AND the labels are “sum” and not “count” after the change.

      http://www.contextures.com/xlPivotAddIn.html

      There’s a link to a free add-in on the same page

  • Ashwani

    The macro was very helpful to me. Thanks .

  • Vaibhav

    Thanks for the macro!!
    It was very helpful.

  • Michiel

    Great Macro – thanks a lot this saved me a lot of time and annoying work!!

  • Erin Dalton

    Thanks for the macro!

  • Pingback: Change the summary calculation of multiple pivot table fields at once using Macro (Excel) | Zulkarnain Hassan()

  • EV

    Finally! Really helpful to make the dumb work! Thank you!

  • hadi

    Thank you a million times πŸ™‚
    Your Macro is great .. it helped a lot ..

  • Luoi Cay

    Could we let user input dialogue box value for Sum or Count or other functions then the macro will change to specific desired function for multi fields? Please help the code with many thanks.

    • Well this isn’t the neatest option but it does work:

      Public Sub PivotFieldsToSum()
      ‘ Cycles through all pivot data fields and sets to sum
      ‘ Created by Dr Moxie
      Dim SubTotalType As String
      Dim pf As PivotField

      SubTotalType = InputBox(“What type of summary do you want? Options are: xlSum, xlAverage, xlCount, xlMax, xlMin”, “Summary Type”, “xlSum”)

      With Selection.PivotTable
      .ManualUpdate = True
      For Each pf In .DataFields
      With pf
      ‘.Function = xlSum
      If SubTotalType = “xlMin” Then
      .Function = xlMin
      ElseIf SubTotalType = “xlCount” Then
      .Function = xlCount
      ElseIf SubTotalType = “xlAverage” Then
      .Function = xlAverage
      ElseIf SubTotalType = “xlMax” Then
      .Function = xlMax
      Else
      .Function = xlSum
      End If

      .NumberFormat = “#,##0”
      End With
      Next pf
      .ManualUpdate = False
      End With
      End Sub

  • reddy

    this is great and saves lot of time… tks a million…..

  • Pazhani selvam J

    great macro
    thanks

  • Jennifer Taylor

    This is marvelous! Thanks so much for having this online for us to find πŸ™‚

  • Christian De Santis

    Hello, this is a great macro! Any idea how to add the std deviation to the code? I tried but unsuccessfully. πŸ™

  • David Sellnow

    Found a way to add all variable types once you have all your data in place as a “Sum of” calculation.

    Public Sub AddPivotDataToSumFields()
    ‘ Cycles through all pivot data fields and sets to sum
    ‘ Created by Dr Moxie

    Dim pf As PivotField
    Dim FieldName As Variant
    Dim TableName As Variant
    With Selection.PivotTable
    TableName = .Name
    .ManualUpdate = True
    For Each pf In .DataFields
    With pf
    FieldName = Mid(.Caption, 8)
    Selection.PivotTable.AddDataField ActiveSheet.PivotTables( _
    TableName).PivotFields(FieldName), “Max Of ” & FieldName, xlMax
    Selection.PivotTable.AddDataField ActiveSheet.PivotTables( _
    TableName).PivotFields(FieldName), “Min Of ” & FieldName, xlMin
    Selection.PivotTable.AddDataField ActiveSheet.PivotTables( _
    TableName).PivotFields(FieldName), “StdDev Of ” & FieldName, xlStDev
    Selection.PivotTable.AddDataField ActiveSheet.PivotTables( _
    TableName).PivotFields(FieldName), “Var Of ” & FieldName, xlVar
    End With
    Next pf
    .ManualUpdate = False
    End With
    End Sub

  • Abhishek Anand

    AMAZING….THANX ALOT FOR THIS…..