Pivot table Field Setting Number Format

[ra]

Hello All,

Is there a way to globally define number formats in a Pivot table?
I'm familiar with going Pivot Table-->Field Settings-->Number, but
that only sets the format for a column.
I would like to be able to set one number format to apply to all
columns in a table.


Is this possible?

thanks and regards

Richard

 

[Roger Govier]

Hi Richard

Don't format the columns.
Right click on PT>Wizard>Layout>Double click the Data Field(s))>Number
Format

The number format will be applied to all columns holding that data, as well
as the Grand Totals.

If using XL2007, just right click on any cell in the data area>Number>Format

 

[ra]

Hi Richard

Don't format the columns.
Right click on PT>Wizard>Layout>Double click the Data Field(s))>Number
Format

The number format will be applied to all columns holding that data, as well
as the Grand Totals.

If using XL2007, just right click on any cell in the data area>Number>Format
--
Regards
Roger Govier










- Show quoted text -

Thanks for your response Roger. I understand how to format the Data
Feilds as per your instruction however if I have 50 Data feilds can I
Format all without Double Clicking on each individual one?

 

[Roger Govier]

Hi

It can only be done with VBA for all data fields.
Below is a modification of some code from Debra Dalgleish, which sets each
data field to be Sum, and I have added number format to it as well.

Sub SumAllValueFields()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet

Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Application.ScreenUpdating = False

pt.ManualUpdate = True
For Each pf In pt.DataFields
pf.Function = xlSum
pf.NumberFormat = "#,##0.00"
Next pf
pt.ManualUpdate = False

Application.ScreenUpdating = True
End Sub

Amend the number format to suit your requirement.

Copy the Code
Alt+F11 to invoke the VB Editor
Insert Module
Paste code into large white pane that appears
Alt+F11 to return to Excel
Navigate to Sheet with your PT>Alt+F8>Select macro SumAllValueFields>Run

 

[ra]

Hi

It can only be done with VBA for all data fields.
Below is a modification of some code from Debra Dalgleish, which sets each
data field to be Sum, and I have added number format to it as well.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
      pf.NumberFormat = "#,##0.00"
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
End Sub

Amend the number format to suit your requirement.

Copy the Code
Alt+F11 to invoke the VB Editor
Insert Module
Paste code into large white pane that appears
Alt+F11 to return to Excel
Navigate to Sheet with your PT>Alt+F8>Select macro SumAllValueFields>Run
--
Regards
Roger Govier







- Show quoted text -

Excellent, that works perfectly. Many thanks!!

 

[jcook7]

Hi

It can only be done with VBA for all data fields.
Below is a modification of some code from Debra Dalgleish, which sets each
data field to be Sum, and I have added number format to it as well.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
      pf.NumberFormat = "#,##0.00"
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
End Sub

Amend the number format to suit your requirement.

Copy the Code
Alt+F11 to invoke the VB Editor
Insert Module
Paste code into large white pane that appears
Alt+F11 to return to Excel
Navigate to Sheet with your PT>Alt+F8>Select macro SumAllValueFields>Run
--
Regards
Roger Govier







- Show quoted text -

I just came accross this post and it worked great for me too! Exactly
what I was looking for...thanks again!