Any Tricks to decreasing Workbook size (disk space usage)?
 

Wondering if any of you have any suggestions for decreasing the file size for
a workbook. Granted it has a LOT of sheets (aka tabs) (about 210), but not a
lot of info and it grows in 2MB increments it seems - 45 rows to a sheet, 36
columns, a third of which are blank, and a third are numeric entries, the
other third formulas.

It is getting a bit unwielding to e-mail around and I am looking for ideas
on how I can decrease the size of it.

Thanks for any thoughts you may have.
--
Bruce

Bruce,

I did an experiment where I created 6 sheets with a 36x45 block of data + a
header. This came out to 81kb. I then took the same data and pasted it into a
new workbook all on the same sheet and delted sheets 2 and 3. No difference,
still 81kb. I then zipped it up with winzip and got a 95% compression ratio,
i.e., a 5kb file size. If you haven't been zipping, that will be a big help.
It doesn't sound like your indivdual sheets are overly complex, so I'm not
sure that you can reduce much. Hard to say without seeing your formulas and
so forth.

Roy



"Bruce" wrote:

Wondering if any of you have any suggestions for decreasing the file size for
a workbook. Granted it has a LOT of sheets (aka tabs) (about 210), but not a
lot of info and it grows in 2MB increments it seems - 45 rows to a sheet, 36
columns, a third of which are blank, and a third are numeric entries, the
other third formulas.

It is getting a bit unwielding to e-mail around and I am looking for ideas
on how I can decrease the size of it.

Thanks for any thoughts you may have.
--
Bruce

Bruce wrote:
Wondering if any of you have any suggestions for decreasing the file size for
a workbook. Granted it has a LOT of sheets (aka tabs) (about 210), but not a
lot of info and it grows in 2MB increments it seems - 45 rows to a sheet, 36
columns, a third of which are blank, and a third are numeric entries, the
other third formulas.

It is getting a bit unwielding to e-mail around and I am looking for ideas
on how I can decrease the size of it.

Thanks for any thoughts you may have.


Three thoughts:

1) Position the cursor on a page and hit Ctrl-End. If the cursor jumps to the
end of your data more or less you're ok. If however it jumps waaay down below
where your data actually ends, then somehow Excel is storing a bunch of empty
cells for you too. You'll need to delete them from every sheet, close the file
and then reopen it to see the difference. Sometimes when you're doing copies of
things you inadvertantly copy a whole lot of nothing along with it.

2) I have similar files. On mine, the formulas really only get evaluated the
one time and then don't change. Consequently after I've copied the formulas to
a sheet and it has evaluated, I then copy the sheet and paste it back over top
of itself using PasteSpecialValues. This replaces a bunch of long text
formulas with a simple number. It reduces the stored size of my sheets by about
50% that way.

3) Finally, as Roy suggested, you might try Zipping your files to crunch them
down when you mail them. That converts a 16MB file of mine to 5MB.

Good luck...

Bill

If you hit ctrl-end (on each worksheet), do you go way past where you know the
data ends?


If yes...

Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused


And if you have pictures/objects embedded on those worksheets, they could be
causing trouble.

Bruce wrote:

Wondering if any of you have any suggestions for decreasing the file size for
a workbook. Granted it has a LOT of sheets (aka tabs) (about 210), but not a
lot of info and it grows in 2MB increments it seems - 45 rows to a sheet, 36
columns, a third of which are blank, and a third are numeric entries, the
other third formulas.

It is getting a bit unwielding to e-mail around and I am looking for ideas
on how I can decrease the size of it.

Thanks for any thoughts you may have.
--
Bruce

--

Dave Peterson