Change All Number Formats in Pivot Table

Last week, I asked for your input in my survey on pivot table number formats. There were 3 questions in the survey:

1. Which number format do you use most often in pivot tables?
2. How many decimal places do you usually set?
3. If you use Number format, do you usually include the 1000s separator?

Thanks for your responses, and you can see the results below.

Survey Results

There were 84 responses when I last looked, and I created pivot tables from the votes.

1. For the number format, Number as the clear winner, with 73% of the votes. Currency was a distant second, with only 11%.
2. The number of decimal places was almost tied – zero places had 45% and 2 places had 44%.
3. Another strong winner was “Yes” for the 1000s separator, with 85% of the votes.

Improving the PivotPower Premium Add-in

I was working on an update to my PivotPower Premium add-in, and wanted to see which format should have its own button on the Ribbon. In the old version, the Currency format was featured, but this survey shows that it isn’t the favourite. So, in the new version, the featured format is Number, with zero decimals. With one click, you can change all the value fields to that format.

Create Your Own Number Format Macro

If you haven’t bought a copy of my add-in, you can create your own macro, to change all the Value fields to the Number Format, with zero decimals.

Copy the code below, and store it in your Personal Macro Workbook. Then, add a button to the Quick Access Toolbar, so it’s easy to run. You can see instructions on my Contextures Blog.

Sub PT_Num_0Dec_All()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveCell.PivotTable

If Not pt Is Nothing Then
   For Each pf In pt.DataFields
      pf.NumberFormat = "#,##0"
   Next pf
Else
   MsgBox "Please select a pivot table cell."
End If

End Sub

______________