Reduce Size of Excel Files

Excel file sizes have a way of ballooning on you as you create and edit them. Your workbook usually contains text, but it may also contain charts and colorful graphics that take up a lot of bytes. Sending these huge files over email clogs your server, and storing them on your hard disk gobbles up available space. Fortunately, you can take several steps to reduce the size of your Excel files.

Steps

Try Some Easy Fixes

  1. Check for formatting on unused cells. By deleting all unused rows to the bottom of the document on each worksheet, you can greatly reduce the file size.
  2. Save the file to Excel in binary (.xlsb). Doing this structures the workbook into a compressed folder that has many binary files. The amount of compression that you're going to get depends on the contents of the workbook. Also, you may find that opening, closing and saving the file takes longer than something saved just in regular (.xls) format.[1]
    • After you've saved the file as an .xlsb, highlight and delete all of the cells outside the data range (typically to the right and below).
    • Highlight the same cells and delete all formatting (colors, lines, etc.). Delete formatting for each for each sheet within the workbook.
    • Re-save the workbook as an .xlsx file. Check to see how much these actions reduced the file size.
  3. Save your workbook as the same file and type but under an alternate file name. Just the process of conversion may remove extra space or inefficiency within the file.

For Workbooks Containing Both Charts and Formatting

  1. Open the Excel file.
  2. Save the Excel file in .html format.
  3. Change the filename of the .html document. Then save the new document in .xls format.

For Workbooks Containing Only Text

  1. Open the Excel file.
  2. Save the Excel file in .xml format.
  3. Rename the .xml file and then re-save it as an .xls file under the new name.

Use WinZip

  1. Open your Documents and find the file that you want to compress. Click on the filename and change the .xls suffix (file type) to .zip.
  2. Click on the file and choose "Open with WinZip." Then choose a destination for the extracted contents.
  3. Re-zip the file contents using WinZip.
  4. Open Documents. Highlight the file and change the .zip suffix to .xlsx. You should notice a substantial reduction in the size of the file.[2]
  5. Alternatively, navigate to the worksheets subfolder to identify the largest (oversized) worksheets; reopen the original excel file; create the required number of new sheets and open each oversized worksheets in turn; select cells containing data and copy and paste into a new sheet; delete the oversized worksheets and save.

Getting to the Bottom of the Error Source

  1. After having identified the worksheet that is causing the file to be large, SAVE FILES AS filename.xls, this will identify the sheet and the problem. This means your data has gone outside the Excel limit.
  2. Make a copy of the Excel (to play around with), delete each column after and keep on saving the worksheet.
  3. You will notice that when you delete the “faulty” column the time taken to save will reduce and if you check the file size it would have come down from megabytes to kilobytes.

Save As

This method works for spreadsheets with images.

  1. Click file, then save as.
  2. Next to save button you'll find the tools dropdown menu. Click it.
  3. Compress pictures.
  4. Compress to screen or print sizes.
  5. Save. Done.



Tips

  • To compress Excel files on a Mac, try third-party software such as FILEminimizer Office or Express Zip Mac Compression Software.
  • If you're emailing an Excel file from a Mac to a Windows computer, be sure that you save the Mac workbook as a .xlsx file. You'll ensure that Windows won't have trouble opening your file and that the file won't undergo any weird reformatting when it's opened on a PC.[3]

Warnings

  • Always make a backup of your original file before you try to compress it. While you're trying out these tips, you could lose formatting, charts and other crucial components from your original workbook.
  • If you have an excel file with macros, saving to HTML or XML will strip your code. Instead, go through each worksheet manually and remove formatting in unneeded cells.

Related Articles

Sources and Citations

  1. ↑ http://msdn.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_WorkbookOpeningClosingSavingSize
  2. ↑ http://datapigtechnologies.com/blog/index.php/how-to-compress-xlsx-files-to-the-smallest-possible-size/
  3. ↑ http://www.michaelhanscom.com/eclecticism/2010/01/26/microsoft-excel-xls-and-xlsx-weirdness/
Retrieved from "https://kipkis.com/index.php?title=Reduce_Size_of_Excel_Files&oldid=361649"