Workbook not Saving

by Allen Wyatt
(last updated January 22, 2021)

4

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

Ordering Worksheets Based on a Cell Value

Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...

Discover More

Printing Graphic Thumbnails

If you are doing work with a lot of graphics, it may be helpful to create a summary page that contains thumbnail ...

Discover More

Macro Won't Limit Replacements to a Selection

When you are using Find and Replace under macro control, you can specify what you want it to do when the end of your ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Noting the Workbook Creation Date

You may want to add, to your worksheet, the date on which a particular workbook was created. Excel doesn't provide a way ...

Discover More

Opening a Workbook to a Specific Worksheet

When you open a workbook, Excel displays the worksheet that was visible when the workbook was last saved. You may want, ...

Discover More

Noting When a Workbook was Changed

Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 two less than 8?

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


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.