How Do I Quickly Change a Pivot Field Number Formatting with VBA?

gloomy

Is there a way I could change the number formatting of all my pivot fields without having to spend forever on it?!

Sure! The VBA snippet below has some of the most common number formats, which you can directly re-use in your code!

Sub ChangeNumberFormats()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
For Each PF In PT.DataFields
If PF.SourceName Like "*Spend" Then
'comment as needed
PF.NumberFormat = "0.00" 'shows only two decimals
PF.NumberFormat = "#,###" 'shows numbers as thosands
PF.NumberFormat = "0,##" 'shows a leading zero in numbers smaller than a thousand
PF.NumberFormat = "#,##0" 'shows minus sign for negative values
PF.NumberFormat = "#,##0_);(#,##0)" 'shows negative numbers in brackets
PF.NumberFormat = "[$$-en-US]0.00" 'dollar currency symbol
PF.NumberFormat = "[$€-x-euro2] #,##0.00" 'euro currency symbol
PF.NumberFormat = "[$£-en-GB]#,##0.00" 'pound currency symbol
PF.NumberFormat = "0%; -0%;" 'hides zero values but keeps the negative ones
PF.NumberFormat = "0.0%" 'formats as %
End If
Next PF
Next PT
Next Wks
End Sub

PS. Do make sure to un-comment the formats that you do not need! Otherwise your preferred one may get overwritten.

Also, the code above will modify all the value fields in all your pivot tables across your workbook.If that’s not what you need, you will have to implement some conditional statements

 

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.