In addition to transportation disadvantages, a large Excel file is typically the culprit for performance drops when you’re opening, calculating, or saving a spreadsheet. If your file suddenly increased from KBs to MBs, it might be a good idea to troubleshoot for possible causes.

Some obvious methods like removing unnecessary formulas or data will certainly help, but there also are some more discrete methods you may have never noticed. In this article, we’re going to cover the most efficient ways to reduce file size of your Excel workbooks.

 

Remove Cells That Are Not Used

Probably the most common reason of a sudden increases in file sizes is including unused cells in worksheets. Although Excel doesn't explicitly show this, there’s a Used Range for each spreadsheet. Excel uses this term for the cells that have content or formatting, and ignores the remaining cells to improve performance. The computing power requirements are different for 100 cells and 17,179,869,184 cells, which is the limit Excel has since version 2007.

It’s a good idea to check whether Used Range of sheets of the workbook matches your actual data. There may be forgotten contents or formatted cells at irrelevant sections of your worksheets.

To find the end point of your sheet, use the CTRL + END combination on your keyboard. Let’s see this on an example.

Actual data:

End of the sheet:

If your actual data ends at J140 while the CTRL + END takes you to a cell like FK55535, you’ve just found the reason why your file is too large. To fix this, you need to remove extra cells. The easiest way to delete rows and columns is to select all rows to the end, and then,

1. Click the next row number where your data ends
2. Press END to activate End mode
3. Press SHIFT + ↓ to select through the end
4. Right-click any selected row number
5. Click Delete in the content menu

Repeat the same steps for columns as well, by selecting columns and pressing SHIFT + → to select columns to the right end.

Remember to save your file. Your spreadsheet should now go down in size.

 

Reduce the size and number of images

Images might be essential for spreadsheets in the form of logos and hardcoded visualizations. An Excel file can keep the original file and use it more than once if you’d like to create other images by copying from original. This is a built-in way to keep file size low.

Images can be duplicated in Excel when changing sizes, copying and pasting. Excel has another built in feature to reduce images file sizes: compressing.

To use the Compress Pictures feature, click one of the pictures and click the Compress Pictures icon under the FORMAT tab.

In Compress Pictures window, make sure that Apply only to this picture checkbox is unchecked to apply your settings to all pictures. Delete cropped areas of pictures option helps reduce image size as well.

Another important point is to select the E-mail option for the lowest ppi value, as well as picture size.

 

Reduce the number of graphics

Graphics are great way to improve visual presentation of spreadsheets. They get viewer's focus faster than anything else. However, but of course, this can come at a cost.

Extensive use of graphics increases the file size. Therefore, this is one of the items you may want to check when file size is an issue. Try to remove unnecessary graphics or combine them into fewer diagrams.

 

Remove Unnecessary Formats and Conditional Formats

Cell formatting needs disk space too. This can become problematic especially when used in large ranges. We recommend removing formatting from empty cells and raw data that are not displayed. To remove formatting,

1. Select the cell range
2. Under the HOME tab, click the Clear dropdown
3. Select Clear Formats item

Another thing to remember here is that Conditional Formatting is also technically formatting. This feature may require a lot of disk space when used in large ranges, mainly due to its ability to keep formulas. Make sure to check the references applied and remove the unnecessary applications. To remove a conditional formatting,

1. Select the range
2. Under the HOME tab, click Conditional Formatting dropdown
3. Hover the mouse over Clear Rules option
4. Click Clear Rules from Selected Cells

 

Save as XLSB

This is one of the most efficient ways to reduce file size of a workbook. However, we listed this as the last item because this actually means changing the file type. The XLSB file format is one of the new file format, introduced in Excel 2007. The XLSB file format, similar to old XLS, keeps data as Binary instead of XML, as in XLSX or XLSM.

Saving your file as XLSB may cause significant reduction in file size in some cases. If raw data and formulas taking the most of the space, making this transition will help the most.

Another advantage of the XLSB format is in performance. These files typically open and calculate faster than other extensions. However, it does have limited third party support and you may not be able to use some of your add-ins. Another disadvantage is that you can't know whether your file contains any macros. There is no distinction in the file extension as in XLSX and XLSM.

To save your file as XLSB, open the Save As dialog and select the Excel Binary Workbook (*.xlsb) option in the Save as type dropdown.