Excel File Size

[Guest]

This isn't a "prgramming" question, but I don't think there is a NG specific
to my question, and the people on this thread seem to be quite knowledgable
about all things Excel. </suck-up>

I've got two different Excel files. They are very nearly identical. Without
going into detail, they are production tracking forms. They each have four
worksheets with links among the sheets. I have data validation pointing to a
specific range on the "main sheet" that dynamically populates dropdown boxes
on two other sheets. I do have to use volatile functions for this.

Neither form contains macros. To the best of my knowledge, neither of them
EVER contained macros.

The only appreciable difference between the two files is that the "man
sheet" on one of the files contains more columns of data entry. (The bulk the
of sheet is locked and protected; only the places where users can type are
editable. One of the forms has more of these editable areas - which users
just type in a whole number between 1 and 50.)

However, one of the forms in 980K. The other is 2 MB. What's most vexing is
the one that contains "more" stuff in it is the smaller of the two. Does
anyone have any idea how the file could have ballooned like that? Does Excel
offer any sort of "compact and repair" function like Access does. I can tell
you that both files have undergone a lot of radical modifications - but
identical ones. (In fact, I usually make the changes to one, then simply copy
the changed sheet to the other.)

Thanks for your help!

 

[Norman Jones]

Hi MDW,

See Debra Daleish's notes on file size at:

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

 

[Guest]

I don't see anything on that FAQ that deals with file size.

 

[Tom Ogilvy]

For each sheet, Excel stores information on just the smallest rectangular
area that includes all the cells excel considers in use. If Excel thinks
one sheet is using cells A1:Z200 and another is using A1:Z1000, the file
size of the first would be significantly smaller than the file size of the
second, even if, by looking at them they appeared identical (or perhaps the
first had even more cells that actually contained data). What excel thinks
is in use isn't necessarily based on if the cell contains data. If it does
contain data, it is definitely in use, but empty cells can be considered in
use as well for any number of reasons. The cited link tells you how to get
Excel to reevaluate what is the used region (making it smaller will make the
file size smaller).

 

[Guest]

Aha! I see your point. If I'd had more coffee, I probably would have made
that connection myself. Sorry I missed it.

Thanks Tom, and Norman (and Debra).

195K sounds a lot better, don't you think?

 

[Don Guillett]

Read it again. Carefully.

 

[Guest]

I figured it out, thanks to Tom's explanation. In my defense, nothing in that
example mentioned that deleting excess rows would decrease the file size. It
was framed as a way to make sure the scrollbars "work correctly".