Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preserving format changes in Pivot charts (Excel 2003)

    I've been designing various Pivot charts in Excel 2003, linked to accompanying Pivot tables. These involve graphing two or more variables, whether is line or bar graphs. I have no problem with making various changes to the line or bar: color, thickness, style, etc. Here is my problem: suppose I make these changes when the Page field is set on "All" then save. If I then toggle from "All" to display a single item rather than All, the lines/bars revert back to their automatic settings and I lose the changes I have made. In fact, if I then toggle back to "All" (where these changes were made then saved), i still get the automatic format as far as color, thickness, etc.

    Is there some way to make such changes and not lose them? I am designing reports that are meant for their end users to be able to manipulate at will.

    Thanks!

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

    Re: Preserving format changes in Pivot charts (Excel 2003)

    This is a known bug/feature (pick your choice), see MSKB article Changing a PivotChart Removes Series Formatting. The only workaround is to create a macro that formats the chart again. You'll find a simple example in <post#=445508>post 445508</post#>.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    or upgrade... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Has this problem been solved in Excel 2007?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Yes, finally. (at least according to Mr. Walkenbach - I will be testing it a little later just to verify for myself!)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Many thanks!

  7. #7
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Thanks, Rory.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Just checked and it does indeed now preserve the formatting. Didn't take long...
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Macros have proven to be a great solution here. Since they differ slightly from workbook to workbook, it's just a matter of a specific one for each workbook.

    As these workbooks are shared with others who may in turn share them with others, rather than using a keyboard shortcut to run the macro, I was wondering if Excel 2003 allows me to create a "Reformat Chart" button that could be placed on each chart to run the macro when clicked?

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

    Re: Preserving format changes in Pivot charts (Excel 2003)

    You can use a command button from the Forms toolbar or from the Control Toolbox on top of the chart (or just above or below it, whatever you prefer).

  11. #11
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preserving format changes in Pivot charts (Excel 2003)

    Thanks as always!

Posting Permissions

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