Wednesday, 27 June 2012

Why does Excel File size increase and how to reduce it?

When you work on Excel spreadsheet, you must have seen that the size of excel file sometime increased beyond limits. This happens so many times with all of us. I too have faced similar problem. Yesterday morning when I opened my Excel spreadsheet, the file size increased from 4 MB to 40 MB and is taking forever to load. I was wondering, what could be the reason for this? Then I Google my problem and found that I am not the only victim of this problem, there are thousands. I did a complete research on this problem and found that there are so many reasons for increase in file size.

Reasons for File Size Increase
  • Blank characters in empty cells
  • Unwanted formatting in cells
  • If Excel spreadsheet contain only text
  • If Excel closes unexpectedly
  • If Excel sheet have so many links
  • Extra rows and columns in Excel sheet
How to reduce Excel file size?
  1. First and most important, take a backup.
  2. Check formatting on unused cells and delete all unused rows. To delete unused row  perform below steps for each worksheet.
    1. Select any cell and press F5.
    2. Click the Special button and check the Blank radio button, click Ok.
    3. Go to Edit-> Clear-> All.
    4. Manually go to last row in the worksheet and select the entire row.
    5. Now press Ctrl+ Shift + [down arrow].
    6. Select Edit-> Clear-> All.
    7. Save the file.
  3. If you are using charts and formatting in your sheet, first save the file in .html format then save it in .xls format with different name.
  4. Delete “last used cell” on your worksheet by pushing Ctrl+End. Sometime “blank cells” are not really blank and you should delete them because they take unnecessary space.
  5. Remove unnecessary formatting as it cause a file to be unnecessarily large. For removing formatting, select Ctrl+A and then select “Clear Format” from Edit toolbar.
  6. If you are using Excel sheet with only text save the file in .xml format and again save it in .xls format but with different name.
  7. Reduce the size of graphics as they take lot of space in Excel.
  8. Remove unnecessary comments, cell shading, header footer, hyperlink as they take up additional space.
  9. Delete hidden worksheets if you don't need them.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...