Read this Excel tutorial until the end to learn how to reduce Excel file size in easy and effective ways.

When you keep adding new worksheets, datasets, tables, images, formulas, visualizations, and so on in your Excel workbook, its file size increases extensively. You often find that certain Excel files are way above the email attachment standard, which is 25 MB if you work in a real-world business.

Not to mention, large Excel workbooks may take minutes to open and recalculate the cells or cell ranges that have external references. Also, you’ll find it hectic to navigate around the Excel file. Moreover, if your PC has limited memory, which is the case for most workplaces, you can barely move from one worksheet to another.

In this scenario, if you wish to be productive when dealing with massive Excel files, I’ve got you covered. Find below multiple ways to reduce file size in Excel so you can conveniently open and modify workbooks.

Use File Compression Software

The best way to reduce the file size of an Excel workbook is by zipping it into an archive file using any offline or online software.

Offline Tool: 7-Zip

You can get the 7-Zip archiver for free. Once you download and install it, do the following to compress an Excel file using 7-Zip:

1. Right-click on the target Excel file.
2. Hover the cursor over 7-Zip in the context menu.
3. Choose Add to archive… option.

4. Inside the Add to Archive dialog, change the Compression level to 7 – Maximum.
5. Click OK to start the file compressing process.
6. Once done, you’ll find a compressed archive of the target Excel file in the same name.

Sometimes, you may not be able to achieve enough file compression for a truly large Excel file. In those cases, you can use the Split to volumes feature of 7-Zip to create fragments of one file.

When the recipient extracts the file, 7-Zip or other archives shall automatically merge the pieces in one Excel workbook.

7-Zip works locally on your PC. Hence, it doesn’t upload any files to remote cloud servers. So, this method is suitable for Excel workbooks that contain confidential business or academic research data.

Online Tool: ClipCompress

If you don’t want to install any software or want to quickly compress an Excel file, this method is just for you. ClipCompress offers free solutions to reduce Excel workbook sizes to a great extent. I was able to achieve a 25% compression.

Go to the above portal and Select File to Compress button to upload your Excel workbook.

The tool shall visually show you the file uploading and compression progress bars.

Once the compressing process is over, click the Download link below the thick blue bar.

Now that you’ve downloaded the Excel file, hit the Delete file from server to remove your file from the website.

This method involves uploading your Excel workbook to an online server. The website uses HTTPS protocol to encrypt data communication between your PC and its server. You can use this website for not-so-sensitive spreadsheets.

Get Rid of Hidden Content

You can use the following VBA script to unhide all hidden worksheets, rows, and columns in your Excel workbook. Then, if you find unnecessary worksheets or datasets, you can remove those to reduce the overall file size.

Sub UnhideEverything()
    Dim ws As Worksheet
    Dim i As Integer
    
    ' Unhide all rows and columns in each worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Rows.Hidden = False
        ws.Columns.Hidden = False
    Next ws
    
    ' Unhide all sheets in the workbook
    For i = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.Sheets(i).Visible = xlSheetVisible
    Next i
End Sub

To create a macro using this script, check out the following article:

📒 Read More: How To Use The VBA Code You Find Online

Convert Needless Formulas to Values

Array formulas, volatile functions (e.g., NOW(), TODAY()), complex formulas, and external references in formulas often contribute to the size increase of an Excel workbook.

Audit your workbook for scopes to convert calculated formulas into values and reduce the file size by a few percent.

Highlight cell or cell range that contains a formula. Press Ctrl + C to copy the content.

Press Ctrl + Alt + V to bring up the Paste Special dialog. Here, choose Values under the Paste section.

Excel shall paste the values resulting from the formulas in the cell range and get rid of the formulas afterward.

Reduce File Size in Excel By Changing Format

Find below the two most common Excel file conversion tips to reduce the size of the workbook:

Use Comma-Separated Values (CSV)

If you can afford to convert your Excel workbook or worksheets into plain text format delimited by commas, you should follow this method. be mindful that you’ll lose all formatting, images, visualizations, etc. This method is best suited if you’re storing a large database in Excel for future use.

On your workbook, go to File > Save As to open the Save As menu.

Click the Browse button and navigate to a directory where you’d like to save the CSV file. The Save As dialog shall open.

Click the Save as type drop-down menu and choose CSV file type from the list.

Click the Save button to complete the process.

Use Excel Binary Workbook (XLSB)

XLSB files are binary files, meaning they store data in binary format rather than Open XML used by XLSX (typical Excel workbooks) files.

Hence, binary Excel file formats offer faster performance and smaller file sizes compared to XLSX formats.

You also don’t lose anything from your original workbook. Moreover, when needed for compatibility, you can easily switch to XLSX from XLSB.

To save your Excel file in binary format, follow the steps mentioned earlier until you reach the Save As dialog.

Now, in Save as type, choose XLSB, and hit the Save button.

I achieved a 48% decrease in file size by converting XLSX to XLSB.

If you wish to automate the XLSX to CSV and XLSB file conversion process, you can instead use this VBA script:

Sub SaveWorkbookAsCSVorXLSB()
    Dim FilePath As String
    Dim FileFormat As Long
    
    'Prompt user to choose the file format
    Dim Choice As String
    Choice = InputBox("Choose the file format:" & vbCrLf & _
                      "Enter '1' for CSV" & vbCrLf & _
                      "Enter '2' for XLSB", "Save Workbook As...")
    
    'Validate user input
    If Choice = "1" Then
        'CSV selected
        FileFormat = xlCSV
    ElseIf Choice = "2" Then
        'XLSB selected
        FileFormat = xlExcel12
    Else
        'Invalid input
        MsgBox "Invalid choice. Operation canceled.", vbExclamation, "Save Canceled"
        Exit Sub
    End If
    
    'Prompt user to select the location and provide a filename for the saved file
    FilePath = Application.GetSaveAsFilename(FileFilter:=IIf(FileFormat = xlCSV, "CSV Files (*.csv), *.csv", "Excel Binary Workbook (*.xlsb), *.xlsb"))
    
    'Check if a file path was selected
    If FilePath <> "False" Then
        'Save the workbook
        ThisWorkbook.SaveAs FilePath, FileFormat
        MsgBox "Workbook saved successfully!", vbInformation, "Save Complete"
    Else
        MsgBox "No file path selected. Operation canceled.", vbExclamation, "Save Canceled"
    End If
End Sub

The macro created using this script will ask you to enter 1 for CSV and 2 for XLSB.

It’ll also show a Save As dialog to save the file where you want visually.

Compress Images in Excel to Reduce File Size

When you add a picture to your Excel worksheet in JPEG, PNG, GIF, etc., format, the size of the overall file increases proportionately. If your Excel workbook is 1 MB and the picture is 2 MB, the total file size becomes 3 MB.

In the original sales_data_sample.XLSX file, I added a JPEG image of 260 KB and the resulting workbook size increased to 621 KB (sales_data_sample_2.XLSX).

However, Excel offers an image compression tool so you can reduce the picture file size to optimize the overall workbook size.

Open your workbook and navigate to the worksheet that has one or many pictures.

Click on one picture and go to the Picture Format tab. Inside the Adjust commands block, click the Compress Pictures button.

Choose the E-mail (96 ppi) option in the Compress Pictures dialog to reduce the image size without affecting its visual appearance.

To apply this compression ratio to all the images in the workbook, uncheck the Apply only to this picture checkbox before hitting the OK button.

Reduce File Size in Excel by Discarding Pivot Cache

When you create a PivotTable using a dataset in the workbook, Excel creates a Pivot Cache. All the data manipulation you do in a PivotTable takes place in the Pivot Cache. Hence, the overall workbook size increases.

However, for long-term or cloud storage of a large Excel workbook with hundreds of PivotTables, you can delete the Pivot Cache before saving the workbook. This way, you’ll save on disk space. If you reuse the workbook for data analysis, Excel shall automatically create the Pivot Cache once again.

Go to the sheet where you’ve created the PivotTable. Click on the table to activate PivotTable.

Navigate to the PivotTable Analyze tab. Go to the PivotTable block or click on the PivotTable drop-down. There, you should see Options. Click on that.

On the PivotTable Options dialog, go to the Data tab. There, uncheck the checkbox for Save source data with file. However, checkmark the checkbox for Refresh data…

This way, you can significantly save disk space when storing or sharing your Excel workbook.

Remove Conditional Formatting

Conditional formatting rules increase the file size of an Excel workbook. It happens, especially if these rules are applied extensively across a large range of cells or when the rules involve complex formulas along with excessive formatting styles.

Each conditional formatting rule added to the workbook requires additional storage space within the file to store the rule’s parameters, formulas, and applied formatting settings.

If you have many rules or if they are resource-intensive, it can result in a noticeable increase in file size.

If there are any redundant conditional formatting rules in your workbook, remove those to save on file size and performance.

Open your workbook and go to a worksheet that contains multiple conditional formatting rules.

Press the Alt + H + L + R keys to open the Conditional Formatting Rules Manager dialog.

You can now audit all the rules in one dialog box by switching to This Workbook or This Worksheet from the top drop-down menu.

If you see any rule you can remove, select it, and click the Delete Rule button.

Crop Each Sheet to the Used Range

You can use the following Excel VBA script to crop all the worksheets to their used ranges to save on disk space.

Sub CropToUsedRange()
    Dim ws As Worksheet
    Dim rng As Range

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Find the used range of the worksheet
        Set rng = ws.UsedRange

        ' Crop the worksheet to the used range
        ws.Rows.Hidden = True
        ws.Columns.Hidden = True
        rng.EntireRow.Hidden = False
        rng.EntireColumn.Hidden = False
    Next ws
End Sub

As soon as you execute a macro made of the above script, Excel hides all unused columns and rows in all the sheets of the file.

Delete Watches to Reduce File Size in Excel

Cell watches are essentially formulas or references set up to monitor changes in specific cells. The database of watch values increases the file size.

Open your workbook and go to a worksheet. Now, open the Watch Window by pressing Alt + M + W on your keyboard.

If you see a database as shown in the screenshot, select all of the entries in the dialog box. You can do this by clicking the first row, scrolling down to the last row, pressing Shift, and clicking the final row.

Click Delete Watch to remove all the watches from your worksheet.

Conclusions

Whether you’re a master of Excel data analytics and visualization or just starting to learn Excel, the file size of your workbook can become a great barrier between you and your collaborators.

Also, larger Excel files can eat up disk space on your PC, server, or cloud storage unnecessarily.

Use various methods shown above to reduce Excel file size for different scenarios. If you know a method that might help others but missing in this article, comment below.