This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once with VBA in Excel.

Assuming that you have created a pivot table with your data. And as the default setting for numbers is to sum the data for all numeric fields and I want to change the count of function to sum of function. You can change each of columns using the value field setting option or you can also use an VBA macro to change all fields at once.

1. Change Multiple Pivot Table Fields by Manually

If you want to change multiple pivot table fields, you can change the function in the Value Fields Settings, just do the following steps:

Step1: select one filed in your pivot table, and right click on it, and then choose Value Fields Settings from the dropdown menu list. And the Value Fields Settings dialog will open.

Step2: select Count function in the Summarize value field by list box, and click Ok button.

Step3: you would see that the sum function has been changed to count function for your selected field.

Step4: you need to repeat the above steps to change other’s fields for your pivot table.

2. Change Multiple Pivot Table Fields by VBA

You can also convert all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel. Just do the following steps to run the macro:

Step1: select your pivot table that you want to convert fields

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

Step5: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub PivotFieldsToCount()
    Dim pf As PivotField
    With Selection.PivotTable
        .ManualUpdate = True
        For Each pf In .DataFields
            With pf
                .Function = xlCount
                .NumberFormat = "#,##0"
            End With
        Next pf
         .ManualUpdate = False
     End With
 End Sub

Note: If you want to convert all fields to SUM function, you just need to change “x1Count” to “x1Sum” in the above VBA code.

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

Step7: Let’s see the result: