Very Large Spreadsheet Gets Corrupted in Various Different Manners

  • First off, I want to thank everyone who makes this the best Excel help forum on the web. I have learned so much from this forum, and recommend it to everyone interested in Excel.

    I have several large spreadsheets (900MB in XLSM format, 750MB XLSB) that frequently get corrupted. Sometimes they crash while I'm in them, sometimes they just won't open after supposedly saving/closing just fine, and sometimes they crash while saving/closing. Yes, I know, I am a fool and I'm not utilizing Excel correctly. I need to clean up my spreadsheets, or use different software... etc. But what if I'm not a fool, my spreadsheets are extremely efficient, and I am utilizing Excel correctly?

    My Machines

    • Intel Core i7-6950X with 128GB of Registered ECC RAM, 1TB Samsung 960 Pro SSD, Windows 7 Pro 64 bit with Microsoft Office 2010 Pro 64 bit
    • I have never been close to running out of RAM, the max I've seen utilized on the Task Manager is 35%.
    • I have two of these exact machines, which might be important. One is in New York and the other is in California. The problems started around the time I started using this dual PC setup.
    • I use LogMeIn to open these large spreadsheets from both locations, with Google Drive or OneDrive to sync them. I though Google Drive was corrupting them, so I switched to OneDrive.
    • I try to set the default printer to Adobe PDF on both PCs, since I know differing/missing default printers can cause errors. I believe it did hurt me for a while before I realized it.
    • Non-Microsoft Excel add-ins (potentially the problem); Bloomberg Excel Tools, SNL Office Excel, Name Manager Utility, Stock Market Functions Add-in. However, I have tried disabling all add-ins (even COM add-ins) and it doesn't seem to help. Plus they always end up magically re-enabling themselves... Lastly, I need these add-ins.

    My Spreadsheets

    • Not that it matters but if you're inclined to help I'll satisfy your curiosity: They are algorithms to pick stocks and backtest the hypothetical trades, I work on Wall Street.
    • There are no external links, no visible or hidden named ranges with errors, no ActiveX, no custom formulas, no data connections, and no blank cells taking up space (i.e., the ctrl + end trick).
    • There are no wasted formulas and as few array formulas as possible (sometimes they are required)
    • In case it matters, there were data connections (ODBC, MySQL 5.3), but I thought they were the problem and recently replaced them with hard-coded tables (they are simply ranges, not formatted Excel "tables") pulled in via VBA
    • There are a few userforms, and <10 VBA modules with <100 lines of code each. All modules are very small in size (few KBs at most)
    • Each spreadsheet only has 15-20 sheets, nothing crazy
    • One sheet in each model is huge (60,000 columns by 2,000 rows). Not a single error or wasted cell though.
    • I run manual calculations, but it only takes ~15 seconds to calculate the whole thing

    The Errors / Corruption Issues

    • All over the map...
    • To be safe, I always open a copy of the file NOT being synced by OneDrive (i.e., a copy on my Desktop and not in the OneDrive folder).
    • Today I got the "Module not Found" error (when I press OK Excel crashes) upon opening an XLSM file that I successfully saved 5 minutes ago. The VBA project has 6 references, and they're all legit and available. The only one I added manually was ActiveX Data Object 6.1 which I need to upload to MySQL. I tried removing a few of them and it didn't fix the problem. So I revert back to the good Excel file from last Friday, but anytime I update and save gets corrupted again.
    • In the past the spreadsheets just wouldn't open, and the appcrash was due to EXCEL.EXE or VBE7.something but Googling the errors never led anywhere. Perhaps we could explore this more?

    I have rebooted my PC, repaired MS Office, and installed all Windows and Office Updates.

    So right now, I have a perfectly good spreadsheet that opens and runs macros with no problem. If I save it right now it will corrupt itself, it happened 4x today.

    I will pay someone to help me figure this out. Any software or consultant suggestions?

  • I'm a little bit late to the party I guess but I suffer from the exact same issues to this day. Just curious if you ever found any solutions?

  • I did find a solution. Take the following steps;

    1. Before you save the file, open the Visual Basic editor and on the top menu click Debug -> Compile VBA Project

    2. Save-As when you save the file (new file name so it’s a fresh file)

    3. Close Excel completely

    4. Open Excel in safe mode by holding the Control key when you open Excel

    5. Since that opens a blank workbook, in safe mode Excel go to File -> Open and open your new spreadsheet you just saved

    6. Open VB editor and click Debug -> Compile again. Maximize the VB window if its not already. Then close it.

    7. Save-As to another new file name.

    8. Close safe mode excel and open the file from #7 above normally...hold your breath

    This works 90% of the time on my 300MB to 1.8GB Excel files (xlsm and xlsb).


  • Hi Jason - In the nicest possible way, that doesnt sound like a solution :) More like a hacky workaround, but thanks for sharing.

    In situations like this, where I suspect the excel workbook is corrupted and crashing regularly, I rebuild the whole thing. Even in complex applications. It only takes an hour or two.
    1. Export forms, classes, vba etc from within the editor
    2. Copy the data Only into new worksheets in another workbook. When I say data only, I dont mean copy the worksheet, I mean select only the data and copy that.
    3. Start rebuilding.

    It feels daunting at the start - but the thought of doing is often the worst part.

    By the way, have you used a process of elimination to find out why the workbooks are so big?

    Also, have considered putting your data in MySQL, or Access? Again, seems daunting, but if your tables are well structured, it shouldnt take that long.

    Finally.... Syncing a 1 GB file to Onedrive sends shivers up my spine. Worst software product ever. It may not be your problem, but Onedrive is fraught with problems.


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Thanks Ger. Yes, I have rebuilt hundreds of workbooks from scratch exactly as you've described. In this situation it's ~8 hours, because the named ranges and time it takes to rebuild the calc trees. However, it 100% doesn't fix the problem, the files keep getting corrupted upon saving (they never crash while I'm actually using them). I'm still willing to pay someone thousands of dollars to find the real problem.

    I have dozens of very large MySQL databases on AWS - I'm not using spreadsheets to warehouse data (and Ctrl + End takes me to the end of my formulas). My spreadsheets are as efficient as can be while still performing their function (financial analysis & backtesting).

    Lastly OneDrive has been pretty solid - syncing ~600GBs across 4 PCs throughout the country. It has been much better than Google Drive for me.

  • Cool. Sounds like you know what youre doing and you may just have hit the natural limit of Excel. I think the key thing is that - as you said - it corrupts while saving. Excel just "doesnt do" large files.

    I have personally noticed that when saving moderately sized files (say 10MBs) Excel can crash if there a lot of formula calculations going on, especially array formulas, on not many rows either. So maybe considering turning off calculations or preventing calculations while saving.…te-workbook-before-saving

    So what I'm saying is, you could pay someone a million dollars and the result will be no different. Fixing that would require a fix from MS to fix the Excel application itself.

    I'm genuinely struggling though to understand how a well formed and pruned Excel file could end up being so large. The solution on your side of the fence is to make your workbook as small as possible (MB's) with as few calculations as possible - and you believe you have done this. The solution on the Excel side is for MS to fix the less than stable saving of large excel files.

    I guess you've read about Excel's file size limitations... (from MS themselves). Seems like you are skirting very close to these numbers...…e8-45a8-9be2-b58778fd68ef


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!