Why is my workbook so large (7.34mb) and is there a way to make it smaller?

[jonvanwyk]

My electronic timesheets are finally complete, and the only complaint I have about them is the file takes about 5-10 seconds to load. I assume this has to do with its size. The 2007 version (the version we will be using) is 7.34mb in size. Can anyone tell me why the file is so large? Is there a simple fix that does not affect my layout that will reduce the size or allow it to load more quickly? Thanks!

Final Template in Excel 2003 (18mb): http://www.jonvanwyk.com/FY2011TemplateExcel2003.xls

Final Template in Excel 2007 (7.34mb): http://www.jonvanwyk.com/FY2011TemplateExcel2007.xlsm

[Richard Buttrey]

Hi,

It would have assisted greatly if you had also supplied the passwords for the sheets and VBA code.

If you save it as a binary file (.xlsb extension) you will cut it down to 4.6 Mb, but no doubt the biggest cause of the problem is that each of your time 26 sheets is showing CS718 as the last active cell when presumably O31 should be the last cell on each sheet. The Two Summaries also appear to be showing a last cell which is many rows and columns removed from where it should be.

You will need to delete all columns P:CS and Rows 32:718 on each sheet and then save the workbook to clear and reset the last cells.

Regards

[Domski]

Another way is to create a new workbook like this:

1) Make a new workbook

2) From your "bloated" workbook, select all the cells in the sheet, Right-Click|Copy

3) In the new workbook, select all the cells and right-click and select Paste Special...|Formats

4) Again, Right-click and select Paste Special...|Paste all except borders

Dom

[leaning]

Richard,

If you use the XL Checker from this post:http://www.excelforum.com/the-water-...lways-use.html, try the ExcelDiet button.

According to the author of that particular code :

You may have a workbook that has recently increased drastically in size. Usually this is caused by Excel thinking that the used range for one or more sheets is much larger than it should be. This code will delete all unused rows and columns and may significantly reduce the size of the Excel file.

. from http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

HTH!

VR/Lost

[jonvanwyk]

The password to access the VBA code and unlock the sheets is "6566". I hope there is a simple way to reduce its size. Once unlocked, you will see why there are many more rows and columns involved than what you are able to see when everything is locked up.

However, even with all of my background forumlas, each worksheet should not go outside of A1 to AN231. THOUGH I did change the background color of cells way past that. Could changing the background color really increase the size of the file that large? I suppose I should go through the template and make the background "automatic" for anything outside of the viewable area?

[royUK]

Try running Rob Bovey's Code Cleaner addin

[jonvanwyk]

@royUK ... I will try to get our system's administrators to install that application on my work PC. If not, I might have to clean the code at home (I give myself permission :-) I understand that cleaning my VBA is a good thing to do anyway, but will it really reduce the size of my file?

Anyway, I attempted to remove the background color on two of the worksheets (that went out hundreds of cells past what I needed it to color). Doing this actually INCREASED the size of my file by 0.1mb. I am at a loss.

[Richard Buttrey]

Hi,

However you do it you need to remove the excess columns and rows so that when you choose End Home the cursor is positioned at the last meaningful cell. If you do this manually, after removing the excess rows/columns you'll need to save the workbook before Excel resets the last cell.

Regards

[jonvanwyk]

I am manually deleting unused rows and columns. The columns are being reduced to exactly where I need them. The rows are being reduced from 718 to 356. I am only using 231, so I am not sure why I cant get them to delete under 356...it just seems to be the magical number. The good news is, for each of the worksheets I am doing this to, it is reducing my file size by 0.2mb. At this rate, my file will go from 7.33mb to around 2mb.

[Richard Buttrey]

OK, Good

Have you tried deleting rows a second time to see if that sets the last cell to row 231?

Don't forget that if you also save it as a .xlsb file it will be even smaller and load/save more quickly.

Rgds

[jonvanwyk]

what is the difference between .xlsm and .xlsb? I would have to imagine you lose something when going to .xlsb, or why would .xlsm even exist? Thank you for the education! :-)

[jonvanwyk]

Okay, I have stripped the file of all unused cells. For my 2007 document, it changed the size of the file from 7.33mb to 3.32mb. While it is nice to take up less space on the shared drive, it seems to open up only slightly faster than it did before. However, the 2003 version opens very fast, and the Binary file opens only slightly slower than the 2003 version. This is strange to me since these to files are larger than the 2007 version; especially the 2003 version which is nearly 4 times the size!

I tried using the CodeCleaner at the recommendation of royUK, however the instructions say it can only be opened from the tools menu of the VBE. I had IT install the file, but it is not to be found in the tools menu of the VBE of my Excel 2007 file.

Below are the unprotected versions of my latest template. I am at a loss as to how to further reduce the size of my 2007 excel file, and especially how to get it to open more quickly. I like the speed of 2003, but I can hardly ask IT to give me 1.3Gb of space on the shared drive. I suppose the binary file is almost the same size as the 2007 file, and opens nearly as fast as the 2003 file. Perhaps that will be a good compromise...hmmmm.

Excel 2003 (14.2mb - Opens in 2.2 Seconds): http://www.jonvanwyk.com/Template2003

Excel 2007 (3.32mb - Opens in 5.1 Seconds): http://www.jonvanwyk.com/Template2007

Excel 2007 Binary (4.01mb - Opens in 2.7 Seconds): http://www.jonvanwyk.com/Template2007xlsb

[Richard Buttrey]

Hi,

No, you don't lose anything. It is to all intents and purposes completely transparent to you apart from the fact that being a binary file it's much more efficient with space.

I understand MS introduced this when they created Excel 2007 with its > 1 million rows. They did this I believe to head off or at least alleviate problems with extremely large workbooks which started to take advantage of 1m rows and >16k columns.

Regards

[jonvanwyk]

@Richard Buttrey

What you are saying makes sense to me, only the binary file is 4.01mb and the xlsm file is 3.32mb. Any idea why this is?

Update: I used the compress function on right clicking the xlsb file and it reduced the size on disk to 3.32mb.