Shared Excel file growing too large.

**friecr** · 05-18-2012, 09:04 PM

Hi all,

I have been researching this issue for a couple of weeks and can't quite find a solution that works for me. I have a four sheet Excel 2003 (.xls) document that needs to be shared among about 10 computers over the network. There are no Macros or programming anywhere in the files, they are extremely straight-forward. There is some conditional formatting and date formulas and such but nothing major.
The 10 computers are all running different versions of Excel (2003/2007). I initially combined four seperate files into one workbook using copying/pasting. After about a week though, the file was up to 700Mb and useless. I rebuilt everything from scratch and the file was somethink like 100Kb, again a week later it is back up to 700Mb and useless.
Like I said I have looked into some solutions, I have made sure to Edit-Clear-All of the cells that are blank in case there was something in there. No change. I have kicked out all of the users, and made sure that there were no "ghost" users in the document, no change. Even when I unshare the document now it still stays huge. I dont know what to do anymore.
Another thing, we have another file that was created some years ago by someone who doesnt work here anymore and I heard that they had the same problem but got theirs working eventually. It is also a 2003 xls document that is being used with 2007 computers, but it doesnt have the same issues as this new file I created. So, I dont think that the different versions are the issue. I looked through all of the setting on the file that is working, and mine appear to all be the same.

Craig

**ben_hensel** · 05-18-2012, 09:16 PM

How much data are we talking about here? Tens of cells? Tens of thousands?

All with conditional formatting?

**vlady** · 05-18-2012, 09:46 PM

Hello welcome to the forum..

1. use the "Don't keep change history" option in Excel
2. Make sure it is open in "Compatibility Mode" xl2007
3.check any images, logo, picture even small they can be large embedded images.
4. saving xls to xl2007 will increase the size 5 times. you can try to copy the contents to another workbook and this will be saved as higher version.
5. delet unused rows and column before exit.
try it first in a dummy file.....

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Dim objSh As Object
For Each objSh In ThisWorkbook.Sheets
objSh.Rows("35:65535").Delete objSh.Columns("P:IV").Delete 
Next 
ThisWorkbook.Save 
Set objSh = Nothing
 End sub

6. xlsx are zip files so when we save it to xls(2003) it will include all the xml metadata(formatting etc.) so if the file is in xlsx remove all formatting first before saving to lower version (compatibility)
7.very basic formatting in Office 2007 should be used.

**friecr** · 05-21-2012, 07:01 PM

Thank you both for your inputs. I will try to get back to this sometime this week, they kind of put this on the backburner for now, and it takes a while to notice if the file changes size. The growth is gradual/exponential so I'll let you know when I know something.

Also, the conditional formatting is about 20 cells on all 4 worksheets (80 cells) that just checks a date function and changes text colors, so not a lot. The entire file is only about 40 rows by 7 columns on each worksheet. It really isnt hardly any data at all, thats why it is so weird that it is doing this.