Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    When Copying Worksheet to another Workbook Percentages convert to Decimals

    Hello All,
    I have a worksheet that contains charts I copied to a different workbook. When the source workbook is open the Percentage formatting on the charts display properly, when I close the source workbook the percentages convert to decimal in the destination workbook. Any suggestions?

    Thanks
    MOState

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MOState,

    Did you copy as link or value?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    The entire worksheet was copied to another workbook. Thanks for the response!

  4. #4
    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
    Are the formats linked to source? That would do it - if the source is closed the format isn't read, only the value.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Yes, they are linked to the source. So, would the only resolution be to break the link? I would like to keep the link between the 2 workbooks if possible...

  6. #6
    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
    I'm not talking about the data link. If you change the number format (I don't know if you're talking about axes/datalabels/other) so that it's manually set to a Percentage and uncheck the 'linked to source' option, the problem should go away.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    That works great for the Axes but not the data table. Is there a way to format the number format to % in the data table?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I believe that the data table reads the formatting of the source data and can not be explicitly set. Unfortunately linked data from closed workbooks seems to only look at the values, not the formatting. To work around that, i would suggest creating your own table from the data in a worksheet range and place a copy of the chart above this range. Some details can be found at http://www.tushar-mehta.com/excel/ne...able/index.htm

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    MOSTATE (2014-10-15)

  10. #9
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks Steve, I think that's the solution I will go with!

Posting Permissions

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