Shrinking an Excel File

[Guest]

I'm creating an excel template, I find if I alter a whole lot of cells the
file size grows; as you'd expect. If I then delete the rows that contain
these cells the file size doesn't shrink again.

In a template that is going to be used to create a lot of documents the
file size is quite important. We need to store these documents for 3 to 5
years. Is there anyway, maybe some kind of tool, that can minimise an Excel
file size?

- Robert Mulroney

 

[Norman Jones]

Hi Robert,

See Debra Dalgleish's notes on restting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

Yep, ta. That doesn't really answer my question though.

I really need to know that my excel file is as small as it possibly can be.
In Access there is a function "Compact and repair database" that removes a
lot of the blank space in the files left behind by addind and deleting from
the file. Something like that is what I'm looking for.

The strange thing is I can remake the file with exactly the same "looking"
sheets and modules but the files sizes are varstly diffrent. It's almost like
the more you play with a file the bigger is gets.


- Rm

 

[Norman Jones]

Hi Robert,

Yep, ta. That doesn't really answer my question though

You initially said:

I would expect that, in this scenario, properly restting the used range
*would* reduce the size.

In limited testing, my results confirmed this expectation.

Have you tried running Debra's DeleteUnused macro?

 

[Guest]

My appologies I didn't mean that remark to come off as rude. I do
appriociate your response. I did run the macro that you refered me to and it
cut-off about 7k of a 2.4mb file. The strange thing is that I can copy and
past all the data into a new workbook and have it come out at 1.15mb.


- Rm

 

[Norman Jones]

Hi Robert,

As a simple test, I opened a new workbook. I entered a simple formula
(=Row()) into A1 on the first sheet. I then copied the formula down to
A65536 and saves the WB. This produced a file size of 3.4MB.

I then deleted cells A101:A65536 and ran Debra's macro. Saving the revised
file reduced its size to 49k.

Depending on what version of Excel you are running, it may be necessary for
you to save, close and re-open your file to reset the used range / file
size.