Bloated Excel 2010 File Size

[HOOS77]

I have multiple Excel files created in Excel 2007 (all cloned from the same master file) which are used by our managers that unexpectedlyl increase in size when saved in Excel 2010 (as .xlsx files in both cases). The amount of data is insignificant and does not vary that much between the various files. Prior to saving in Excel 2010, the files were about 1.1 to 1.3 mb; afterwards, they ballooned to 11 - 12 mb. There are no pivot tables, no macros and no excessive formatting.

I opened the master copy (blank, no data) in Excel 2010 and immediately saved without making any changes and the file increased from 1.11 mb to 12.4 mb !! Files of this size take much longer to open and save which is a major inconvenience to our managers. Interestingly, when I saved as a binary file, the file size dropped to 1.55 mb.

Have searched in vain on Microsoft and other sites for relevant information and found nothing helpful. There has to be a reason for this, but I'm stumped at this point. Any help would be greatly appreciated!

Thanks.

 

[PCRIDE]

I am having these issues repeatedly.
1. Opened the .csv file (NO formatting, no formulas, no objects, no VBA, no MACROS)
2. Saved to local desktop folder 1,545 KB
3. Opened File, Saved as Excel Workbook 1,734 KB (4,946 rows)
a. Column 1 remove all single quotes (replace all)
b. Format column to Custom ‘00000000’
4. Copy and Paste Special - Values from a separate workbook 179,332 rows (existing records no formatting, no formulas, etc.)
5. Save and Close – 35,216 KB

This is why the file size has increased. You are converting from CSV to XLS/XLSX. Remember CSV is text, it doesn't support pivot tables , formulas etc...
Also you formatting cells with '0000000' not sure what that does, but you have added information and for all rows and columns. That could be the issue right there.

Here are some key points to remember. I export data out of multiple systems all the time (SQL, Ticket Systems, Web Systems etc..)

Exporting large amounts of data i.e 70-150 columns x 300K rows comes across in many sizes.

If I export using CSV, the file size is x size, it HAS formatting like fonts, font size, a header etc.. this contributes to file size. Dates are formatted as dates etc..

If I take that same data, copy then paste as text, all formatting is stripped away, dates are now serial numbers etc.. save as CSV the file size is much smaller than the original.

The exporting of data out of various systems; the format is defined by the system, so it would be different depending on your system. Exporting or actually copying out of SQL is probably the most simplifed format you can get without inflating the file size.

As soon as you SAVE AS XLS/XLSX the whole thing changes. File sizes increase, etc...

I deal with 150-200 MB files all day and with 500K rows and 150 columns, this is excels limit. Of course less columns you could get more rows. But trying to pivot this data, excel errors due to hitting the max limit of records that can be pivoted, trying to run formulas to make new fields in the data, excel can take 10-60 seconds to calculate etc...

There is a ton to consider here.