Change All Number Formats in Pivot Table

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.

numberformatresults01

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.

numberformatresults02

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

______________

3 thoughts on “Change All Number Formats in Pivot Table”

  1. Hi Debra – Thanks for the article. I once wrote a quick utility that resets the number format of each Value field to the number format found in row 2 of the data set. I understand why Excel won’t do this (the data is now stored in the cache, without formatting), but carrying through the formatting from the original data set would just be a nice thing to have happen!

    1. @Bill, you’re right! It would be great if the original number formats were carried over to the pivot table. I like your idea of grabbing the formats from the source data.

  2. I agree with Bill that it would be great to use the source formatting. It would also be nice if the Comma and Comma [0] cell styles were added the Number Format list, as it seems like those are the most popular choices.

    A survey on how many people use cell styles would also be interesting. I’m guessing the number is pretty low.

    Thanks for the article!

Leave a Reply

Your email address will not be published. Required fields are marked *

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