Tuesday, May 17, 2011

How do you fix large file sizes in Microsoft Excel?

Are you wondering why your Excel file size is so huge? Is there a way to make large file sizes into smaller, more manageable sizes? As a matter of fact, there is! One method is to examine each separate object in your spreadsheet files so that you can see how it contributes to the objects' overall size. Here's how to do it:

  • Save your file
  • SaveAs a completely new name so that you can mess about with it
  • SaveAs Web page (choose 'Other Formats' in Excel 2007)
  • Open Explorer
  • Navigate to where you saved the file
  • Open the folder with the same name as your file
  • Sort descending by the file size
    • The largest objects will be named and at the top

This works best when you are dealing with multiple sheets. If you only have one sheet, the most likely cause is file formatting. My advice is to recreate the sheet with no formatting, then add the formatting to entire sections such as columns or large ranges.

  • Do not format a range of cells, and copy that formatting to other cells.
  • You can find out exactly how small your file could be by copying only the data to a new sheet and saving that.

Why does the file size get so huge? It could be due to a corrupt spreadsheet, which I will tell you how to fix in an upcoming post!