Written by Allen Wyatt (last updated November 7, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016
Tim has an Excel workbook that he's used for years. The file is approximately 18 MB, containing 22 worksheets. The largest worksheet extends to cell HB2000. Recently when he tries to save the file, he gets a "Document Not Saved" error. He can open and save the document in Libre Office with no problem. He wonders what could be causing the problem and how he can fix it.
There could be any number of reasons why this problem is happening. Most of them have to do with being a workbook that is experiencing some sort of corruption issue internally. This is more common in workbooks that have been used for quite a while and that are large and complex—all of which seem to be the case with Tim's workbook.
There are some things to try. First, try using Save As. Open the workbook and immediately press F12 to display the Save As dialog box. Make sure you give the workbook a new name, which can be as simple as appending a number at the end of the name. (For instance, save the workbook as MyWorkbook2 or MyWorkbook3.) Saving the workbook in this way causes Excel to refresh many of the internal pointers that it wouldn't otherwise bother with.
If that doesn't work, save the workbook as an XLSB file. Make sure you use the Save As Type drop-down list to select Excel Binary Workbook (*.xlsb) as the format. XLSB files are smaller than regular XLSX or XLSM files, which mean that they will load and save faster.
You may also want to look at somehow simplifying your workbook. For instance, get rid of PivotTables you no longer need or unused columns in PivotTables you do use. (PivotTables are notorious for using a ton of Excel resources.) You should also minimize the use of charts, complex formulas (or huge numbers of formulas), and excessive cell formats or styles. You might also consider breaking your workbook up into multiple workbooks.
For some more ideas of how to troubleshoot errors when saving workbooks, see this Knowledge Base article:
https://support.microsoft.com/en-us/help/271513
If you are still using Vista or Windows 7, the problem could also be related to a rather esoteric bug in how the operating system stores information in what is called a "client side cache." You can clear this cache—which may fix the problem with not being able to save the workbook—by following the steps outlined in this Knowledge Base article:
https://support.microsoft.com/en-us/help/942974
If you continue to have problems with the workbook, then the only option is to reconstruct it. That means you start with a new workbook and recreate all the formulas and data in the new workbook. You cannot copy of worksheets from the old workbook to the new one, as that may copy the problems as well. This can be a painful process, but it may be necessary if the old workbook is truly corrupted.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1644) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you create a workbook that is read-only, users can still make changes to the workbook. When they exit, they are ...
Discover MoreAll good things must come to an end at some point. When you are done sharing your workbook with others, this is how you ...
Discover MoreWorkbooks created in very old versions of Excel can, at times, have issues when opened in later versions of the program. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-01-22 10:01:38
Mike Hodkinson
The very first thing I would check in each sheet would be where the last activated cells is by using Ctrl + END.
I most cases of very large files I have found this to be many rows and columns away from where the actual last cell is containing text / data.
Why does this happen? Because users format whole columns and rows and do not realise the potential impact this will have.
On one occasion a file of 5mg was reduced to less than 500kb by resolving this.
How do you resolve? you need to "Clear All" all rows below the last data cell row to row 1048576 and then Delete these same rows and then save the file and re open. Check the file size and it might well be a lot smaller.
Other issues
Seen a massive number of added styles (over 700+) in one file - had to been a macro to remove them as all of them apart from the default ones were unrequired duplications.
The biggest contributor to massive file size is the number of columns / rows of complex large formulas eg. nested =IF, =VLOOKUP. If you want to again significantly reduce the number of these use VBA to create these formulas and then copy paste special value them on a refresh button.
Hope you find these comments of interest and assistance.
Mike H
MS Office IT Consultant Trainer
2017-02-27 13:57:32
Greg
Hi Tim,
Just noticed your question about the error message.
Simply put, this is just an example of error trapping in the code. The error is coming up from the Windows OS to the app and then the app is finding that it doesn't have a good answer, but does have a generic answer so it provides you one.
I have seen, and I kid you not, and error code which is defined as "an otherwise undefined error" - and this is as a system admin .. sort of a 'yaeh, well, something happened ... and it wasn't good ... have a nice day" kind of error
In the defense of the coders, there are so many ways for things to go wrong that it is very hard to find them all in testing and then code for those - at least it was kind enough to let you know that the save function failed and not close out your spreadsheet and lose data.
Given the beasts that Windows and Office are alone, when combined... my hat is off to the dev teams that everything works as well as it actually does. But the army of users will always find the things which they did not (I was also in QA role for a time... you never find all the bugs)
Good luck,
2017-02-27 13:47:08
Greg
Hi,
We had a similar problem with a spreadsheet which had been slowly evolving over several years and even 1 migration of MS Office and thus Excel.
The symptom was that it would save, no errors given, but the format, and in some cases cell content would not be the same when reopened. Another tell was that on the Home ribbon, if you expanded out the styles section, the usual default Styles 'Normal', "bad', 'good', 'neutral', 'calculation', 'check cell' or if you have the other more common header, body, etc... these all changes to say "40%" and were all filled in bright florescent green.
After much digging, I found a MS KB ( sorry, I've lost the number) which pointed me out to a 3rd party free ware tool.
https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/
This gentleman - Serge - wrote a very simple, very fast little tool that uses some MS code available to the Open XML Format API and does some clean up very fast.
This not only fixed the loss of format and loss/corruption of data, it also made the file size smaller and the sheet opens much faster etc now.
What I have found is that whenever a copy a sheet from another workbook into this master, that hastens the point that funny things start to happen. We do this often as the workbook is a project plan that has a lot of updating required and a short period to do it in, so people work on individual sheets and then we roll them all back up into a master at regular intervals.
Anyway, I use the DOT Net 4.0 version of the tool and have been doing so for about 2 years and have seen no negatives.
This is quick and dirty and may fix your problem.
Good luck,
2017-02-25 15:51:19
Tim Kendal
Thanks to all for the prompt & comprehensive replies to my problem - much appreciated!
I'll look into all suggestions, including the Microsoft articles, and also possibly dividing up the workbook. Incidentally there are no pivot tables in it
In the meantime, just 2 comments:
1 Why does Excel have to present such a useless message? Surely it should be clever enough to know what the actual problem is and where it is so I can do something about it After all, it stops you entering a formula which contains a circular reference, so it is quite used to self examination!
2 The workbook loads and saves in Libre Office (dare one mention that here?!) and I can use it there without any apparent problems - though when I save it as an xls file, Excel can't read that - it says it is corrupted!
All I want to do is to get on with the job!
Once again, many thanks to all
Tim Kendal
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments