Excel unable to save file; huge file size

Topic

New Reply

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

Reply | Quote

Replies

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.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Are Pivot Tables being used?

Reply | Quote

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

Reply | Quote

Hi TFSPRY…..no pivot tables at all….

Reply | Quote

HI RG….the workbook in questions has 9 sheets in it…when I removed 3 sheets, they totalled 15MB…and the remaining 6 = 2MB…

Reply | Quote

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)

Reply | Quote

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)……..

Reply | Quote

I had the same problem, it was solved by using this file from the Microsoft site: XSFormatCleaner.xla

Reply | Quote