I have once again run into a major probem in Excel 2010 related to doing major surgery on a large file and ending up with it being unwilling to Save and requiring me to perform some serious “sleight-of-hand” to save my efforts into a new File that WOULD save successfully. I am curious if anyone else has had the same problem (??).
Scenario:
1. BIG original spreadsheet from XL2003 (probably started in XL 5 time-frame – seriously).
2. Lotsa Worksheets
3. Has lotsa Conditional formats that I mostly had to remove to save the file earlier in the process.
4. Uses Many Tables and Structured References in a big (and very cool) way. Lotsa tables linking back and forth to each other (also in cool ways). This is all relatively new to the file and may or may not be releated to the problem.
5. I deleted several large and no longer necessary Worksheets from the file and deleted all the REF’ed out refernces that resulted.
6. The file gave me an error to the effect that it was corrupted and would need to be repaired. Woud I like Excel to repair it? Then after attempting to repair it Excek said “Document Not Saved” (Tough s**t in footnotes).
To save the work I:
1. Created a new file
2. Copied each functional remaining worksheet over the the new file one at a a time.
3. Went through the new file and removed all Linked references.
Then saved it as an XLSX file and THEN opened it again, copied the code over, compiled the code, and saved it as an XLSM file.
Now the file works fine and saves fine too.
This is not the first 2010 file I’ve had this kind of problem with and it seems to relate to adding 2010 capabilities to file that started life in the pre-2007 era.
This is not a big downer on the new capabilities of Excel, just a warning to the wise that they should be ready for this type of thing in their conversions to come and they should work on their skill in this technique. They should also save a lot and use incrementally numbered files as much as practical so they can step backwards if necessary.
Hope all had a good Holiday Season and hope WE all have an “Excel”- lent New year 🙂 (sorry about that one).
Dick
Dick Moffat's Spreadsheet and BI Blog 
Dick
I have seen exactly that prob in 2007 with chart series that refer to a deleted range. Best fix so far is to loop all the charts and delete any with bad refs.
Happy New Year Simon ….
Yes that would do the trick. And in charts that would be kinda subtle to the average user – yes?
Not good.
Dick
I’ve had similar experiences with Excel XP. Couldn’t save, copy’n’patse, nada. The file was a monster with just over 500,000 formulae, many referencing externla libraries.
Part of the problem is building Frankenstein spreadsheets which should really belong in a different environment.
A million rows in Excel is just asking for trouble. I already heard users discussing how they can use Excel as a database – look at all those rows!
XL2010 is going through pre-roll-out testing where I work, so I’ve finally started working with it. Far & away the most annoying thing I’ve found so far is that it’s way to easy to crash it without even an OS warning that it has crashed while playing with UI customization.
As for monster files, data or formulas or nonformula-derived content? I’ve caused the recalc engine to go tilt in the past by using too many lookup calls, so the Calculate indicator is always there. Never had Excel unable to save files. But I haven’t been using XL2010 long enough yet.
One thing I do like: large XLS files I’ve checked average 50% size reduction as XLSM and 75% reduction as XLSB. Guess I should say provided Excel will save them.