Formatting Pivot Tables

Posted on by Dick Kusleika

A few times in a man’s life, technology meets genius to create something truly remarkable. This is one of those times.

Applying a style to a cell with the keyboard is a pain. As I’ve mentioned in a previous post, I created a macro and assigned it the hotkey Ctrl+M.

Sub MakeComma()
   
    If TypeName(Selection) = “Range” Then
        Selection.Style = “Comma”
    End If
   
End Sub

Simple but effective. I love this macro. Another bane of my existence is formatting pivot fields in a pivot table. Right click, Field Settings, Number, etc., etc. So without further ado, I present to you the greatest macro ever written.

Sub MakeComma()
   
    Dim pf As PivotField
   
    If TypeName(Selection) = “Range” Then
        On Error Resume Next
            Set pf = ActiveCell.PivotField
        On Error GoTo 0
       
        If pf Is Nothing Then
            Selection.Style = “Comma”
        Else
            pf.NumberFormat = “#,##0.00”
        End If
    End If
   
End Sub

You know you’ve written something magical when you giggle like a school girl every time you run it. You’re welcome.

Posted in Uncategorized

25 thoughts on “Formatting Pivot Tables”

  1. You are a god walking among men. I bow before your awesome excel knowledge. How do I assign this to a macro key so that is always available?

  3. Great! Now that this problem has been solved, fill in the blanks below:

    Sub SolveWorldHunger()
    ‘blank
    End Sub

    Sub FixEarthClimate()
    ‘blank
    End Sub

    Sub PlugOilLeak()
    ‘blank
    End Sub

    There’s probably a VBA library for these kinds of things… good luck! :)

  4. I modified the first one to my favorite format: “Comma [0]” because I see a lot of people enter numbers like 12345 and 3423456, which are hard to gauge the magnitude of without straining my eyes.

    I’ll have to try the second one out because it too is the “bane of my existence” when formatting PivotTables. Thanks for the code. I’m sure I’ll be laughing here pretty soon, ’cause I don’t giggle.

  5. You have touched on a subject that has haunted me since I first began using Pivot Tables (PT).

    Why don’t fields in a PT default to the format of their respective data in the source table?

    If my source data is on a Excel sheet, it is very easy for anyone to manually apply formatting (currency, date, etc.). Even a relatively new user can create a macro in seconds to apply formatting using the macro recorder. None of those options are good enough. When you create a PT based on the formatted data, all the numbers are formatted as general. You have to format each field individually, which takes multiple clicks. This really stinks !!!!!!

    Until someone at Microsoft wakes up to this issue and does something about it, at least I know have this macro in my box of tools.

    Thank you very much,

    GL

  6. Hi Dick

    That’s excellent
    Like Gregory, I also like the comma separator for zero decimal numbers, so to make it variable, I modified your code as follows

    Sub MakeComma()

    Dim pf As PivotField, dec As Integer
    dec = InputBox(“Number of decimals”)
    If TypeName(Selection) = “Range” Then
    On Error Resume Next
    Set pf = ActiveCell.PivotField
    On Error GoTo 0

    If pf Is Nothing Then
    Selection.Style = “Comma” & ” [” & dec & “]”
    Else
    pf.NumberFormat = “#,##”
    If I Then
    pf.NumberFormat = “#,##” & “.” & Left(“0000?, dec)
    End If
    End If
    End If

    End Sub

  8. Dick, beyond what you’ve done, I also invariably rename the field because Excel put the supremely annoying “SumOf” in front of the fields, so using your post as inspiration, I wrote this today:

    Sub FormatPivotField()
    Dim szPivotTableName As String
    Dim szPivotFieldName As String
    Dim szPivotFieldNewName As String
       
        szPivotFieldNewName = InputBox(“Enter the new name for  the field”, “Pivot Field Rename”)
       
        szPivotTableName = Selection.PivotTable.Name
        szPivotFieldName = Selection.PivotField.Name
       
        With ActiveSheet.PivotTables(szPivotTableName).PivotFields(szPivotFieldName)
            .NumberFormat = “_(* #,##0_);_(* (#,##0);_(* ““-““_);_(@_)”
            .Caption = szPivotFieldNewName
        End With

    End Sub

    I need to spend a few minutes tweaking the code so I can choose between my ,0 format and a percentage format with 1 decimal place as those are the two most common formats I use. I also need some error checking logic. Right now, it is best if the cursor is on the field name of field you want to modify.

  9. If you don’t want to use a shortcut you can always add the macro to the Pivot Table Context Menu (Right Click Menu).

    I created an AddIn and included this in the AutoExec code.

    Application.CommandBars(“PivotTable Context Menu”).Reset

    With Application.CommandBars(“PivotTable Context Menu”).Controls

         With .Add
            .Caption = “Format: Currency”
            .OnAction = ThisWorkbook.Name & “!PivotCurrency”
            .BeginGroup = True
         End With
             
    End With

    This allows to select one cell in a pivot table, right click and select the Pivot Curency and it will format the entire column. The error handling is a joke but it works for my purposes.

    Private Sub PivotCurrency()

    On Error Resume Next

    With Selection
        With ActiveSheet.PivotTables(.PivotTable.Name).PivotFields(.PivotItem.Name)
            .NumberFormat = “#,##0.0_);(#,##0.0)”
        End With
    End With

    End Sub

  10. Dude! this has gone straight to my custom toolbar where it will occupy a place of prominence

  11. I think you are testing the wrong variable Roger, and there is a superfluous concatenation.

    I think it should be

    If CBool(dec) Then
    pf.NumberFormat = “#,##0.” & Left(“0000?, dec)
    End If

  13. Thanks Dick, I love your magic, I’ve been using my hotkey many times already this week :)
    99 out of a 100 times I need to set my pivot field to the summary type and the same time, so I’ve made one further modification to set the function type. By setting the Function type first this will already trigger a default name starting with CountOf.. to be updated to SumOf.. before it is supplied as default value in the InputBox asking for a possible name change.

    Sub MakeComma()
        Dim pf As PivotField
        If TypeName(Selection) = “Range” Then
            On Error Resume Next
                Set pf = ActiveCell.PivotField
            On Error GoTo 0
            If pf Is Nothing Then
                Selection.NumberFormat = “#,##0_);[Red](#,##0)”
            Else
                pf.Function = xlSum
                pf.NumberFormat = “#,##0_);[Red](#,##0)”
                pf.Name = InputBox(“Enter the new name for the field “ & Chr(13) & “(Please note the field summary type is now SUM!)”, “Pivot Field Rename”, pf.Name)
            End If
        End If
    End Sub

  15. Pivot tables are a great tool, however, one of my greatest frustrations is seeing them get discarded due to integrity issues. I can appreciate the formatting tools offered but was wondering if anyone else is having problems with their pivot tables being trashed by Excel.

    I ended up writing a macro to re-build the pivot table on the fly but would like to think there is a more elegant solution.

    I’ve gone through great lengths making sure my service packs are up to date and more … with no success. My spreadsheet is password protected and I can only think that this may be the root of the problem. Any advice would be appreciated.

    By the way, thanks for the great code snippets. They work wonders.

  20. Thanks! I love pivot tables, and go cross-eyed staring at numbers without formatting.

    Doing what you’ve done has been on my long list of things to do, but I didn’t know how to do what you did. I’ve added some tricks I know to your code to accommodate several different formats that I use most frequently.

    Sub Format_PTFields()
    ‘Macro goal: allow users to quickly choose the format to apply to pivot table fields

    ‘Code modified from Dick Kusleika’s code at:
    ‘http://www.dailydoseofexcel.com/archives/2010/06/18/formatting-pivot-tables/

       
        Dim pf As PivotField
        Dim FormatChoice As String ‘allows you to dynamically select the format
        Dim QuestionString As String
       
        On Error GoTo HandleErr
       
        If TypeName(Selection) = “Range” Then Set pf = ActiveCell.PivotField
           
       
        ‘Consolidates the question blurb to a variable
        QuestionString = “Apply which format to this pivot field?” & vbCrLf & _
                    ”    ‘0’: numbers with 0 digits after the decimals” & vbCrLf & _
                    ”    ‘1’: numbers with 1 digit after the decimals” & vbCrLf & _
                    ”    ‘d’: dollars (no cents)” & vbCrLf & _
                    ”    ‘c’: dollars and cents”
       
        ‘Ask the user what format to apply
        FormatChoice = InputBox(QuestionString)
       
        ‘based on the FormatChoice, format the selected pivot field
        Select Case FormatChoice
            Case 0      ‘shows numbers with 0 digits after the decimal
                pf.NumberFormat = “#,##0”
           
            Case 1      ‘shows numbers with 1 digit after the decimal
               pf.NumberFormat = “#,##0.0”
               
            Case “d”    ‘shows dollars (no cents)
                pf.NumberFormat = “$#,##0”
               
            Case “c”    ‘Shows dollars and cents
                pf.NumberFormat = “$#,##0.00”
        End Select
       
    ExitSub:
        Exit Sub
       
    HandleErr:
        If Err = 1004 Then
            MsgBox (“This macro only does something useful if you are “ & vbCrLf & _
                    “in a pivot table value field.  Exiting macro.”)
        Else
            MsgBox “Unexpected Error: “ & Err & Err.Description
        End If
       
        GoTo ExitSub
       
    End Sub

  21. question, I have a pivot table with time reflected in multiple columns, what I’d like to due is format it so that if there is no time in a cell is shows nothing right now its shoowing “(blank)”, it makes the report hard to see the information I’m trying to show….help anyone?

  22. Like Pauline sad, when you got blanks in your data you can’t apply NumberFormat.

    The only thing you can do:
    1. put some fake value in the data
    2. refresh pivot
    3. apply NumberFormat
    4. delete fake data (if you put a ” ” in the blank cells here you will get rid of the “(blank)” in the pivot)
    5. refresh pivot

    In my case I have a fraction and the pivot is showing it as a number. Unfortunately I have blanks in between.

  23. Dave,

    thanks for this, I was getting tired of formatting pivot table fields using the dialogs each time, the macro really helped.

    I made a slight addition to your macro, so that it would work for multiple pivot table cells. Say I wanted to format 5 columns the same, I can select cells in each column (using ctrl-select if the columns aren’t all together), then using the below version of the macro will format all of them

    Chris

    Sub MakeCommaDollarValueFormat()

    Dim pf As PivotField
    Dim c As Range

    If TypeName(Selection) = “Range” Then
    For Each c In Selection
    On Error Resume Next
    Set pf = c.PivotField
    On Error GoTo 0

    If pf Is Nothing Then
    Selection.Style = “Comma”
    Else
    pf.NumberFormat = “$#,##0.00;-$#,##0.00;”
    End If
    Next c
    End If

    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.