There are some simple things you can do in order 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 encase something goes wrong).
1. 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.
The following will need to be done on each tab:
2. 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:
a. 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. Rick click and delete.
b. 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.
3. 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.
4. Clean Up Formulas – To many formulas will cause Excel to get bogged down, try the following:
a. Set your Excel Options so that workbook calculation is manual. File > Excel Options > Formulas > Manual.
b. 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.
Save after each step to see if the file size is improving and ensure there is still a need to continue to improve file size.
RSS Feed
