Catalog

How to Compress Excel Files (5 Methods)

November 21, 2023 2.8K views

While Excel is a valuable product within the Microsoft Office suite, it may not be the optimal choice for file sharing. Unfortunately, sharing files, especially those containing heavy elements like images or detailed content, can pose challenges due to larger file sizes. Fortunately, there's an easy way to compress Excel files, significantly reducing load times during sharing. This facilitates smoother and more efficient file-sharing experiences.

 How to Compress Excel Files

How to Compress Excel Files

Why is Excel file size too big?

The size of an Excel file can swell up for several reasons, and addressing these issues is crucial for optimal performance. Some key factors contributing to large file sizes include:

  • Data Model and Data Visualizations: Workbooks with complex data models and visualizations can surpass file size limits. Optimize memory usage by creating a memory-efficient Data Model with Excel and Power Pivot. Utilize tools like Workbook Size Optimizer to analyze and compress the workbook further.

  • Formatting and Cell Styles: Extensive formatting applied to entire columns and unnecessary cell styles can lead to larger file sizes. Remove extraneous cell styles and named ranges using tools like XLStylesTool, reducing file size without opening it in Excel.

  • Memory and File Size Limits: Excel workbooks with a Data Model have a maximum file size of 2 GB in the 32-bit version, with a 4 GB memory limit. Exceeding these limits can prevent saving. Reduce the number of linked, imported, or copied tables, rows, or columns in the Power Pivot window to address this issue.

  • SharePoint and Microsoft 365 Subscription Limits: Different Microsoft platforms impose specific file size limits. SharePoint Online, for instance, has limits for workbooks stored in libraries on sites. Be aware of subscription-specific restrictions, as they can affect file size.

  • Pictures and Images: Large images or numerous pictures within the spreadsheet can significantly contribute to increased file sizes.

  • Pivot-Table Cache and Embedded Objects: Data required for pivot tables and embedded objects inside the Excel file can add to the file size. Manage this by optimizing the use of pivot tables and reducing embedded objects.

  • External Links: Links to other spreadsheets can also contribute to larger file sizes. Review and manage external links to minimize their impact on the overall file size.

How to Compress Excel File

Remove Unnecessary Worksheets/Data:

Step 1: Go to the sheet tab at the bottom of the Excel interface.

Step 2: Right-click on the extra sheets, choose "Delete".

Microsoft Excel delete sheet

Microsoft Excel delete sheet

Step 3: Confirm deletion in the warning message.

Microsoft Excel warning message

Microsoft Excel warning message

Remove Unnecessary Formulas:

Step 1: Select cells with unnecessary formulas.

Step 2: Press "CTRL + C".

Step 3: In the Home tab, click "PasteSpecial" from the drop down menu.

Microsoft Excel Paste special

Microsoft Excel Paste special

Step 4: Now, choose "Values", and click "OK".

 Microsoft Excel paste values only

Microsoft Excel paste values only

Compress Pictures in Excel:

Step 1: Click on the picture, go to the Picture Format tab.

Step 2: Click "Compress picture".

 Microsoft Excel compress picture

Microsoft Excel compress picture

Step 3: Uncheck "Apply only to this picture" if compressing all pictures, then click "OK".

Microsoft Excel compress all pictures

Microsoft Excel compress all pictures

Clear Data Formatting:

Step 1: Open the spreadsheet, click on the Home tab.

Step 2: In the ribbon, click "Clear", choose "Clear Formats".

 Microsoft Excel clear formatting

Microsoft Excel clear formatting

Remove Conditional Formatting:

Step 1: Select cells with conditional formatting.

Step 2: In the Home tab, click on Conditional Formatting, then "Clear Rules" > "Clear Rules from Selected Cells".

Microsoft Excel remove conditional formatting

Microsoft Excel remove conditional formatting

These methods help reduce Excel file size for easier sharing.

Tips for compressing an Excel file

Some strategic tips you can take a look into to effectively share your excel files or store them whilst they are not taking up extra space can help your Excel file.

  1. Image Compression: Efficiently compressing images within your Excel file can significantly contribute to size reduction. Leverage dedicated Excel compressor apps like NXPowerLite Desktop, simplifying the process of compressing images without compromising quality. Additionally, utilize Excel's built-in feature for compressing embedded images. This can be accessed through the Picture Format tab, providing nuanced control over compression settings.

  2. Excess Formatting Cleanup: Unnecessary formatting, especially across large worksheets, can bloat file sizes. Take advantage of tools such as NXPowerLite Excel Compressor or Excel's native Clean Excess Cell Formatting command found in the Inquire tab. These tools facilitate the removal of redundant formatting elements, enhancing file efficiency.

  3. Binary Format for Large Datasets: When dealing with substantial datasets, consider saving your file in the Excel binary format (.xlsb). This format offers enhanced compression compared to the standard XLSX. Alternatively, explore traditional Zip compression methods, particularly effective for reducing file sizes.

  4. PivotTable Cache Management: Effectively managing PivotTable cache is crucial for optimal file size. Right-click within a PivotTable, navigate to PivotTable Options, and switch to the Data tab. Uncheck 'Save source data with file' while ensuring 'Refresh data when opening file' is selected. This strategy prevents unnecessary data accumulation, promoting a leaner file.

  5. Embedded Object Compression: For objects embedded as icons, consider extracting them, compressing independently, and reintegrating into the Excel file. Tools like NXPowerLite Desktop prove valuable for compressing various embedded objects, particularly those in PDF or Office document formats.

  6. Object Size Reduction: Objects embedded as pictures can be optimized by converting them into simpler picture formats. Copy the object, paste it as a special picture format (e.g., JPEG or PNG), and subsequently remove the original object. This approach ensures visual integrity while reducing overall file size.

  7. Breaking Links to External Spreadsheets: Evaluate the necessity of links to external data within your spreadsheet. Breaking these links can substantially decrease file size. Access the Edit Links feature in the Data tab, select the links to be removed, and execute the Break Link operation.

  8. Exploration of Add-ins: Consider exploring Excel add-ins, such as Sheetwise, designed to streamline the identification and management of embedded objects, images, and media. These tools offer a visual representation of resource sizes within your spreadsheet, aiding in efficient size optimization.

How to Compress Excel Files for Free in WPS Office

  • Why Choose WPS Office

Microsoft Office has witnessed a decline in its popularity, not due to a performance setback, diminished quality, or lackluster updates. Instead, formidable alternatives like WPS Office have emerged, presenting a significant challenge. WPS Office stands shoulder to shoulder with Microsoft Office, boasting compatibility across Linux, Mac, and Windows platforms, offering a versatile and robust suite of features. This competition introduces a dynamic shift in the landscape of office productivity tools, providing users with viable alternatives that cater to diverse operating systems.Beyond compatibility, WPS Office offers a comprehensive suite of features comparable to Microsoft Office, making it an attractive choice for users seeking a powerful and versatile office productivity solution.

WPS Office

WPS Office

One notable aspect of WPS Office is its user-friendly interface, simplifying the learning curve for new users and streamlining the overall experience. The seamless integration of essential tools, coupled with a commitment to cross-platform functionality, positions WPS Office as a compelling option for those looking to break away from the traditional Microsoft Office suite.

  • How to Compress Excel Files in WPS Office

WPS Office offers a straightforward option to compress spreadsheet files, but with a unique twist: you need to utilize WPS Spreadsheet online and save the file on WPS Cloud.

Step 1: Ensure your spreadsheet is saved on WPS Cloud. Open the file in WPS Spreadsheet, access the Main menu (top left), and click "Save As".

WPS Spreadsheet save files as

WPS Spreadsheet save files as

Step 2: In the Save As dialog box, choose "My Cloud Files" and save the file on WPS Cloud.

WPS Spreadsheet Cloud storage

WPS Spreadsheet Cloud storage

Step 3: Visit WPS Office Online to access WPS Spreadsheet online.

Step 4: Open the spreadsheet file saved on WPS Cloud.

WPS Spreadsheet online

WPS Spreadsheet online

Step 5: Click the Main menu (top right, next to the WPS logo).

Step 6: In the main menu, select "File Slimming" in WPS Spreadsheet.

WPS Spreadsheet online File skimming

WPS Spreadsheet online File skimming

Step 7: Your spreadsheet file will be cleaned and optimized. Click "Close" to share your compressed Excel file.

WPS Spreadsheet online file skimming report

WPS Spreadsheet online file skimming report

Initially, uploading to the cloud and using WPS Online may seem complex, but it proves advantageous, allowing convenient file access from any location. WPS Spreadsheet effectively addresses various user requirements.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

Q1. How do I split a large Excel file into multiple smaller files?

Certainly! Here's the revised set of instructions:

Step 1: Open the Excel spreadsheet in WPS Spreadsheet.

Step 2: Navigate to the Tools tab, and then click on the "Split or Merge" option.

WPS Spreadsheet Split or Merge

WPS Spreadsheet Split or Merge

Step 3: In the "Document Split or Merge" window, click on the "Split" button.

WPS Spreadsheet Document Split or Merge

WPS Spreadsheet Document Split or Merge

Step 4: Optionally, add additional spreadsheet files or click "Next".

WPS Spreadsheet add files to split

WPS Spreadsheet add files to split

Step 5: Choose where to save the split workbook on your computer and click on "Split".

WPS Spreadsheet split large excel files

WPS Spreadsheet split large excel files

Q2. How to sort largest to smallest in Excel without mixing data?

Here is a guide on how to sort Largest to Smallest in Excel without mixing data:

Step 1: Select any cell in your data.

Step 2: Navigate to the "Home" tab and locate "Sort & Filter" or go directly to the "Data" tab and select "Sort."

Step 3: Opt for Sort A to Z (ascending) or Sort Z to A (descending).

Step 4: For custom sorting, choose Custom Sort.

Step 5: In Custom Sort, Add Level and select columns for sorting.

Step 6: In the Custom Sort window, after adding a level, opt for "Values" to define how to sort the data in the chosen column. Then, choose your preferred order, like A to Z or Largest to Smallest.

Step 7: Repeat the steps for additional columns.

Step 8: Check "My data has headers" if your data includes headers.

Step 9: Select OK to apply the sorting.

Excel Efficiency Unleashed: Simplify, Compress, Thrive!

While Excel may not excel in terms of being a readily shareable file due to its file size, employing compression techniques can mitigate this issue. By learning how to compress Excel files, you can easily send it or store it without encountering storage problems. If you're on the lookout for spreadsheet options that occupy less storage space, consider exploring alternatives. One such option is to download WPS Office. It stands out for its incredibly lightweight nature while offering all the functionalities that Excel provides. Choosing WPS Office could be a great solution for your needs. Download WPS Office now to experience its benefits.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.