none
Setting default format for all the pivot table fields

    Question

  • Hi,

    I want to set all the fields in *Pivot Table Field List* to have default format (say Number) irrespective of what we have in the column before we add it to the Pivot table.

    We can do for the fields in the Pivot table using following macro:

        For Each pt In ActiveSheet.PivotTables
            For Each pField In pt.DataFields
                pField.NumberFormat = "#,##0"
                Debug.Print pField
            Next pField
        Next pt

    but field which are not added to the Pivot by default have General format

     

    Any solutions?

     

    thanks,

    Kishor

     

     

    Thursday, October 13, 2011 4:41 AM

Answers

  • In user Interface if you select any cell of pivot table you will see a fieldlist in right side of sheet.In below of that list there is 4 boxes Report Filter ,Column Labels,Row Labels,Values.

    After running the my macro if you add any field in row label (RowFields),column label(ColumnFields),report filter(PageFields) the changed format will be affected.But if you add in value area the format will not be changed.In bracket I have mentioned the object name in VBA.

    However if it is essential that all the automatically converted to number format you can trap excel events.Some are given below.

    1.Worksheet_PivotTableUpdate

    2.Workbook_SheetPivotTableUpdate

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim pf As PivotField

        For Each pf In Target.DataFields
            pf.NumberFormat = "0.00"
        Next pf

    End Sub

    But pls keep in mind the above event will be fired for any change in a pivot table.

    (You will insert this code in the Sheet module where the pivottable is present.

    • Marked as answer by Liliane Teng Friday, October 21, 2011 9:02 AM
    Friday, October 14, 2011 6:01 AM

All replies

  • You can use the following:

    Actually if a field is text then then it's number format can't be changed throgh pivot table.Error will be generated in that case.

        On Error Resume Next
        
        For Each pt In ActiveSheet.PivotTables
            For Each pField In pt.PivotFields
               pField.NumberFormat = "#,##0"
               Debug.Print pField
            Next pField
        Next pt

    Pls mark as answer if this post helps

    Thursday, October 13, 2011 5:59 AM
  • Thanks for your quick reply

    >> Actually if a field is text then then it's number format can't be changed throgh pivot table.Error will be generated in that case.

    Note that the field is number field in the table but when it appears in the Pivot table it appears in general format which is wrong.
    Also manually I am able to change the format of the field ( using value field settings and clicking on Number format)

    Is there any way I can set number format setting for fields which are not added to Pivot table ( I presume that after adding the column we can do it manually or using macro in Update#1)

    Please correct if I am thinking wrong.

    Thanks,

    -Kishor

    Thursday, October 13, 2011 6:59 AM
  • Probably you have not noticed I have used PivotFileds property of PivotTable.It includes all the field of PivotTable even not added in Table.If later on you add any field it will be in the specified numberformat.

    I have used On error Resume next.Because if a field is TextLabel then error will be generated for that field.

     

    Thursday, October 13, 2011 9:53 AM
  • Sorry I missed your point.

     

    I have two macros:

     

    This formats the columns which are added to Table

    Sub PivotMacro()

        ActiveWorkbook.Sheets("Sheet2").Select
        Dim pt As PivotTable

        For Each pt In ActiveSheet.PivotTables
            For Each pField In pt.DataFields
                pField.NumberFormat = "#,##0"
            Next pField
        Next pt



    End Sub


    After running this,  even I add colum from the list it doesn't have number format set by default

        ActiveWorkbook.Sheets("Sheet2").Select
        Dim pt As PivotTable
        On Error Resume Next
        For Each pt In ActiveSheet.PivotTables
            For Each pField In pt.PivotFields
                pField.NumberFormat = "#,##0"
                Debug.Print "Formatted " & pField
            Next pField
        Next pt



    End Sub

    Can you please help me here ?

     

    Thanks,

    -Kishor


    • Edited by kishor.iitr Thursday, October 13, 2011 11:33 AM
    Thursday, October 13, 2011 11:32 AM
  • If you add a field in data then general format is applied.I have explored the object & properties but not found any way of specifying the format before adding as datafield 

    You may apply the macro and later on If you add any field to row/page/column (Except data) area the specified formatting will work.

    Thursday, October 13, 2011 12:14 PM
  • >>If you add a field in data then general format is applied.I have explored the object & properties but not found any way of specifying the format before adding as datafield

    Okay is there any feature in Excel 2010 to do the same?

     

    >> You may apply the macro and later on If you add any field to row/page/column (Except data) area the specified formatting will work.

    I am relatively new to Macro programming; Can you please explain with an example ?

     

    Thanks a lot for your help!

    -Kishor

    Thursday, October 13, 2011 4:08 PM
  • In user Interface if you select any cell of pivot table you will see a fieldlist in right side of sheet.In below of that list there is 4 boxes Report Filter ,Column Labels,Row Labels,Values.

    After running the my macro if you add any field in row label (RowFields),column label(ColumnFields),report filter(PageFields) the changed format will be affected.But if you add in value area the format will not be changed.In bracket I have mentioned the object name in VBA.

    However if it is essential that all the automatically converted to number format you can trap excel events.Some are given below.

    1.Worksheet_PivotTableUpdate

    2.Workbook_SheetPivotTableUpdate

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim pf As PivotField

        For Each pf In Target.DataFields
            pf.NumberFormat = "0.00"
        Next pf

    End Sub

    But pls keep in mind the above event will be fired for any change in a pivot table.

    (You will insert this code in the Sheet module where the pivottable is present.

    • Marked as answer by Liliane Teng Friday, October 21, 2011 9:02 AM
    Friday, October 14, 2011 6:01 AM