Large files are time-consuming to work with and prevent efficient working so it’s worth trying to get them smaller. The screenshot shows a file I quickly reduced in size by over 80% using my TOP TIPS.
My TOP TIPS are easy to use...
- Save your file in binary format
File, save as…. then under “Save as type” select “Excel Binary Workbook" or "*.xlsb”.
Note: this tip reduces the file size taken up on your hard drive or server and also helps when sending files by e-mail. But it still takes up the same size in memory when it is open. Nevertheless, I find it very useful.I have been successfully using it for many years and have only identified one disadvantage: Power Query cannot directly read the sheets in an XLSB file. If you want to get your file back into XLSX or XLSM format, simply open the file and "save as" the desired file type.
- Prevent duplicate data storage in pivot tables
Right-click in pivot table and select “PivotTable options”.
Select Data tab and deselect “Save source data with file”.
-->
- Delete unused columns and rows
Use Ctrl-End on each sheet – Excel saves all cells up to here.
If this includes many blank rows and/or columns, then delete them.
Important: first make a back-up copy then unhide any hidden rows or columns to be sure that you can see everything that you are about to delete.Further tips
- Reduce or remove lookups or links to external files, especially those with long path names
Copy formulas and paste as values (to replace the formulas) or move the source data in to the workbook (right click on source sheet then “move…” the sheet to main workbook). - Remove unnecessary formatting including conditional formatting on large areas
You can review and possibly delete conditional formatting rules individually.
--> Or you can clear them all from selected cells or an entire sheet all at once.
- Check for hidden sheets, unhide and delete if necessary
Right-click on a tab name and see if the “unhide” option can be used.
Unhide, review and delete e.g. old unused sheets. - Use Microsoft's "Check Performance" Tool
Microsoft have created a new tool to help you investigate and fix slow workbooks. It’s called “Check Performance” and at the time of writing (October 2022) it is currently only available for Office 365 subscribers in Excel for the Web. But you can analyse and fix an Excel file there, then continue to use it in desktop Excel.Read more details in the Microsoft blog.
