What is the differencdein these two files?

[Lou_Reed]

The answer to this post may solve some other problems that I am having. I have attached two Excel files to this post. They are really the same file, with a few modification


on one or the other that would make them different. T

hey are essentially the same.

However, the size difference of the files is about 10 fold. The size difference is huge!

How can two similar files have such a huge memory difference?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

Formatting is one feature that adds to file size.

[Lou_Reed]

So you say that the larger one's size is the result of formatting? That explains it?

I am not disagreeing with you, I just want you to expand on this that is all.

Respectfully,

Lou Reed

[June7]

I didn't download your files from this thread but tested with an older copy of your workbook. File started at 80kb. I removed all formatting from the worksheet and saved the file. Size is now 50kb.

[CJ_London]

the other thing that can affect file size is cells used (and therefore 'recognised' by Excel and 'in memory'), but now blank to the right and/or below your main data. To establish this, use the end-home keys to get to the bottom right of your worksheet.

in the test file this is FE130, whilst the bottom right of your data range is CN130 - you have around 70 additional columns

in the final file it is XBX149 - you have around 16200 columns additional columns

Deleting these additional columns reduces your final file from 927kb to 92kb. Use the delete columns option on the home ribbon, just highlighting the columns and hitting the delete key will not work.

Don't ask me why you have these additional columns, it is something you have done somewhere along the line. Based on experience, my best guess is the data has been imported from some other system such as SAP or Oracle which can often be inefficient in terms of optimising Excel.

[Lou_Reed]

I appreciate your response very much.

Please explain how you did this. The first sentence threw me.

"To establish this, use the end-home keys to get to the bottom right of your worksheet."

Please explain.

I just thought that it had to be something like this, but did not know how to analyze.

When I opened the files they looked almost identical. So how could they be so different in size?

Did the final file additional cells have data in them? I did not see it if they did.

Also, I know you said not to ask, but how did the final file get so much more data in it? I did not do it on purpose.

Your response is much appreciated.

respectfully,

Lou Reed

[CJ_London]

When I opened the files they looked almost identical. So how could they be so different in size?

has already been explained.

"To establish this, use the end-home keys to get to the bottom right of your worksheet."

Please explain.

it is the normal use of the navigation around excel. Hit the end key then the home key. google 'excel navigation' for more about navigating around excel

Did the final file additional cells have data in them? I did not see it if they did.

Not that I could see - read my post. 'the other thing that can affect file size is cells used (and therefore 'recognised' by Excel and 'in memory'), but now blank to the right and/or below your main data'. Note I said cells used - that does not necessarily mean they had real data in them - see last para below

Also, I know you said not to ask, but how did the final file get so much more data in it? I did not do it on purpose.

so don't ask - I don't know, it's your spreadsheet, you know what you have done in it. I've offered a suggestion based on my experience when other systems create exports for excel

[ArviLaanemets]

Excel also stores the history of whatever the user did with it - hidden from user. So when you add data, and then delete data, format some area and/or remove formats, etc., the file size increases. I'm not sure about newer versions of Excel, but for Excel2000/2010 saving the file with a new name (Save As) removed all history. So you saved a file with new name, after that you did overwrite old file with new one. And as last step, you could delete new file. The file size could shrink up to order.

Update. Sorry, wrong forum!