How to Make an Excel File Smaller

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

 

 

Remove Empty Rows and Columns

To see the rows and columns that are being used in your worksheet, press CTRL + END. The mouse pointer moves 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!

 

 

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 + ↓. The last cell in Column A in the worksheet is selected – this being Row 25,394.
2. Press END + →. 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 moves to Column C25394.

 

 

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.

 

 

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.

 

 

2. 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.

 

 

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

Remove the Pivot Cache

If you have any pivot tables, 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.

 

 

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

 

 

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

 

 

Save as a Binary File

Finally, to really reduce the file size significantly, you can save the file as a binary file (.xlsb extension) 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.

 

 

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

 

 

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