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

Comparing Documents Top and Bottom

Word has a feature that allows you to compare two documents side-by-side. What if you actually want to compare the documents ...

Discover More

Printing Documents without Markup

If you have a document with Track Changes turned on, you can accumulate quite a bit of "markup" in it. Here's how you can ...

Discover More

Best Quality for High Resolution Graphics

You want your documents to look as good as they can. If those documents include graphics, then you also need to make sure ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (RIBBON)

Comparing Workbooks

Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the comparisons, ...

Discover More

Tying Workbooks Together

If you work with multiple workbooks at the same time, you might wonder how to tie them together so they open and close and ...

Discover More

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
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 for this tip:

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. 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 3 - 0?

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.

Links and Sharing