Excel file optimization

[Phill]

Hi (again),


I was wondering if there is a way to optimize an Excel file so that it becomes smaller in size.


I have created a logging sheet which includes a lot of repeating formulas and conditional formatting which is as template nearly 10Mb in size.


With the things I learn here at Chandoo's I'm trying to optimize this but the size still remains a problem (always have to compress it before mailing it).


The template is created in 2003 but when opened and saved in 2007 it still remains that large, so no improvement there ....


I found several (payable) tools which promise optimization but since I don't have any experience with that kind of tools and I don't have a view on how they do what they do, I don't want to try these.


If anyone has any suggestions on this it would be great help.


Thanks in advance !

 

[dan_l]

Ehhhh....


What's the source of the data? How many fields/how many rows?


Generally:

-If some of the formulas aren't 'live', copy>paste special> values to turn them into fixed data

-Make sure all pivot tables are pointed at the same cache

 

[Luke M]

As dan alluded to, there's a lot of factors that can cause file bloat.

Do you have multiple formats? XL has to remember each "different" type of format you're using

Can the formulas themselves be optimized? (E.g, using 6 nested IF functions instead of 1 LOOPUP function)

 

[Phill]

Thanks already for your feedback.


The file is a so called RAID log (which stands for (R(isks), A(ctions), I(ssues), D(ecisions), etc), this workbook exists of multiple sheets with the same structure (comparable with Chandoo's Issue Tracker).


Per sheet there are columns with validation rules, conditional formatting and some IF statements. Nothing special but I think the biggest cause of the size is that I already prepared 100+ rows per sheet to make sure that enough entries are available.


I'm thinking of redoing the workbook and add records via VBA when needed but I don't have enough experience yet to create that kind of VBA instructions.


So at this time, I stay with this preformatted workbook.


As example I exported one of the sheets and posted it on http://www.speedyshare.com/files/27279021/Phill_ExampleLogFile_Chandoo.xls


If somebody would have some time to have look at it and post some recommendations that would be much appreciated !

The complete RAID log exists of six sheets, all with the same structure but with a different subject.


Thanks in advance guys !

 

[dan_l]

Nothing about this screams spreadsheet hell. I added some records just with crap data and the file maintains a <1 meg size. I even tried applying some filtering and the results were the same.


Is that description field being used for a ton of text?

 

[Phill]

Thanks for reviewing Dan.


The Description field is only used for a brief description of the issue, it is however possible that in the comments/resolution field a lot of text will entered. This is however far from always the case.


Like said, the workbook exists of six similar sheets and is as template already 9Mb in size :-/

 

[dan_l]

Why don't you copy/paste special> values with the records and paste them into another spreadsheet with no formats or anything. Save the spreadsheet. Let's see how big just the data is.

 

[Phill]

Dan, I couldn't do that because it's an empty template, but ....


Your idea got me thinking on how to find the cause of the size explosion.

As I did with the example I copied the sheets one-by-one to a new workbook and saved them separately to get a view on the size per sheet. This learned me that four of the sheets were much larger then the others.


I verified then the formulas, conditional formatting, etc per sheet but couldn't find anything what could explain the larger sizes. I saved the sheets again but whit no change in size.


I then cleaned up all columns and rows outside the report range, saved them again and woohoo "Lo and behold" ... the file was ten times smaller !

Before I repeated this for the other sheets I tried to find out what in these columns, rows caused this, but I could not find anything. So I cleaned up the other sheets and got the same results.


I repeated this on all sheets in my RAID template and now it is only 300k instead of 9Mb ! Talking about gaining diskspace !


Thank you Dan and Luke for your support, it got me solving my issue !