How to Repair and Condense an Excel File?

Microsoft Excel file sizes continue to grow with increasing usage. The heavy files are prone to corruption and other issues. Fix the large file size issue by condensing and repairing the Microsoft Excel file. The write-up below will help you learn how to condense and repair an Excel file. However, if the file is damaged due to its large size, use the advanced Microsoft Excel repair tool like Excel Repair Tool.

The workbook may contain additional objects like shaded cells or images which exceeds the file size. Too large Excel spreadsheets may slow down the performance, loading time, or corrupt files. Moreover, it becomes quite difficult to circulate these heavy worksheets to colleagues. So, we need to reduce the file size, especially when it exceeds 50 MB. There are many tips to condense the Excel file size to repair the file. Some of them are described in the article.

Why Condense an Excel File?

In Microsoft Excel, you can easily create a large spreadsheet, but as the file size increases, there more memory is required to open it on the system. Sometimes it may take more time which hinders system performance. Also, there are higher chances of file corruption due to the large file size. For this, there is a need to keep the spreadsheet small with fewer records. For this file, condensing is the best file optimization tip to reduce the original file size and ease fast data transfer.

Condense and fix an Excel file

Excel file condense helps in improving the speed of the Spreadsheets and ease the file sharing process. The file can be minimized by removing unnecessary data like Pivot cache, formulas, or formatting or many other methods. Here is the list of some effective methods to condense an Excel file:

Method 1: Reduce Excel File Size by Saving It in Binary Format

  • Save the Excel worksheet as a Microsoft Excel Binary  Worksheet (.xlsb) to minimize the file size. The default format of the workbook is an open standard format, i.e., XML. However, using the binary format of the file is enough for major tasks and also reduces the file size. Follow the below steps to save the Excel file in a binary format:
  • Open the desired worksheet.
  • Locate File tab.
  • Choose Save as button.
  • The Save as wizard will be displayed as shown in fig:

  • Select the location where you need to save the file.
  • Under “Save as type,” click on the arrow to choose the format list.
  • Choose “Excel Binary Workbook.”

  • Click Save.

Tip: you can verify the Microsoft Excel Binary Worksheet (.xlsb) format by right-clicking on the desired file as in fig:

Method 2: Check the Resolutions of Pictures in Worksheets

If the images saved in the worksheets are at a higher resolution, they may make the file large; follow the steps to save the images at lower resolutions:

  • Open Excel.
  • Go to File, click Options as shown below:

  • It will display the Excel Options wizard.
  • Under the Image Size and Quality section, there are below options:
    • Discard editing data.
    • Do not compress the image.
  • Ensure you have not selected the “Do not compress images in file” option.
  • Place a tick on “Discard Editing data” to remove the data stored for image retrieval.
  • Note: If you select “Discard editing data,” it won’t restore the picture.
  • Enter resolution upto 150ppi in the “Default target output to“.

Method 3 – Avoid Saving the Pivot Cache With the File

The Pivot table stores the copy of the source data in the file, due to which the file size increases or may slow down the opening of the worksheets. You can reduce the size of the pivot table by not saving the pivot table cache with the file. Instead of it, use the below steps to enable “refresh data when opening the file” option:

  • Open the Worksheets with pivot table.
  • In pivot table, right-click on any cell.
  • Choose” PivotTable_Options…”
    The PivotTable option will display as shown in fig:

  • Click the Data tab and do the below actions:
    • Unselect the “Save source data with file” option.
    • Select “Enable show details.”
    • Tick mark the “Refresh data when opening the file.”

    • Click OK.

Method 4: Compress Pictures in an Excel Worksheet

  • Uncompressed images occupy a large storage space which may slow down the opening of the file. You need to condense an Excel file to reduce the image size. Follow the steps to compress the large-sized images in Excel:
  • First, open the worksheet with an oversized image.
  • Select the image you need to un-compress.

  • Click the Format tab, and choose “Compress Pictures.”
  • The compress pictures window will display as shown in fig:

  • Below Compression options, do the below tasks:
  • First, select “Apply only to this picture.”
  • If you need to delete cropped images’ data, select the “Delete cropped areas of pictures.” option. Once you select this option, you won’t be able to restore the image.
  • Under Target output, select “Use document resolution.”
  • Click OK.

Method 5: Reduce the Unnecessary Data in a Workbook

The Excel workbook may contain more worksheets, and data points. The more the worksheets, the larger the excel file size will be. Therefore, you need to delete unnecessary data points like unused excel formulas, cell formatting, conditional formatting, etc.

Clear cell formats in Excel:

Reduce file size by removing unnecessary cell formats from the Excel worksheet. To do this, here are the steps:

  • First, select the desired cell on the worksheet.

  • On the Home tab, right side of the Editing section, click the Clear button.
  • Choose the Clear formats option from the drop-down as shown below:

  • It removes the cell formats as shown below:

Conclusion

Large Excel files are prone to file corruption and may result in Excel errors. Using the above methods, you can minimize the file size by condensing an Excel file. However, sometimes the file becomes un-accessible with file corruption in which manual methods do not work. Use the advanced repair tool like Stellar Repair for Excel to repair large-sized Excel file corruption.
The software helps fix file corruption and keeps the Excel data intact.

Credits: Microsoft excel Logo Icon by Pixel Icons on IconScout

Comment Policy: Comments adding value to the article are encouraged. Relevant links will be allowed in such comments.
If you think that you have a link that adds value to this article please contact us at techie[at]techzog[dot]com for evaluation of inclusion into the article.
Comments left solely for spamming links will be deleted. Thank you for understanding.

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.