How to Change Multiple Pivot Table Fields in Excel

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.

change multiple pivot table fields1

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

change multiple pivot table fields2

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

change multiple pivot table fields3

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.

Get the position of the nth using excel vba1
Step3: then the “Visual Basic Editor” window will appear.

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

export each sheet to csv2

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

change multiple pivot table fields4
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.

change multiple pivot table fields5

Step7: Let’s see the result:

change multiple pivot table fields6

Leave a Reply