Massively reduce the size of your large Excel files

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.

Gary Knott | how2excel - Excel Schulungen Large Excel-files

My TOP TIPS are easy to use...

  1. Save your file in binary format
    File, save as…. then under “Save as type” select “Excel Binary Workbook" or "*.xlsb”

  2. 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”

  3. 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.

Further tips

  1. 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).

  2. 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

  3. 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