See all How-To Articles

How to Make an Excel File Smaller

This tutorial shows five ways to make an Excel file smaller.

 

ReduceFileSize Intro

 

Remove Empty Rows and Columns

To see the rows and columns that are being used in your worksheet, press CTRL + END. The mouse pointer will move to the last cell in the worksheet. This may or may not contain data. If this does not contain data, then remove all the rows and columns from the last cell that does contain data, down to the cell that CTRL + END goes to. Excel saves data points for these cells even though they are empty, and this increases the size of your file!

ReduceFileSize RemoveRowsAndColumns

 

In the example above, pressing CTRL + END on the keyboard takes you to cell E25437. However, that cell is blank and not the actual last data cell.

  1. Go back to cell A1, and press END + Down Arrow. The last cell in Column A in the worksheet is selected – this being Row 25,394.
  2. Press END + Right Arrow. This jumps to the last column – Column C in this example. The last cell being used in the worksheet is therefore C25394 and not cell E25437.
  3. You therefore need to delete Rows 25,394 to 25,437 and Columns D and E from the worksheet.
  4. Save the file after removing the excess rows and columns to reduce the file size.
    Now when you press CTRL + END, the mouse pointer will move to Column C25394.

 

ReduceFileSize ClearRows

 

Remove Empty Worksheets

Similarly, if you have empty worksheets in the file, they also could be making the size of the file larger. Remove any empty worksheets from the file by deleting the worksheets.

Right-click on a sheet tab and select Delete to remove the sheet from the workbook.

 

ReduceFileSize DeleteSheet

 

Save Pictures at Lower Resolutions

If your workbook contains any pictures, you can reduce the resolution of these pictures to reduce the size of your file.

  1. Select the picture whose resolution you wish to reduce, then in the Ribbon, select Picture Format > Compress Picture.

 

ReduceFileSize CompressPicture

 

  1. Clear the option Apply only to this picture to reduce the size of all the pictures in the file, and then (if it is not already selected), select Use default resolution.

 

ReduceFileSize Picture_Size

 

  1. Click OK to apply the compression to the pictures in the file.

Remove the Pivot Cache

If you have any Pivot tables in your file, removing the Pivot Table cache will reduce the size of your file.

  1. Select any cell in the Pivot table, and then, in the Ribbon, select PivotTable Analyze > Pivot Table > Options.

 

ReduceFileSize PivotRibbon

 

  1. Select the Data tab, clear the option Save source data with file, and click OK.

 

ReduceFileSize PivotOptions

 

  1. Save the file to apply the changes. You will notice that the file size is once again reduced.

 

ReduceFileSize Pivot

 

Save as a Binary File

Finally, to really reduce the file size significantly, you can save the file as a binary file (.xlsb format) rather than a default Excel file (.xlsx). The default format is XML based which may be important if the data in your file is going to be used by other third-party programs. However, for the most part, the binary form works just as well and saves to a much smaller file.

  1. In the Ribbon, select File > Save As.

 

ReduceFileSize SaveAs

 

  1. In the drop-down box below the file name, select Excel Binary Workbook (*.xlsb) from the drop-down list, and then select Save.

 

ReduceFileSize SaveAs Binary

 

You can see once again that the size of the file is greatly reduced!

 

ReduceFileSize BinarySave