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

Hi Richard

Don't format the columns.
Right click on PTWizardLayoutDouble 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 areaNumberFormat
--
Regards
Roger Govier

"ra" wrote in message
...
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

On 3 Mar, 17:44, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Richard

Don't format the columns.
Right click on PTWizardLayoutDouble 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 areaNumberFormat
--
Regards
Roger Govier

"ra" wrote in message

...



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- Hide quoted text -

- 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?

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 PTAlt+F8Select macro SumAllValueFieldsRun
--
Regards
Roger Govier

"ra" wrote in message
...
On 3 Mar, 17:44, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Richard

Don't format the columns.
Right click on PTWizardLayoutDouble 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
areaNumberFormat
--
Regards
Roger Govier

"ra" wrote in message

...



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- Hide quoted text -

- 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?

On 4 Mar, 15:42, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
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 PTAlt+F8Select macro SumAllValueFieldsRun
--
Regards
Roger Govier

"ra" wrote in message

...



On 3 Mar, 17:44, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Richard

Don't format the columns.
Right click on PTWizardLayoutDouble 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
areaNumberFormat
--
Regards
Roger Govier

"ra" wrote in message

....

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- Hide quoted text -

- 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?- Hide quoted text -

- Show quoted text -

Excellent, that works perfectly. Many thanks!!

On Mar 4, 10:42*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
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 PTAlt+F8Select macro SumAllValueFieldsRun
--
Regards
Roger Govier

"ra" wrote in message

...



On 3 Mar, 17:44, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Richard

Don't format the columns.
Right click on PTWizardLayoutDouble 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
areaNumberFormat
--
Regards
Roger Govier

"ra" wrote in message

....

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- Hide quoted text -

- 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?- Hide quoted text -

- Show quoted text -

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