Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Number formats (2002 sp3)

    I often use pivot tables to summarise data and was wondering if there was any way to change the default format that is applied to my data. The vast majority of the time, I use the thousands separator and zero decimal places. This means that I have to go through and manually set the format for every data field. There has to be a better way?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Number formats (2002 sp3)

    See the thread starting at <post#=508,205>post 508,205</post: >.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Number formats (2002 sp3)

    Thanks. I had hoped to be able to change it without resetting 'normal'.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Number formats (2002 sp3)

    <P ID="edit" class=small>(Edited by jsquared on 22-Aug-05 21:23. Revised code to allow selection of multiple fields)</P>I've been thinking about my question and have written this little bit of code to change the format field by field. I select the field headings whose formats I want to change and run the macro. It's pretty rustic but seems to work for what I want. Any suggestions or advice would be appreciated. Especially if there's an easy way to trap errors e.g., selected cells not in a pivot table etc.,

    Sub PivotFormat()

    For Each cell In Selection
    ThisPivot = cell.PivotTable.Name
    Thisfield = cell.Value

    ActiveSheet.PivotTables(ThisPivot).PivotFields(Thi sfield).NumberFormat = "#,##0"

    Next cell

    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Number formats (2002 sp3)

    If you want to format all data fields, you can use this:

    Sub FormatPivot()
    Dim pf As PivotField
    For Each pf In ActiveSheet.PivotTables(1).DataFields
    pf.NumberFormat = "#,##0"
    Next pf
    End Sub

    This assumes that the pivot table is the first (or only) one on the active worksheet.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Number formats (2002 sp3)

    I create a number of p-ts in one file and would like to be able to run this as needed. Not sure how to say "this pt I'm in" as opposed to PivotTables(1) or all. How could I do that? TYIA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Number formats (2002 sp3)

    Do you have multiple pivot tables on one worksheet?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Number formats (2002 sp3)

    The following version will act on the pivot table that contains the active cell:

    Sub FormatPivot()
    Dim pt As PivotTable
    Dim pf As PivotField
    For Each pt In ActiveSheet.PivotTables
    If Not Intersect(ActiveCell, pt.TableRange2) Is Nothing Then
    Exit For
    End If
    Next pt
    If pt Is Nothing Then
    MsgBox "Active cell is not within a pivot table!", vbExclamation
    Exit Sub
    Else
    For Each pf In pt.DataFields
    pf.NumberFormat = "#,##0"
    Next pf
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •