Excel File Size question

[schielrn]

Ok let me explain the testing I have done and then I will ask the question.

It was just simple testing of putting the number "1" in cells without the quotes and this was my results.

A brand new workbook (3 Sheets) for me with nothing in it was 12kb (which I couldn't get back to by deleting everything out of the workbook).

File sizes as follows:

One cell:
Put a 1 in A1 = 14kb
Put a 1 in IV1 = 14kb
Put a 1 in A65536 = 36kb
Put a 1 in IV65536 = 36kb

256 cells:
Put a 1 in 1:1 = 14kb
Put a 1 in A1:A256 = 21kb
Put a 1 in A1:H32 = 15kb
Put a 1 in A1:D64 = 16kb
Put a 1 in A1:B128 = 18kb
Put a 1 in A1:IU1 and IV65536 = 38kb
If I put a 1 in 256 random cells = 49kb (This isn't really a good test for anything, except I noticed when I deleted all VBA code and all 256 random numbers my file size will not go below 16kb)

Now based on what I have found here is that file size is more driven by rows than columns?

Does anyone know or have a good article that explains Excel file size?

The reason I ask is because I recently took over a programmer's workbook and had noticed that he stored data horizontally rather than vertically for an analysis sheet we use. That had me thinking why and now I may see why he chose to do that with his code.

I would appreciate any insight into this subject matter.

Thanks,

Rob

 

[RoryA]

It used to be true that more rows and fewer columns was better; now apparently the reverse is true (see the calculation bible) I don't think there's a lot in it frankly and for tabular data, I usually find it clearer if it's laid out the same way a database is presented - fields across the top and 1 record per row. If you want to use the List/Table features or database functions, you have to do it that way!

 

[Macropod]

Hi schielrn,

Excel's file size is goverened more by the complexity of the formulae and the range formatted than by the data location. The reason the size grows more by going to the last row than the lasr column is that the last row is #65536 but the last column is only #256 (both pre-2007).

Cheers