How to reduce file size?

[SacreBleu]

In a previous thread, I was asking how to reduce the file size of my "bloated" Excel workbooks. One suggestion was to restore as many cells as possible to default format settings, i.e., no fill colors or anything other than default.
Another suggestion was to use the ASAP Utilities add-in to bring the "last cell" to the actual last cell used in the worksheets.
While I was doing this worksheet by worksheet, the file size did reduce somewhat, but then it started to increase again.
This is a 8.40 MB workbook and it needs slimming down. Is there anything I need to know?

 

[jmw]

I risk losing all by turning off autosave.
Autosave keeps editing within the document and tends to bloat the files significantly. I found that exporting finished individual sheets to a new file still took all this editing history with it. Once this is out of the undo command capability it ought to be purged but so far as I can tell, isn't.

I can't do more than that since I have Windows 98SE and not XP but XP users can get a download from somewhere (Microsoft Office I think, and certainly some other sources of Excel plug ins) that will strip out extra/superfluous content.

I was looking for one of the source sites for this but can't now find it in my bookmarks which means another google so I can't aadd anything else at the moment.

JMW

http://www.ViscoAnalyser.com

 

[SacreBleu]

jmw,
I am using Win98SE also. Actually, my Excel is the Office 97 version. It runs same on Win2000 and on WinXP, 2 other computers I use. I don't use AutoSave anymore. I save the workbook regularly, every 20 minutes aprox, by manually hitting the "save as" button and saving as a different file size.

 

[SacreBleu]

postscript-
I meant file name, not file size.

 

[francesca]

I've never used an Excel file that was that big and wasn't being used as a database.

For instance, things like pivot tables take up an ungodly amount of space, whereas MS Access just stores the data required to generate the cross-tab query which is exactly the same thing.

Don't use Excel when Access is the correct tool.

The only other large Excel files I've ever used (other than effective databases and/or files with excessive pivot tables) were the gas pricing models I worked on for a utility company. Their pricing model was several tabs, with a great deal of data, and chances are, a properly structured database running queries and implementing field calculations in the same way that Excel runs cell formulae would've been more appropriate.

Of course, if an 8Mb Excel file is restricting your computer resources, maybe you just need to spend $350 on a new Dell, but don't forget to beef up the RAM and get the extended warranty.

 

[SacreBleu]

francesca,
It is not being used as a database. It has many lookup tables, but they are relativeley small. Some tables have static values, and others have formula-generated values. It is just that the workbook has a lot of formulas in a lot of cells. In a separate thread, I was discussing the effect of the computer on speed. I since learned that it is mainly CPU speed which helps. (Most modern PC's have ample RAM, unlike those of a few years ago). Even my Athlon 1.6+ (Really about 1.3 GHz), with 128MB RAM, running Win98SE at 94% of resources, is almost as fast as the Intel 2.8 Mhz machine with 512MB RAM running WinXP. I learned that not much RAM is actually used in running Excel, unlees the copy/paste of the entire workbook was done, which was extremely slow.
I thing I just learned is to copy all sheets into a new workbook. That helps a moderate amount.

 

[d23]

SacreBleu

As was already stated I too have found that formatting seems to be one of the bigger problems. If you do things like hide rows or columns, add pictures etc. it makes a dramatic addition to the file size.

Another thing is saving a file in an older version than you have on your machine. It should be saved as a Microsoft Workbook. If you saved it as say a Microsoft Workbook Ver 4 so other users can access your file it will add to the file size.

There are a couple more possibilities for reducing file size. The most practical is to split your file into several smaller files that are linked together via macro or with VB. Doing this will limit the parent file size to a manageable amount. If you are uncomfortable with the code just do a Tools Macro Record and make the macros for the links.

There is the possibility of specialized software. I have never used it, but software like TurboExcel is available. TurboExcel claims to convert your EXCEL file to a C+ program. Attached is the link if you’re interested.

http://www.savvysoft.com/prturboexcel.htm

Good Luck
D23

 

[HgTX]

I don't suppose there's an Excel equivalent to the Word trick of saving the document as RTF and back again?

Hg

 

[SacreBleu]

I checked the Excel help web sites listed in the other recent thread, but I think I really need to bite the bullet and clean up the workbook. It has been "added-on" for many years, and the lookup tables can reduced by more efficient combinations of formulas. Thanks for the suggestions.

 

[IFRs]

If this is a shared workbook, limit the number of changes it keeps.

 

[FrancisL]

Are there any pictures in any of the sheets, or reports?
I have seen company logos in Word files that bloated them by several MB by the way.

 

[SacreBleu]

FrancisL,
No pictures. Just some very minimal drawing objects (the MS Excel drawing features). I already am deleting some of those.
I am overhauling the whole mess in my "spare time". I am really seeing ways to streamline the one big worksheet that contains most of the data and formulas.