How to Reduce Excel File Size

Follow these 4 steps to reduce the size of your file and speed up Excel response time.

There are some simple things you can do to reduce the size of a large Excel file and speed up operation.  Below are some quick and easy steps to reduce Excel file size. (Note, before you begin, it’s a good idea to have a backup of your file just in case something goes wrong).

The first step applies to the entire file; however, steps 2 – 4 must be performed on each tab.  Save after each step and check file size!

Step-by-Step Instructions

Step 1 - File Type

File Type – If you’re using Excel 2007 or later, ensure your file is saved as .xlsx – not in Excel 97-2003. If that doesn’t cut enough into the size of your file, save it as an Excel Binary Worksheet (.xlsb). I just had a file that was 12.6 MB in .xlsx cut to 6.72 MB when saved as .xlsb.

Step 2 - Unused Cells

Check Unused Cells – frequently “blank” cells aren’t really blank and need to be deleted. You can check for the “last used cell” in your workbook by pushing Ctrl + End, this will take you to the last used cell. If that’s not the end of your cells with data, then you can clean up those unused cells. To do this:
  • Select the first blank column, hold down Ctrl + Shift and push the right arrow (this will highlight all columns). Then, right click and select delete. Do the same for rows; go to the first row after your data, select it, hold down Ctrl + Shift and push the down arrow. Right-click and delete.
  • You can also use the Go to Special Function. Press F5 > Special, then select “Blanks”; after all blanks are selected select “Clear All” from the Editing tool bar under the Home Ribbon.

Step 3 - Remove Formatting

Remove Formatting – Unnecessary formatting can cause a file to be unnecessarily large. Select all (Ctrl + a) and the select “Clear Formats” from the Editing tool bar under the Home Ribbon.

Step 4 - Formulas

Clean Up Formulas – Too many formulas will cause Excel to get bogged down, try the following:
  • Set your Excel Options so that workbook calculation is manual. File > Excel Options > Formulas > Manual.
  • Review your formulas to ensure you don’t have a lot of unnecessary formulas, or formulas that are considered volatile (i.e., always recalculated, even when precedents remain unchanged). Volatile functions are Randbetween( ), Today ( ), Offset ( ), Index ( ), Info ( ) etc.

There you go…4 steps on how to reduce excel file size. Let us know if this helped. Any other tips?

Back to Top

 

On October 29, 2015   /   How To, Useful Resources