Problem with saving a workbook as a binary file (.xlsx -->>> .xlsb)

[sanip]

Hi everybody,

I have a critical issue and need your help!

I am working with an excel file (.xlsx) which is relatively big in size - 40Mb (lots of sheets, formulas, etc.). When I saved the workbook as a binary file to reduce the size, excel saved it, but instead of reducing the size, it doubled it.

I tried it a few times and always the same result, excel saves the workbook as a binary book, but it has 80 Mb instead of reducing the original size. I am doing this process often with different books, sometimes it works, sometimes like now it significantly increases in size.

Where can be the problem???

Thank you in advance for your help,
Peter

 

[RudiS]

You might try resetting the last used cell on each worksheet.

Microsoft Excel saves only the part of each worksheet that is in use, meaning the section that contains data or formatting. Sometimes the last cell of a worksheet may be beyond the range of your actual used data and this issue may cause you to have a larger file size than necessary. Clearing the excess rows and columns to reset the last cell can help to compact down the workbook size or bloat.

Note You can locate the last cell of the active worksheet by pressing CTRL+END.


Method to delete excess rows and columns
To reset the last cell by deleting excess rows and columns, follow these steps:
1. Select all columns to the right of the last column that contains data by selecting the appropriate column headings. (Use CTRL+SHIFT+Right Arrow)
2. Right click on a selected column heading, and choose Delete (to delete all empty columns)
3. Repeat steps 1 and 2 for the rows that are under the last row that contains data.
4. Save the file.


To test if the last cell is reset, press CTRL+END again. It should now go to the last cell that has actual content in it.
Note You must do this procedure for every sheet in the workbook. (Or have a macro automate the process if there are many sheets)

 

[sanip]

Hi Rudi,

thank's for your post, makes sense. I will try it with a macro and let you know if it worked.

Regards
Peter

 

[sanip]

Unfortunately it did not work there has to be something else...

 

[RudiS]

I have in the past also encountered this extreme bloated issues when there is hidden (invisible) objects, like text boxes or shapes on the sheet(s). These object might be the remains of comments, illustrations or instructions that have remained on the sheet over time. I had a file in a business environment that was up to 60MB and kept corrupting on save. We discovered that it has 100's of hidden (see through) text boxes that were stacked around the sheet. Deleting them brought the file down to about 10MB, and it saved fine after that too.

Try this to see if it resolves... (see image here)

1. On the Home ribbon, go to Find/Select
2. Choose Select Objects
3. While in this mode, press CTRL+A (to select all floating objects on the sheet)
4. Press Delete (ensure you don't delete charts or objects you intend to keep)

Do this for each sheet, then resave the file.

Hopefully this might be the cause.

 

[sanip]

This was a very good idea as well, thank you for it. The file has reduced from its size about 1 Mb, but the problem with the binary file is still the same.

I did some testing and I think I know what could be the problem:
In my workbook I have lots of "DBRW" functions - I am using them to get the figures from a different system (TM1 - program like SAP-BW), and I think the binary file can not handle this kind of formula.

Could this be the problem?

 

[RudiS]

I wouldn't know? I have no experience with that formula.

Can you save the file as a *.xls file (the old format)? The old *.xls is also a binary format, so if it can save as XLS, then I doubt the issue is binary related. Obviously this will NOT reduce the file size as the old XLS format is not as compressed as the newer XLSB format. It's just a test to see if the formulas you refer to might be the issue?