How to Reduce Excel File Size (All Methods)

We show you how to compress Excel files using Binary Format for saving, removing unneded data, compressing images, clearing pivot cache and using ZIP-files to store your spreadsheets.

Featured - How to reduce excel file size

stands as a stalwart tool, often housing vast amounts of data, complex formulas, and intricate charts in one spreadsheet. However, as the richness of your data grows, so does the size of your Excel files. This burgeoning file size can become a thorn in the side of efficiency, leading to sluggish performance, protracted file opening times, and challenges in sharing via email or collaborative platforms.

Why Is My Excel File So Large?

Why do Excel files become so unwieldy, and what can you do about it? Large Excel files are often the result of accumulated data, excessive formatting, embedded images, and a plethora of formulas and pivot tables. While these elements are essential for analysis, they can make your workbook slow to respond and difficult to manage.

Reducing the size of an Excel file is not just about reclaiming disk space—it's about enhancing the usability and accessibility of your data. Whether you're a financial professional grappling with complex models, an analyst dealing with comprehensive reports, or a student organizing your thesis data, optimizing your Excel file size can save you time and frustration.

In this tutorial, we will explore a variety of strategies to slim down your Excel files without compromising the integrity of your data. From auditing your workbook to understand where the bulk lies, to compressing images, removing unnecessary formatting, to clear Excel cache, and leveraging Excel's binary format, we will guide you through actionable steps to achieve a leaner, more efficient workbook.

How to Reduce Excel File Size by Saving Data in Binary Format

The binary format (.xlsb) can significantly reduce the size of your Excel file. Unlike the standard .xlsx or .xlsm formats, which save data as XML, the binary format saves data using much less space. This can lead to a smaller file size, making it easier to manage and share your Excel files. Workbooks saved in the binary format also often open and save faster, especially if they are large or contain complex calculations. This is due to the streamlined way that data is stored and accessed in the binary format.

  • Compatibility Considerations: While most features and functionalities are preserved in the binary format, there may be some compatibility issues with other software or services that expect Excel files to be in the XML format. Additionally, some Excel features, like Power Query, may not work with binary format files.

  • Macro Awareness: If you use macros, be aware that the binary format does not change the visibility of macros. However, users might not expect macros to be present in a binary file, as the usual macro-enabled file extension is .xlsm, not .xlsb.

  1. Click “File” to open the main menu of Excel
     
    Windows 11 - Excel - File
  2. Click “Save a Copy” and then “Browse”
     
    Windows 11 - Excel - File -Save a Copy - Browse
  3. Select “Excel Binary Workbook (*.xlsb)” in “Save as type” and click “Save”
     
    This will save a copy of your original Excel file in “Excel Binary Workbook”-format which is usually much smaller than the default Excel file format *.xlsx.
     
    Windows 11 - Excel - File -Save a Copy - Browse - Save as Type Excel Binary Workbook - Save

How to Save Excel Files in Binary Format per Default

By configuring Excel to save your workbooks in the binary format by default, you ensure that every file benefits from the reduced file size without having to remember to change the format manually each time you save. This setting can streamline your workflow and automatically keep file sizes in check. Workbooks saved in the binary format also often open and save faster, especially if they are large or contain complex calculations. This is due to the streamlined way that data is stored and accessed in the binary format.

  • Compatibility Considerations: While most features and functionalities are preserved in the binary format, there may be some compatibility issues with other software or services that expect Excel files to be in the XML format. Additionally, some Excel features, like Power Query, may not work with binary format files.

  • Macro Awareness: If you use macros, be aware that the binary format does not change the visibility of macros. However, users might not expect macros to be present in a binary file, as the usual macro-enabled file extension is .xlsm, not .xlsb.

  1. Click “File” to open the main menu of Excel
     
    Windows 11 - Excel - File
  2. Click “More” and select “Options”
     
    Windows 11 - Excel - File - More - Options
  3. Select Excel Binary Workbook as the default file format for saving
     
    Click “Save” in the left sidebar and select “Excel Binary Workbook (*.xlsb)” where it says “Save files in this format”. All new workbooks will be saved as .xlsb unless you change the file type manually in the “Save As” dialog box. This means you don't have to remember to switch formats each time you save a new file, which can save time and ensure consistency.
     
    Windows 11 - Excel - File - More - Options - Save - Excel Binary Format
  4. Save changes with “OK”
     
    Windows 11 - Excel - File - More - Options - Save - Excel Binary Format - Ok

How to Reduce Excel File Size by Deleting Unnecessary Worksheets

Worksheets that are no longer needed can inflate your Excel file unnecessarily. By identifying and removing these excess sheets, you can trim down the file size. This is a straightforward method to eliminate unused data and can have an immediate impact on the file size.

  • Right-click on the Excel sheet you want to remove and click “Delete”.
     
    Windows 11 - Excel - Sheet - Delete

How to Make an Excel File Smaller by Saving Images at Lower Resolution

High-resolution images can be one of the main culprits of a bloated Excel file size. By saving images at a lower resolution after inserting them into your workbook, you can significantly reduce the file's size without a substantial loss of quality, especially if the images are only for reference or illustrative purposes.

  1. Click “File” to open the main menu of Excel
     
    Windows 11 - Excel - File
  2. Open “Options”
     
    If the Excel window is not in fullscreen-mode, you might first have to click “More..”.
     
    Windows 11 - Excel - File - More - Options
  3. Change Excel Image Size and Quality settings
     
    Click “Advanced”, Check “Discard editing data”, uncheck “Do not compress images in file” and set a lower “Default resolution”.
     
    • Discard Editing Data: Checking “Discard editing data” will remove any stored image editing data (like cropping and adjustments) that Excel retains to allow you to revert to the original image. This can reduce the file size because Excel will no longer store the additional data needed to undo image edits.

    • Image Compression: Unchecking “Do not compress images in file” allows Excel to compress the images in your document. This means that Excel will reduce the file size of the images, which can significantly decrease the overall file size of the workbook, especially if it contains many or large images.

    • Default Resolution Setting: Setting a lower “Default resolution” will decrease the dots per inch (DPI) setting for images inserted into the workbook. This means that any new images added to the workbook will be automatically compressed to this resolution setting. Lower DPI images have fewer pixels and therefore take up less space, reducing the file size further.

      • 96 ppi (pixels per inch): This is considered screen resolution because it matches the display resolution of most computer screens.
      • 150 ppi: This resolution strikes a balance between file size and image quality. It's better than screen resolution for printed materials but still helps to keep the file size down compared to higher resolutions.
      • 220 ppi: This setting is a higher quality that works well for printed documents, providing a finer level of detail without being as large as the highest available settings.
      • 330 ppi: This is a high-resolution setting used for documents that require detailed images when printed, such as reports that will be professionally published or materials that include fine print or detailed graphs.
         
    • Impact on Image Quality: While these changes will reduce the file size, they may also lower the visual quality of the images. This might not be an issue for images viewed on-screen within Excel, but it could be problematic if you plan to print the workbook or if precise image details are important for your data presentation.

    • Global Change: These settings are typically global, meaning they will affect all workbooks you work on within Excel, not just the current document. You should consider whether this change aligns with your needs for other projects as well.
       
      Windows 11 - Excel - File - More - Options - Advanced - Image Size & Quality

  4. Click “OK” to save the changes.
     
    Windows 11 - Excel - File - More - Options - Advanced - Image Size & Quality - Save

How to Reduce Excel File Size by Compressing Images

Excel offers built-in tools to compress images that are already embedded in your workbook. This method is useful for reducing the file size after the fact, allowing you to maintain visual aids in your data without the associated file bloat.

  1. Select one image and click “Picture Format” in the Ribbon
     
    Windows 11 - Excel - Select Image - Format Picture
  2. Click the “Compress Pictures” button in the “Adjust” section
     
    Windows 11 - Excel - Select Image - Format Picture - Adjust - Compress Pictures
  3. Set the Excel image “Compression options”
     
    • To compress all images, uncheck “Apply only to this picture”.
    • Check “Delete cropped areas of pictures” to remove invisible but restorable parts.
    • Select “Use default resolution” to maintain image quality or a lower resolution to gain additional space.
    • Click “OK” to finally compress the image(s).
       
      Windows 11 - Excel - Select Image - Format Picture - Adjust - Compress Pictures - Save Changes

How to Reduce Excel File Size by Not Storing Pivot Cache for Pivot Tables

Pivot tables create a hidden Pivot Cache that can inadvertently increase file size. By disabling the option to save this cache with your workbook, you can reduce the file size. This method is particularly effective if you use pivot tables extensively but do not require the cache for future use of the data.

  1. Select a cell in the pivot table and click “PivotTable Analyze” in the Ribbon
     
    Windows 11 - Excel - Select Cell - Pivot Table Analize Tab
  2. Click “PivotTable” and then “Options”
     
    Windows 11 - Excel - Select Cell - Pivot Table Analize Tab - Pivot Table Group - Options
  3. Adjust “PivotTable Options” and save with “OK”
     
    • Uncheck “Save source data with file”: This option will stop Excel from storing a copy of the data used to create the PivotTable with the workbook. This can significantly reduce the file size, especially if the source data is large. However, it means that if you or someone else opens the Excel file without access to the original data source, the PivotTable will not be able to display any data or refresh.
    • Check “Refresh data when opening the file”: This setting ensures that every time the Excel file is opened, the PivotTable data will be refreshed automatically. This means it will pull the latest data from the source, assuming the source is accessible. This is useful for keeping the PivotTable up to date, but it could also slow down the opening of the file if the data source is large or if it's located on a slow network.
       
      Windows 11 - Excel - Select Cell - Pivot Table Analize Tab - Pivot Table Group - Options - Data - Accept

How to Zip an Excel File to Compress It

Zipping an Excel file is a universal method to reduce its size for storage or sharing. This method compresses the file outside of Excel, often resulting in a significant reduction in size. It's a final step that can be used in conjunction with other file size reduction methods for maximum efficiency.

  1. Click “New” in “File Explorer”
     
    Windows 11 - File Explorer Location - New
  2. Select “Compressed (zipped) Folder
     
    Windows 11 - File Explorer Location - New - Compressed
  3. Name the file
     
    Windows 11 - File Explorer Location - New - Compressed Folder - Name
  4. Cut or copy the Excel file(s) you want to compress into a ZIP-file
     
    Windows 11 - File Explorer Select Files - Cut
  5. Double-click the ZIP-file you created previously
     
    Windows 11 - File Explorer Select Files - Cut - Open Compressed Folder
  6. Paste the Excel-files to save them in the compressed ZIP-file
     
    Windows 11 - File Explorer Select Files - Cut - Open Compressed Folder - Paste
  7. You can open the ZIP-File and the Excel-files in there via File Explorer if needed
     
    Windows 11 - File Explorer Select Files - Cut - Open Compressed Folder - Paste - Result

Extra: How to Add Shading to Alternating Rows in Excel

It's an old trick at this point, but applying shading (zebra stripes) to alternative rows in Excel makes your sheet easier to read. The effect, also known as banded row, allows your eyes to keep their place more easily when you're scanning a spreadsheet. The difficulty, then comes in knowing where to look and how to format cells as a table in the first place. In our other guide, we show you how to apply and customize table formatting to form alternating rows in Excel.
 
Featured - How to Apply Shading to Alternate Rows in Excel

Extra: How to Divide in Excel

In Excel, you can divide using cell references, handle errors like dividing by zero, and even combine division with other operations for more complex calculations. In our other guide, you will learn basic to advanced functions for division, handle errors, and optimize calculations for maximum efficiency.
 
Featured - How to divide in Excel

Extra: How to Remove Table Formatting in Excel

In Excel, you can apply predefined table styles, making your data presentation-ready. However, there are instances where you might want to strip away the table formatting without losing the underlying data. In our other guide we show you various techniques to remove table formatting in Excel.
 
Featured - how to remove table formatting in excel

Extra: How to Structure Collected Data in Excel

To make the most of Excel's features, you need to structure your data properly. Data structure refers to how you organize your data in a spreadsheet. A good data structure makes it easy to perform calculations, filter, and sort data, create charts and pivot tables, and apply formulas and functions. In our other guide, we show you how to structure collected data in Excel using some best practices and tips.
 
Featured How to Structure Collected Data in Excel