Workbook not Saving

by Allen Wyatt
(last updated February 25, 2017)

3

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Counting Words

Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you can ...

Discover More

Counting with Subtotals

There are a variety of ways you can count information in different groupings. One convenient way is to use the subtotaling ...

Discover More

Entering a "Slashed Zero" in Your Document

Need to add the occasional zero with a slash through it? There are a couple of ways you can accomplish this task.

Discover More

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!

More ExcelTips (ribbon)

Duplicate Workbooks Opening

If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the number ...

Discover More

Saving All Open Workbooks

Wouldn't it be nice to have a single command that would save each of you open workbooks, all at once? It's easy to do with ...

Discover More

Forcing a Workbook to Close after Inactivity

Tired of your workbooks being left open on the screen where they can be seen by anyone passing by? Here's a way to have Excel ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

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


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.