Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Excel unable to save file; huge file size

    Hi all....I have a file that has jumped from about 4.5MB to almost 20MB......I have had this occur before, and at that time, I saved it as an XML file (and re-saved it as an XLS file), it cured the mushrooming size........however, at this time, that 'trick' is not working. I am getting a message saying that Excel can't save the data and formatting that I have recently added......and when I try to save it as an XML file, I get an error message that says that Excel may not be able to proerly open the XML s/sheet b/c there is at lest one sheet with more than 65,550 formulas that refer to defined names or cells on other sheets....is there a way to correct this, other than removing all the formatting etc? Thanks
    Last edited by dmcnab; 2011-04-10 at 18:38. Reason: add more info

  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
    According to Microsoft there is a limit: Cross-worksheet dependency -- 64,000 worksheets that can refer to other sheets. Also Unique cell formats/cell styles -- 64,000. I don't know if one of these is causing you problems but check the link for other limitations of Excel.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Are Pivot Tables being used?

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    I have heard of something similare once before ..

    the cause was due to exceeeding a max allowable number of cell formats.

    At that time the user was in the habit of formatting entire rows/columns instead of applyging styles to selected cells. And it was a large and old s/sheet that had aquired a life of its own.
    The solution was to remove many of the formats that were utilised once.
    The exact number of unique formats as documented by M/S was somewhat inexact / hazy but was in the region of 4000 +- the number of holes in your belt.

    A solution at the time was a service in UK that ran a utility over the beast ... just supply a credit card #.

    I have also found this http://www.total.rowing.org.uk/quarrell/QAid/
    I can not verify/validate the claims but you never know this might get you out of a hole.
    Good luck

    Geof

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    HI RG....the workbook in questions has 9 sheets in it...when I removed 3 sheets, they totalled 15MB...and the remaining 6 = 2MB...

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi TFSPRY.....no pivot tables at all....

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Maybe too many styles? This free add-in (Formats & Styles) can list or remove them...
    http://excelusergroup.org/media/p/4861.aspx
    (about 1100 downloads)
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

  8. The Following User Says Thank You to Jim Cone For This Useful Post:

    RetiredGeek (2011-04-12)

  9. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you to anyone who replied...I have concluded that it is a problem caused by a lot of conditional formatting etc, as well as varied font sizes etc and so I will break the workbook into 3 separate workbooks and go at it from there...thanks again for the suggestions and the useful downloads (Jim Cone) and websites (Geoff Richardson, Retired Geek)........

  10. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Damerham, Hants, UK
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I had the same problem, it was solved by using this file from the Microsoft site: XSFormatCleaner.xla

Posting Permissions

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