References vs. absolute values - surprising results

J

Joerg

I'm using Excel 2003. Some cells of my data table contain fairly large text.
As the text is identical in all cells of a column I tried to be smart: I
figured that instead of having copies of this text in all these cells I
could reduce file size by keeping this text only in one cell and just
reference this "master cell" in all other cells. I was pretty much surprised
to see that the file size didn't decreased - it doubled.

I then made a test.
I copied a large text into each cell of A1:J10 and saved the workbook as
Abs.xls
I opened a new workbook and copied the text to A1 only. In all other cells
of range A1:J10 I used the formula =$A$1. I saved the workbook as Ref.xls

Resulting filesize of Abs.xls was considerable smaller than that of Ref.xls.
Looking at the binary code of Abs.xls I could see that the text appears only
once. This means that Excel must employ some form of file compression and
intelligent referencing, so that repeated text is stored only once. Much
different in Ref.xls: The text, which I entered only once, appears 100 times
in the binary code, which explains the increased file size.

Conclusion: Repeating absolute values in a workbook over and over again may
not be smart, but can reduce file size.

Cheers,

Joerg Mochikun
 
E

Earl Kiosterud

Joerg,

It seems unlikely that the use of absolute references would affect the file size. It's
possible that the Used Range got reset at various points. Do a search for that and how to
reduce it to reduce file size and see if that might be what happened in your case.
 
D

Debra Dalgleish

Interesting. There's not much difference in file size if the referenced
cell contains a number, but it gets substantially larger if the
referenced cell has a long text string.
 
J

Jerry W. Lewis

I believe that Excel stores the formula result along with the formula--this
allows for no recalculation when opened with the same or earlier version, and
is consistent with Joerg's observation of 100 copies of the text in the
formula version of the workbook.

Numbers have much smaller space requirements than long strings, since a
floating point number requires only 8 bytes regardless of its value while a
string requires as many bytes as characters in the string.

I have no direct experience with Jeorg's claim that string constants can be
repeated without extra storage, nor have I tested whether that phenomenon (if
real) carries over to numeric constants.

Jerry
 
D

Debra Dalgleish

I saved a file with a long text string (13400 char) in cells A1:A100,
and the file size is 29 KB

In another file, the same text string (13400 char) is in cell A1, and
cells A2:A99 contain the formula: =$A$1
Its file size is 1337 KB.

What interests me is why those formulas increase the file size so much.
Doing the same comparison with a number in cell A1 results in files of
16 KB vs 17 KB.
 
J

Joerg

Earl, it may "seem unlikely" to you,but it does (why don't you just repeat
Debra's or my test?). UsedRange may be the usual suspect in cases of bigger
than expected file size, but in this case it's innocence.

Joerg
 
Top