+ Reply to Thread
Results 1 to 4 of 4

Pivot Chart issue

  1. #1
    Registered User
    Join Date

    Pivot Chart issue

    I am new to charting. I was able to kill two birds with stone for my manager. I created a pivot along with an attached pivot chart. It worked great. Then he wanted the values on each bar rotated 90 degrees and placed at the end of the each bar. I figured that out too! Then he wanted the bar colors changed. Did that one too. The only problem is that when he refreshes the pivot, the chart looses the formatting changed I made...Boooooo!

    How can I preserve my formatting changes on the pivot chart?

  2. #2
    Forum Contributor
    Join Date
    Yorkshire, England

    Pivot Chart Formatting


    I dont know if this will help you but i have never found a way to keep the format of a pivot chart after a refresh of the data. However when i was reformatting a pivot table i recorded it as a macro and openned VBA (Alt F11) and copyed it into the 'This Workbook' sheet (down the left hand side bar under the different sheet names). I then trimmed it down by removing the existing sub and macro name and and put it under a new heading as per below.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
    As Boolean, Cancel As Boolean)
    ' Start of Macro
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
    "Line - Column on 2 Axes"
    ' End of macro
    End Sub

    So when ever i save my workbook it will reformat the pivot charts.
    An easier way might be to assign your recorded macro to a button called 'Update' so you wont need to trim it down and can just click the button to update. You can make a button with just a normal auto shape and right click to assign macro )

    Hope it helps

  3. #3
    Registered User
    Join Date


    I did something similar. I guess this has been an issue for some time....you'd think the brain power at MS would have fixed it by now.

  4. #4
    Registered User
    Join Date

    Try saving the chart type as a user-defined format

    This might help,

    => Format the chart as you'd like
    => Right Click the chart;
    => Select Chart Type
    => Select Custom Types tab
    => Select Radial button "User-defined"
    => Click Add
    => Name it and save it.

    You should be able to refresh at will.

    You can also then use the custom type for other charts (you'll likely need to make minor changes, however)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1