File Size

[SCSA25]

Have a question. Have you ever had an Excel file that is normally small,
say 80KB, but after adding some information, the file suddenly jumps to
2400KB's? I had this happen to me before but can not remember
if there is a fix for it.

 

[NikNik]

If the file is still relatively simple and small in terms of content simply copy the data to a new WB and save again

 

[Iridium]

From Dave Hawley's site

Unusual File Size Increase

Unfortunately it is not too uncommon to have an Excel Workbook increase in file size for no apparent reason. Below are some methods to overcome this problem, that should be performed in the order stated! Please note OzGrid accepts no responsibility for any loss of data. This means save a copy of your Workbook first. After each step Save and check the File size by going to File>Properties/General If it has reduced the file size you shouldn't need to go any further!

Avoid saving as multiple versions. Save as "Microsoft Excel Workbook (*.xls). You can make this the default by going to Tools>Options/Transition and selecting Microsoft Excel Workbook from the Save Excel files as Combobox. This alone can reduce file size by 50%

Do the following to ALL Worksheets. Select any single cell then push F5, click Special and check the "Blanks" option and click Ok. Now go to Edit>Clear>All. Manually go to the last used row in the Worksheet (do not use Edit>Go to-Special "Last Cell"). Select the entire row beneath, then holding down the Ctrl+Shift key push the Down Arrow. This should select all unused rows. Now go to Edit>Clear>All. Do the same for the Columns. Now Save.

Right click on any sheet name tab and select "Select all sheets". Now right click again and select "Move or copy" Check the Create a copy option then choose "(New Book)" and click Ok. Save this new Workbook as any name and check it's file size via File>Properties/General.

If no dramatic difference delete one sheet at a time and Save, each time check it's file size via File>Properties/General and see if there has been a disproportional reduction in file size.

If there has, the Worksheet may have been corrupt, go back the the original Workbook and copy the entire contents of the possibly corrupt Worksheet. Come back to the new Workbook, insert a new Worksheet and paste the data onto it. If this does not help you may need to delete a few rows at the time (Saving each time) until you find the offending row(s).

If you have Modules and/or UserForms in the Workbook, open the VBE (Alt+F11) and right click on each in the "Project Explorer" (Ctrl+R) and Export. Now, in a new Workbook again open the VBE and go to File>Import File.. and import all Modules and UserForms into this Workbook. Use the Move or copy method (as shown in step above) to copy all sheets into this Workbook Save.

HTH

 

[gaynard_nelson]

Have a question. Have you ever had an Excel file that is normally small,
say 80KB, but after adding some information, the file suddenly jumps to
2400KB's? I had this happen to me before but can not remember
if there is a fix for it.

This happens all the time if you formatted entire cells or rows. Try this simple test - Ctrl+Home should take you to A1, Ctrl+End will take you to the lower right hand corner of the active spreadsheet. If your cursor goes beyond what should be active, select the unneeded area and do an Edit, Clear All. save the spreadsheet and check the file size. I have had customer spreadsheets go from 3+ MBs to a coupla of hundred K or less after clearing the unneeded portion of the spreadsheet.