How-To

How to Reduce Excel File Size

Excel featured

Need to reduce your Excel file size? There are a few easy tricks you can try. This guide will show you what to do.

There are up to 1,048,576 rows and 16,384 columns in Excel—that’s a lot of cells to fill.

The trouble is, the more data you put into your spreadsheet, the bigger the file. If your Excel file becomes too large, it may be impractical to easily email to colleagues or clients. It can also slow down in Excel and stop working properly.

If you’re facing this problem, here are some effective tips on how to reduce Excel file size.

How to Reduce File Size in Excel by Deleting Worksheets

Using multiple worksheets in Excel makes it easier to structure your spreadsheets, but the more worksheets you use, the larger your file size will be. Removing unneeded worksheets can help to bring the file size down.

To delete worksheets in Excel:

  1. Ensure that the sheet you are intending to delete does not contain any data that is needed or referenced by other sheets.
  2. Right-click on the sheet tab at the bottom of the screen.
    Excel sheet tabs
  3. Select Delete.
    Deleting an Excel sheet

The sheet will be deleted—repeat the steps for any other sheets you want to delete.

Clear Pivot Table Caches

Pivot tables in Excel are a great way to analyze your data, but they can have a significant impact on the size of your Excel files. That’s because, by default, Excel will save your pivot table source data cache along with your file. It’s possible to change this setting, however, and have the data cache refresh when the spreadsheet is opened.

To stop the pivot cache from saving in Excel:

  1. Select any cell of your pivot table.
  2. Open the Pivot Table Analyze menu.
    Pivot table tab in Excel
  3. Select Options.
    Excel table options menu
  4. Select the Data tab.
    Excel table data options
  5. Ensure that Save Source Data With File is unchecked.
    Changing pivot table source data in Excel
  6. Click OK to confirm.

Save Formulas as Values

Excel formulas allow you to do almost anything you want in Excel using the in-built functions. Formulas take up more space than values, however, so if you have a lot of formulas in your spreadsheet that have already been calculated, you might want to consider converting these formulas to values.

To save a formula as a value in Excel:

  1. Select the containing your formula.
  2. Use the keyboard shortcut Ctrl+C on Windows or Cmd+C on Mac to copy the cell. Alternatively, you can right-click on it and select Copy.
    Copying data in Excel
  3. Right-click on the cell and select Paste Special Values.
    Pasting special values in Excel

Your formula will be replaced with the value of its result.

Remove or Compress Pictures

Pictures aren’t something that are commonly used in Excel spreadsheets, but if you have used some in your document, then these will increase your file size. You can either remove these pictures or compress them.

To compress a picture in Excel:

  1. Select the picture in your spreadsheet.
  2. The Picture Format tab will appear in the ribbon—select this menu.
    Excel picture formatting tab
  3. Press the Compress Pictures icon.
    Compressing pictures in Excel
  4. Select a resolution—the lower the resolution, the smaller your Excel file size will be.
    Excel compression options
  5. Ensure that Delete Cropped Areas of Pictures is selected.
    Deleting cropped areas in Excel
  6. If you want to apply the same compression to all of your pictures, make sure that the Apply Only to This Picture checkbox isn’t selected.
    Applying Excel compression to pictures
  7. Click OK to compress your pictures.

Reduce Excel File Size by Saving in XLSB Format

Another way to reduce the size of your Excel file is to save it in binary format. The default format is XML, which works better with other applications, but if you’re only planning to use your file in Excel, then saving it as a .XLSB file can help to reduce the file size.

To save an Excel file in .XLSB format:

  1. Press File > Save As.
  2. Select a location to save your file.
  3. From the list of file formats, select Excel Binary Workbook.
    Choosing a file format for Excel
  4. Press Save to confirm.

Solving Excel Problems

If you’re wondering how to reduce Excel file size, you can usually resolve the problem by following the steps above.

Excel is a powerful application, but when things go wrong, it can be frustrating to fix. If you’re having problems, you may want to try and stop Excel from crashing. If you’re getting notifications about circular reference errors, Excel can help you to track them down.

Click to comment

Leave a Reply

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

 

To Top