Number format in Pivot Tables

[Johannes]

I use pivot tables a lot and find Excel's default number format quite annoying. Each time i make a new table or place a new item to the data area, I need to manually change the number format.

I cannot find any place in Excel where the default settings for pivot tables can be changed, which would have been the easiest solution. However, I suspect that it might be possible to write a macro that would run through each item in the data area of the table and change the number format to my preferred settings, but I cannot figure out what the code should look like. Any creative ideas would be most welcome! Thanks in advance!

Johannes

[geekgirlau]

This will set the number format for all pivot tables on the current sheet:


Sub FormatPivotData()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
For Each pvtTable In ActiveSheet.PivotTables
For Each pvtField In pvtTable.DataFields
pvtField.NumberFormat = "#,##0.00"
Next pvtField
Next pvtTable
End Sub

[Johannes]

Great ! Works beautifully ! Thank you !