Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers,

    I have a fairly large spreadsheet, sometimes when I open the spreadsheet the cell have been reformatted to accounting with 2 decimal places (originally formatted as General). So the cells have a $ - symbol in front of the cell value.

    Any ideas what might cause this and how to stop this from happening - using Excel 2010

    Regards

    Dean

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Dean

    Is the file you are opening an .xls file or an Excel2010 file?
    Is it being 'converted'?
    Are there macros in the file?

    zeddy

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Zeddy - thanks for the reply

    It is a .xlsm file about 1.5MB in size.No macros but excel seemed to want to save it in this file type.

    The re-formatting seems to happen on a random basis. Sometime when the file is opened after being saved its fine other times is re-formatted.

    Hope this helps

    Dean

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Dean

    The 'xlsm' format does suggest macros.
    However, if you are sure there aren't any, then try saving a copy of the file (with the formats you want) as type .xlsx file.
    Or, my favourite, save as a binary format file, .xlsb , as this will make the file size smallest and means it loads faster.

    If you still have issues after reloading the binary format file, double check that there are no conditional formats set for these cells.
    (A certain user-specified condition can trigger cells to display in specific formats when a specified condition is met).

    zeddy





  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Zeddy - will try the Save as option and see if that fixes it. I do have some conditional formatting, but all that does it to change the color of the cell based on the text in the cell. Can't imaging that this would cause the reformatting though - or could it?

    Regards

    Dean

  6. #6
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    It might also help to apply the actual number format that you want in those cells. As long as it is "general" excel will try to match whatever format it thinks the cells should have based on the number/text you have input, however, once a specific format has been applied it will not try to "guess" what format you want. (As long as you don't have any background code changing it as Zeddy suggests)

    (This is coming from 2003 - 2007 land, haven't spent much time with 2010 yet)

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Good call - will try that

    Thanks

    Dean

  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
    2007 and later can apply number formats with CF, so it may be worth double-checking that.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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