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”
- 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 here: first unhide any hidden rows or columns.
- Reduce or remove lookups or links to external files, especially those with long path names
Copy-paste 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 “unhidden” option can be used
Unhide, review and delete e.g. old unused sheets