If the file size of your spreadsheet is too large, try the following tips to make it more manageable.
Save your spreadsheet in binary (.xslb) format
You can reduce your file size by saving your spreadsheet as a binary workbook (.xslb) instead of the default format (.xslx). The default format is XML based, which is important if you're using your data with third-party programs because XML is an open standard. However, for most purposes, the binary format is sufficient and saves to a smaller file.
-
Go to File > Options > Save.
-
Under Save workbooks, in the Save files in this format list, select Excel Binary Workbook.
This option sets binary format as the default. If you want to keep the default to Excel Workbook (.xlsx) but save the current file as binary, select the option in the Save As dialog.
Note: If the file is located in a SharePoint library, you might not see the Save As option. If this is the case, you can save a copy to your local drive, save that copy as a binary, and then replace the original file with the new binary.
-
Go to File > Save As, and if this is the first time you are saving the file, select a location.
-
From the list of file types, select Excel Binary Workbook (*.xlsb).
Save your spreadsheet in binary (.xslb) format
You can reduce your file size by saving your spreadsheet as a binary workbook (.xslb) instead of the default format (.xslx). The default format is XML based, which is important if you're using your data with third-party programs because XML is an open standard. However, for most purposes, the binary format is sufficient and saves to a smaller file.
-
Go to File > Options > Save.
-
Under Save workbooks, in the Save files in this format list, select Excel Binary Workbook.
This option sets binary format as the default.
If you want to keep the default to Excel Workbook (.xlsx) but save the current file as binary, select the option in the Save As dialog box:
-
Go to File > Save As.
-
In the Save as type list, select Excel Binary Workbook (*.xlsb).
Reduce the number of worksheets
If you have worksheets with data that you're not using—and that don't contain any formulas you're using—delete them from the spreadsheet.
The more data points you have in your workbook, the larger your file size will be. Removing unused data will reduce your file size.
Save pictures at lower resolutions
-
Go to File > Options > Advanced.
-
Under Image Size and Quality, do the following:
-
Select Discard editing data. This option removes stored data that's used to restore the image to its original state after it's been edited. Note that if you discard the editing data, you won't be able to restore the image.
-
Make sure that Do not compress images in file is not selected.
-
In the Default resolution list, select a resolution of 150ppi or lower. In most cases, you won't need the resolution to be higher than that.
-
Compress pictures
-
Select a picture in your document. The Picture Format tab appears on the ribbon.
-
On the Picture Format tab, in the Adjust group, select Compress Pictures.
-
Under Compression options, do the following:
-
To compress all the pictures in the file, clear Apply only to this picture. If this option is selected the changes you make here will only affect the selected picture.
-
Select Delete cropped areas of pictures. This option removes the cropped picture data, but note that you won't be able to restore it.
-
-
Under Resolution, do the following:
-
Select Use default resolution.
-
Don't save the pivot cache with the file
If your spreadsheet contains a pivot table, you can reduce the file size by not saving the pivot table source data cache with the file and instead having the data cache refresh when you open the spreadsheet.
-
Select any cell in the pivot table.
-
On the PivotTable Analyze tab, in the PivotTable group, select Options.
-
In the PivotTable Options dialog box, select the Data tab, and do the following:
-
Uncheck Save source data with file.
-
Select Refresh data when opening the file.
-
